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

Example 1 with SelectSqlBuilder

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

the class PartialQueryTableDaoUnitTest method testFindBySelectedField.

@Test
public void testFindBySelectedField() throws Exception {
    DalTableDao<Person> client = new DalTableDao<>(new DalDefaultJpaParser<>(Person.class));
    List<Integer> peopleIds = new ArrayList<>();
    peopleIds.add(1);
    peopleIds.add(2);
    peopleIds.add(3);
    List<Integer> cityIds = new ArrayList<>();
    cityIds.add(1);
    cityIds.add(2);
    cityIds.add(3);
    SelectSqlBuilder builder = new SelectSqlBuilder();
    builder.select("DataChange_LastTime", "CityID", "Name", "ProvinceID");
    builder.in("PeopleID", peopleIds, Types.INTEGER, false);
    builder.and();
    builder.in("CityID", cityIds, Types.INTEGER, false);
    try {
        List<Person> ret = client.query(builder, new DalHints().inAllShards().inTableShard(1));
        Assert.assertNull(ret.get(0).getCountryID());
        Assert.assertNull(ret.get(0).getPeopleID());
    } catch (DalException e) {
        Assert.fail();
    }
}

Example 2 with SelectSqlBuilder

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

the class AbstractBuilderTest method testNullValue.

@Test
public void testNullValue() 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().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 `a` = ? AND `b` in ( ? ) AND `c` BETWEEN ? AND ? " + "AND `sss` IS NULL ORDER BY `PeopleID` DESC LIMIT 1";
    Assert.assertEquals(expect_sql, sql);
}

Example 3 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testSQLServerPagination.

@Test
public void testSQLServerPagination() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder("People", DatabaseCategory.SqlServer, 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", true);
    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 ? ROWS FETCH NEXT ? ROWS ONLY";
    Assert.assertEquals(expect_sql, sql);
}

Example 4 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLSelctCount.

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

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

the class SelectSqlBuilderTest method testSQLServer.

@Test
public void testSQLServer() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder("People", DatabaseCategory.SqlServer, false);
    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);
    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);
}