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

Example 51 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 52 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);
}

Example 53 with SelectSqlBuilder

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

the class BaseQueryBuilderTest method testBuildSingle.

@Test
public void testBuildSingle() throws SQLException {
    SelectSqlBuilder qb;
    qb = createTest("Test", DatabaseCategory.MySql);
    qb.select("columns").where("conditions").orderBy("ob", true).requireSingle();
    assertEquals("SELECT `columns` FROM `Test` WHERE conditions ORDER BY `ob` ASC", qb.build());
    assertEquals("SELECT `columns` FROM `Test_0` WHERE conditions ORDER BY `ob` ASC", qb.build("_0"));
    qb = createTest("Test", DatabaseCategory.SqlServer);
    qb.select("columns").where("conditions").orderBy("ob", true).requireSingle();
    assertEquals("SELECT [columns] FROM [Test] WITH (NOLOCK) WHERE conditions ORDER BY [ob] ASC", qb.build());
    assertEquals("SELECT [columns] FROM [Test_0] WITH (NOLOCK) WHERE conditions ORDER BY [ob] ASC", qb.build("_0"));
}

Example 54 with SelectSqlBuilder

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

the class BaseQueryBuilderTest method testBuildFirst.

@Test
public void testBuildFirst() throws SQLException {
    SelectSqlBuilder qb;
    qb = createTest("Test", DatabaseCategory.MySql);
    qb.select("columns").where("conditions").orderBy("ob", true).requireFirst();
    assertEquals("SELECT `columns` FROM `Test` WHERE conditions ORDER BY `ob` ASC LIMIT 1", qb.build());
    assertEquals("SELECT `columns` FROM `Test_0` WHERE conditions ORDER BY `ob` ASC LIMIT 1", qb.build("_0"));
    qb = createTest("Test", DatabaseCategory.SqlServer);
    qb.select("columns").where("conditions").orderBy("ob", true).requireFirst();
    assertEquals("SELECT TOP 1 [columns] FROM [Test] WITH (NOLOCK) WHERE conditions ORDER BY [ob] ASC", qb.build());
    assertEquals("SELECT TOP 1 [columns] FROM [Test_0] WITH (NOLOCK) WHERE conditions ORDER BY [ob] ASC", qb.build("_0"));
}

Example 55 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testSQLServerLike.

@Test
public void testSQLServerLike() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    List<String> notIn = new ArrayList<String>();
    notIn.add("123");
    notIn.add("123");
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.selectCount();
    builder.and().like("b", "in", MatchPattern.USER_DEFINED, Types.INTEGER);
    builder.and().like("b", "in", MatchPattern.END_WITH, Types.INTEGER);
    builder.and().likeNullable("b", null, MatchPattern.USER_DEFINED, Types.INTEGER);
    builder.and().like("b", "in", MatchPattern.BEGIN_WITH, Types.INTEGER);
    builder.and().like("b", "in", MatchPattern.CONTAINS, Types.INTEGER);
    builder.and().like("b", "in", Types.INTEGER);
    builder.and().likeNullable("b", null, MatchPattern.CONTAINS, Types.INTEGER);
    builder.orderBy("PeopleID", false);
    builder.from("People").setDatabaseCategory(DatabaseCategory.SqlServer);
    String sql = builder.build();
    String expect_sql = "SELECT COUNT(1) FROM [People] WITH (NOLOCK) " + "WHERE [b] LIKE ? AND [b] LIKE ? AND [b] LIKE ? AND [b] LIKE ? AND [b] LIKE ? ORDER BY [PeopleID] DESC";
    Assert.assertEquals(expect_sql, sql);
    StatementParameters p = builder.buildParameters();
    int i = 0;
    Assert.assertEquals("in", p.get(i++).getValue());
    Assert.assertEquals("%in", p.get(i++).getValue());
    Assert.assertEquals("in%", p.get(i++).getValue());
    Assert.assertEquals("%in%", p.get(i++).getValue());
    Assert.assertEquals("in", p.get(i++).getValue());
}