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

Example 26 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLSelectAll.

@Test
public void testMySQLSelectAll() 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 27 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testSQLServerPaginationNew.

@Test
public void testSQLServerPaginationNew() 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);
    builder.atPage(2, 10);
    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 " + "OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY";
    Assert.assertEquals(expect_sql, sql);
}

Example 28 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testSQLServerFirstNew.

@Test
public void testSQLServerFirstNew() 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.requireFirst();
    builder.from("People").setDatabaseCategory(DatabaseCategory.SqlServer);
    String sql = builder.build();
    String expect_sql = "SELECT TOP 1 [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 29 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLSelectCount.

@Test
public void testMySQLSelectCount() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.selectCount();
    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 COUNT(1) 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 30 with SelectSqlBuilder

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

the class AbstractBuilderTest method validate.

public void validate(String exp, String expected) throws SQLException {
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.from("People").setDatabaseCategory(DatabaseCategory.SqlServer);
    // equal equalNull between betweenNull in inNull like likeNull isNull isNotNull AND OR NOT ( )
    String[] tokens = exp.split(" ");
    for (String token : tokens) {
        switch(token) {
            case "equal":
                builder.equal("a", "", Types.INTEGER);
                break;
            case "equalNull":
                builder.equalNullable("a", null, Types.INTEGER);
                break;
            case "like":
                builder.like("a", "", Types.INTEGER);
                break;
            case "likeNull":
                builder.likeNullable("a", null, Types.INTEGER);
                break;
            case "isNull":
                builder.isNull("a");
                break;
            case "isNotNull":
                builder.isNotNull("a");
                break;
            case "in":
                List<?> l = new ArrayList<>();
                builder.in("a", l, Types.INTEGER);
                break;
            case "between":
                builder.between("a", "", "", Types.INTEGER);
                break;
            case "inNull":
                builder.inNullable("a", null, Types.INTEGER);
                break;
            case "betweenNull":
                builder.betweenNullable("a", null, null, Types.INTEGER);
                break;
            case "AND":
                builder.and();
                break;
            case "OR":
                builder.or();
                break;
            case "NOT":
                builder.not();
                break;
            case "(":
                builder.leftBracket();
                break;
            case ")":
                builder.rightBracket();
                break;
            default:
                Assert.fail("Unknown token: " + token);
        }
    }
    Assert.assertEquals(expected, builder.getWhereExp());
}