Examples with SelectSqlBuilder com.ctrip.platform.dal.dao.sqlbuilder.SelectSqlBuilder used on opensource projects

Example 31 with SelectSqlBuilder

use of com.ctrip.platform.dal.dao.sqlbuilder.SelectSqlBuilder in project dal by ctripcorp.

the class SelectSqlBuilderTest method testMySQLFirstNew.

@Test
public void testMySQLFirstNew() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.select("PeopleID", "Name", "CityID");
    builder.equal("a", "paramValue", Types.INTEGER);
    builder.and().in("b", in, Types.INTEGER);
    builder.and().like("b", "in", Types.INTEGER);
    builder.and().betweenNullable("c", "paramValue1", "paramValue2", Types.INTEGER);
    builder.and().betweenNullable("d", null, "paramValue2", Types.INTEGER);
    builder.and().isNull("sss");
    builder.orderBy("PeopleID", false);
    builder.requireFirst();
    builder.from("People").setDatabaseCategory(DatabaseCategory.MySql);
    String sql = builder.build();
    String expect_sql = "SELECT `PeopleID`, `Name`, `CityID` FROM `People` " + "WHERE `a` = ? AND `b` in ( ? ) AND `b` LIKE ? AND `c` BETWEEN ? AND ? " + "AND `sss` IS NULL ORDER BY `PeopleID` DESC LIMIT 1";
    Assert.assertEquals(expect_sql, sql);
}

Example 32 with SelectSqlBuilder

use of com.ctrip.platform.dal.dao.sqlbuilder.SelectSqlBuilder in project dal by ctripcorp.

the class SelectSqlBuilderTest method testSQLServerNew.

@Test
public void testSQLServerNew() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.select("PeopleID", "Name", "CityID");
    builder.equal("a", "paramValue", Types.INTEGER);
    builder.and().in("b", in, Types.INTEGER);
    builder.and().like("b", "in", Types.INTEGER);
    builder.and().betweenNullable("c", "paramValue1", "paramValue2", Types.INTEGER);
    builder.and().betweenNullable("d", null, "paramValue2", Types.INTEGER);
    builder.and().isNull("sss");
    builder.orderBy("PeopleID", true);
    builder.from("People").setDatabaseCategory(DatabaseCategory.SqlServer);
    String sql = builder.build();
    String expect_sql = "SELECT [PeopleID], [Name], [CityID] FROM [People] WITH (NOLOCK) " + "WHERE [a] = ? AND [b] in ( ? ) AND [b] LIKE ? AND [c] BETWEEN ? AND ? " + "AND [sss] IS NULL ORDER BY [PeopleID] ASC";
    Assert.assertEquals(expect_sql, sql);
}

Example 33 with SelectSqlBuilder

use of com.ctrip.platform.dal.dao.sqlbuilder.SelectSqlBuilder in project dal by ctripcorp.

the class SelectSqlBuilderTest method testMySQLPagination.

@Test
public void testMySQLPagination() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder("People", DatabaseCategory.MySql, true);
    builder.select("PeopleID", "Name", "CityID");
    builder.equal("a", "paramValue", Types.INTEGER);
    builder.and().in("b", in, Types.INTEGER);
    builder.and().like("b", "in", Types.INTEGER);
    builder.and().betweenNullable("c", "paramValue1", "paramValue2", Types.INTEGER);
    builder.and().betweenNullable("d", null, "paramValue2", Types.INTEGER);
    builder.and().isNull("sss");
    builder.orderBy("PeopleID", false);
    String sql = builder.build();
    String expect_sql = "SELECT `PeopleID`, `Name`, `CityID` FROM `People` " + "WHERE `a` = ? AND `b` in ( ?, ? ) AND `b` LIKE ? AND `c` BETWEEN ? AND ? " + "AND `sss` IS NULL ORDER BY `PeopleID` DESC limit ?, ?";
    Assert.assertEquals(expect_sql, sql);
}

Example 34 with SelectSqlBuilder

use of com.ctrip.platform.dal.dao.sqlbuilder.SelectSqlBuilder in project dal by ctripcorp.

the class SelectSqlBuilderTest method testMySQLSelctAll.

@Test
public void testMySQLSelctAll() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.selectAll();
    builder.equal("a", "paramValue", Types.INTEGER);
    builder.and().in("b", in, Types.INTEGER);
    builder.and().like("b", "in", Types.INTEGER);
    builder.and().betweenNullable("c", "paramValue1", "paramValue2", Types.INTEGER);
    builder.and().betweenNullable("d", null, "paramValue2", Types.INTEGER);
    builder.and().isNull("sss");
    builder.orderBy("PeopleID", false);
    builder.requireFirst();
    builder.from("People").setDatabaseCategory(DatabaseCategory.MySql);
    String sql = builder.build();
    String expect_sql = "SELECT * FROM `People` " + "WHERE `a` = ? AND `b` in ( ? ) AND `b` LIKE ? AND `c` BETWEEN ? AND ? " + "AND `sss` IS NULL ORDER BY `PeopleID` DESC LIMIT 1";
    Assert.assertEquals(expect_sql, sql);
}

Example 35 with SelectSqlBuilder

use of com.ctrip.platform.dal.dao.sqlbuilder.SelectSqlBuilder in project dal by ctripcorp.

the class DalTableDaoTestStub method testQueryObject.

@Test
public void testQueryObject() throws SQLException {
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.equal("type", 1, Types.SMALLINT);
    builder.requireFirst();
    Short models = dao.queryObject(builder, new DalHints(), Short.class);
    Assert.assertNotNull(models);
    builder = new SelectSqlBuilder();
    builder.equal("type", 1, Types.SMALLINT);
    builder.requireFirst();
    models = dao.queryObject(builder.atPage(1, 1), new DalHints(), Short.class);
    Assert.assertNotNull(models);
    builder = new SelectSqlBuilder();
    builder.equal("type", 10, Types.SMALLINT);
    builder.requireFirst();
    models = dao.queryObject(builder.atPage(1, 10), new DalHints(), Short.class);
    Assert.assertNull(models);
}