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

Example 41 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 42 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());
}

Example 43 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLWhere.

@Test
public void testMySQLWhere() throws SQLException {
    List<String> inParam = new ArrayList<String>();
    inParam.add("12");
    inParam.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder("People", DatabaseCategory.MySql, false);
    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) {
        e.printStackTrace();
        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 ( ?, ? )";
    Assert.assertEquals(expected, builder.build().trim());
    Assert.assertEquals(27, builder.buildParameters().size());
    Assert.assertEquals(28, builder.getStatementParameterIndex());
    Assert.assertEquals(27, builder.buildParameters().get(26).getIndex());
    Assert.assertEquals("inNullable4", builder.buildParameters().get(26).getName());
    Assert.assertEquals(Types.INTEGER, builder.buildParameters().get(26).getSqlType());
}

Example 44 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testSQLServerNotIn.

@Test
public void testSQLServerNotIn() 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.or().notInNullable("c", new ArrayList<>(), 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.from("People").setDatabaseCategory(DatabaseCategory.SqlServer);
    String sql = builder.build();
    String expect_sql = "SELECT COUNT(1) FROM [People] WITH (NOLOCK) " + "WHERE [a] = ? AND [b] in ( ? ) AND [c] not in ( ? ) AND [b] LIKE ? AND [c] BETWEEN ? AND ? " + "AND [sss] IS NULL ORDER BY [PeopleID] DESC";
    Assert.assertEquals(expect_sql, sql);
}

Example 45 with SelectSqlBuilder

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

the class BaseDalTabelDaoShardByTableTest method testQueryByColumnNames.

/**
 * Query by Entity with Primary key
 * @throws SQLException
 */
@Test
public void testQueryByColumnNames() throws SQLException {
    ClientTestModel pk = null;
    ClientTestModel model = null;
    for (int i = 0; i < mod; i++) {
        pk = new ClientTestModel();
        pk.setId(1);
        // By tabelShard
        DalTableDao<ClientTestModel> dao = new DalTableDao(ClientTestModel.class, databaseName, "dal_client_test");
        model = dao.queryByPk(pk, new DalHints().inTableShard(i).selectByNames());
        assertEquals(1, model.getId().intValue());
        assertEquals(i, model.getTableIndex().intValue());
        dao.queryLike(model, new DalHints().inTableShard(i).selectByNames());
        dao.count("id > 0", new StatementParameters(), new DalHints().inTableShard(i).selectByNames());
        Long L = dao.queryObject(new SelectSqlBuilder().select("id").requireFirst().where("id > 0"), new DalHints().inTableShard(i).selectByNames(), Long.class);
    }
}