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

Example 21 with SelectSqlBuilder

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

the class PartialQueryTableDaoUnitTest method testDetectFieldNotExist.

@Test
public void testDetectFieldNotExist() throws Exception {
    DalTableDao<PersonWithoutName> client = new DalTableDao<>(new DalDefaultJpaParser<>(PersonWithoutName.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", "PeopleID", "CountryID");
    builder.in("PeopleID", peopleIds, Types.INTEGER, false);
    builder.and();
    builder.in("CityID", cityIds, Types.INTEGER, false);
    try {
        client.query(builder, new DalHints().inAllShards().inTableShard(1));
        Assert.fail();
    } catch (DalException e) {
        e.printStackTrace();
        assertEquals(ErrorCode.FieldNotExists.getCode(), e.getErrorCode());
    }
}

Example 22 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQL.

@Test
public void testMySQL() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder("People", DatabaseCategory.MySql, 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", false);
    String sql = builder.build();
    String expect_sql = "SELECT `PeopleID`, `Name`, `CityID` FROM `People` " + "WHERE `a` = ? AND `b` in ( ?, ? ) AND `b` LIKE ? AND `c` BETWEEN ? AND ? " + "AND `sss` IS NULL ORDER BY `PeopleID` DESC";
    Assert.assertEquals(expect_sql, sql);
}

Example 23 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLFirst.

@Test
public void testMySQLFirst() throws SQLException {
    List<String> in = new ArrayList<String>();
    in.add("12");
    in.add("12");
    SelectSqlBuilder builder = new SelectSqlBuilder("People", DatabaseCategory.MySql, 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", false);
    builder.requireFirst();
    String sql = builder.build();
    String expect_sql = "SELECT `PeopleID`, `Name`, `CityID` 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 24 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testSQLServerFirst.

@Test
public void testSQLServerFirst() 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);
    builder.requireFirst();
    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 25 with SelectSqlBuilder

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

the class SelectSqlBuilderTest method testMySQLPaginationNew.

@Test
public void testMySQLPaginationNew() 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", false);
    builder.from("People").setDatabaseCategory(DatabaseCategory.MySql);
    builder.atPage(10, 10);
    String sql = builder.build();
    String expect_sql = "SELECT `PeopleID`, `Name`, `CityID` FROM `People` " + "WHERE `a` = ? AND `b` in ( ? ) AND `b` LIKE ? AND `c` BETWEEN ? AND ? " + "AND `sss` IS NULL ORDER BY `PeopleID` DESC LIMIT 90, 10";
    Assert.assertEquals(expect_sql, sql);
}