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

Example 16 with SelectSqlBuilder

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

the class BaseQueryBuilderTest method testBuildList.

@Test
public void testBuildList() throws SQLException {
    SelectSqlBuilder qb;
    qb = createTest("Test", DatabaseCategory.MySql);
    qb.select("columns").where("conditions").orderBy("ob", true);
    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);
    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 17 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLWhereNew.

@Test
public void testMySQLWhereNew() throws SQLException {
    // Test against new constructor, the only different is there is one one ? in in()
    List<String> inParam = new ArrayList<String>();
    inParam.add("12");
    inParam.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.select("*", "id");
    int sqlType = Types.INTEGER;
    Integer paramValue = 1;
    builder.between("bw1", paramValue, paramValue, sqlType);
    builder.and().betweenNullable("bwNullable1", paramValue, 2, sqlType);
    builder.and().equal("eq1", paramValue, sqlType);
    builder.and().equalNullable("eqNullable1", paramValue, sqlType);
    builder.and().greaterThan("gt1", paramValue, sqlType);
    builder.and().greaterThanNullable("gtNullable1", paramValue, sqlType);
    builder.and().greaterThanEquals("gteq1", paramValue, sqlType);
    builder.and().greaterThanEqualsNullable("gteqNullable1", paramValue, sqlType);
    builder.and().in("inparam1", inParam, sqlType);
    builder.and().inNullable("inNullable1", inParam, sqlType);
    builder.and().isNotNull("notnull1");
    builder.and().isNull("isnull1");
    builder.and().lessThan("less1", paramValue, sqlType);
    builder.and().lessThanNullable("lessNullable1", paramValue, sqlType);
    builder.and().lessThanEquals("lessThan1", paramValue, sqlType);
    builder.and().lessThanEqualsNullable("lessThanNullable1", paramValue, sqlType);
    builder.and().like("like1", paramValue, sqlType);
    builder.and().likeNullable("likeNullable1", paramValue, sqlType);
    builder.and().notEqual("notEqual1", paramValue, sqlType);
    builder.and().notEqualNullable("notEqualNullable1", paramValue, sqlType);
    paramValue = null;
    try {
        builder.between("bw2", paramValue, paramValue, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().betweenNullable("bwNullable2", paramValue, 2, sqlType);
    try {
        builder.and().equal("eq2", paramValue, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().equalNullable("eqNullable2", paramValue, sqlType);
    try {
        builder.and().greaterThan("gt2", paramValue, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().greaterThanNullable("gtNullable2", paramValue, sqlType);
    try {
        builder.and().greaterThanEquals("gteq2", paramValue, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().greaterThanEqualsNullable("gteqNullable2", paramValue, sqlType);
    List<String> inParam2 = new ArrayList<String>();
    inParam2.add("12");
    inParam2.add(null);
    try {
        builder.and().in("inparam2", inParam2, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().inNullable("inNullable2", inParam2, sqlType);
    try {
        builder.and().lessThan("less2", paramValue, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().lessThanNullable("lessNullable2", paramValue, sqlType);
    try {
        builder.and().lessThanEquals("lessthan2", paramValue, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().lessThanEqualsNullable("lessThanNullable2", paramValue, sqlType);
    try {
        builder.and().like("like2", paramValue, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().likeNullable("likeNullable2", paramValue, sqlType);
    try {
        builder.and().notEqual("notEqual2", paramValue, sqlType);
    } catch (Exception e) {
        Assert.assertNotNull(e);
    }
    builder.and().notEqualNullable("notEqualNullable2", paramValue, sqlType);
    List<String> inParam3 = new ArrayList<String>();
    inParam3.add("12");
    inParam3.add(null);
    inParam3.add("12");
    inParam3.add(null);
    try {
        builder.and().in("inparam3", inParam3, sqlType);
    } catch (Exception e) {
        Assert.assertEquals("inparam3 is not support null value.", e.getMessage());
    }
    builder.and().inNullable("inNullable3", inParam3, Types.VARCHAR);
    List<Integer> inParam4 = new ArrayList<Integer>();
    inParam4.add(12);
    inParam4.add(null);
    inParam4.add(12);
    inParam4.add(null);
    builder.and().inNullable("inNullable4", inParam4, Types.INTEGER);
    List<Integer> inParam5 = new ArrayList<Integer>();
    inParam5.add(null);
    inParam5.add(null);
    builder.and().inNullable("inNullable5", inParam5, Types.INTEGER);
    String expected = "SELECT *, `id` FROM `People` WHERE " + "`bw1` BETWEEN ? AND ? " + "AND `bwNullable1` BETWEEN ? AND ? " + "AND `eq1` = ? " + "AND `eqNullable1` = ? " + "AND `gt1` > ? " + "AND `gtNullable1` > ? " + "AND `gteq1` >= ? " + "AND `gteqNullable1` >= ? " + "AND `inparam1` in ( ? ) " + "AND `inNullable1` in ( ? ) " + "AND `notnull1` IS NOT NULL " + "AND `isnull1` IS NULL " + "AND `less1` < ? " + "AND `lessNullable1` < ? " + "AND `lessThan1` <= ? " + "AND `lessThanNullable1` <= ? " + "AND `like1` LIKE ? " + "AND `likeNullable1` LIKE ? " + "AND `notEqual1` <> ? " + "AND `notEqualNullable1` <> ? " + "AND `inNullable2` in ( ? ) " + "AND `inNullable3` in ( ? ) " + "AND `inNullable4` in ( ? )";
    builder.from("People").setDatabaseCategory(DatabaseCategory.MySql);
    Assert.assertEquals(expected, builder.build().trim());
    Assert.assertEquals(23, builder.buildParameters().size());
    Assert.assertEquals(24, builder.getStatementParameterIndex());
    Assert.assertEquals(23, builder.buildParameters().get(22).getIndex());
    Assert.assertEquals("inNullable4", builder.buildParameters().get(22).getName());
    Assert.assertEquals(Types.INTEGER, builder.buildParameters().get(22).getSqlType());
}

Example 18 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLLike.

@Test
public void testMySQLLike() 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.requireFirst();
    builder.from("People").setDatabaseCategory(DatabaseCategory.MySql);
    String sql = builder.build();
    String expect_sql = "SELECT COUNT(1) FROM `People` " + "WHERE `b` LIKE ? AND `b` LIKE ? AND `b` LIKE ? AND `b` LIKE ? AND `b` LIKE ? ORDER BY `PeopleID` DESC LIMIT 1";
    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());
}

Example 19 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLNotIn.

@Test
public void testMySQLNotIn() 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.equal("a", "paramValue", Types.INTEGER);
    builder.and().in("b", in, Types.INTEGER);
    builder.and().notIn("c", notIn, 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 COUNT(1) FROM `People` " + "WHERE `a` = ? AND `b` in ( ? ) AND `c` not in ( ? ) AND `b` LIKE ? AND `c` BETWEEN ? AND ? " + "AND `sss` IS NULL ORDER BY `PeopleID` DESC LIMIT 1";
    Assert.assertEquals(expect_sql, sql);
}

Example 20 with SelectSqlBuilder

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

the class AbstractBuilderTest method testNullValueAtBegining.

@Test
public void testNullValueAtBegining() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.select("PeopleID", "Name", "CityID");
    builder.equalNullable("a", null, Types.INTEGER);
    builder.and().in("b", in, Types.INTEGER);
    builder.and().likeNullable("b", null, Types.INTEGER);
    builder.and().between("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 `b` in ( ? ) AND `c` BETWEEN ? AND ? " + "AND `sss` IS NULL ORDER BY `PeopleID` DESC LIMIT 1";
    Assert.assertEquals(expect_sql, sql);
}