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

Example 81 with SelectSqlBuilder

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

the class BaseDalTabelDaoShardByTableTest method testQueryListBySimpleTypeTableShardBy.

@Test
public void testQueryListBySimpleTypeTableShardBy() throws SQLException {
    int index = 1;
    // expected 1 record
    List<String> list1 = new ArrayList<>();
    list1.add("0");
    StatementParameters parameters1 = new StatementParameters();
    parameters1.setInParameter(index, "tableIndex", Types.INTEGER, list1);
    SelectSqlBuilder ssb1 = new SelectSqlBuilder();
    ssb1.selectAll().from(TABLE_NAME).where(" tableIndex in (?) ").with(parameters1);
    List<Integer> result1 = dao.query(ssb1, new DalHints().tableShardBy("tableIndex"), Integer.class);
    assertEquals(1, result1.size());
    // expected 3 record
    List<String> list2 = new ArrayList<>();
    list2.add("0");
    list2.add("1");
    StatementParameters parameters2 = new StatementParameters();
    parameters2.setInParameter(index, "tableIndex", Types.INTEGER, list2);
    SelectSqlBuilder ssb2 = new SelectSqlBuilder();
    ssb2.selectAll().from(TABLE_NAME).where(" tableIndex in (?) ").with(parameters2);
    List<Integer> result2 = dao.query(ssb2, new DalHints().tableShardBy("tableIndex"), Integer.class);
    assertEquals(3, result2.size());
    // expected 6 records
    List<String> list3 = new ArrayList<>();
    list3.add("0");
    list3.add("1");
    list3.add("2");
    StatementParameters parameters3 = new StatementParameters();
    parameters3.setInParameter(index, "tableIndex", Types.INTEGER, list3);
    SelectSqlBuilder ssb3 = new SelectSqlBuilder();
    ssb3.selectAll().from(TABLE_NAME).where(" tableIndex in (?) ").with(parameters3);
    List<Integer> result3 = dao.query(ssb3, new DalHints().tableShardBy("tableIndex"), Integer.class);
    assertEquals(6, result3.size());
    // expected 10 records
    List<String> list4 = new ArrayList<>();
    list4.add("0");
    list4.add("1");
    list4.add("2");
    list4.add("3");
    StatementParameters parameters4 = new StatementParameters();
    parameters4.setInParameter(index, "tableIndex", Types.INTEGER, list4);
    SelectSqlBuilder ssb4 = new SelectSqlBuilder();
    ssb4.selectAll().from(TABLE_NAME).where(" tableIndex in (?) ").with(parameters4);
    List<Integer> result4 = dao.query(ssb4, new DalHints().tableShardBy("tableIndex"), Integer.class);
    assertEquals(10, result4.size());
}

Example 82 with SelectSqlBuilder

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

the class BaseDalTabelDaoShardByTableTest method testCountInAllTableShards.

@Test
public void testCountInAllTableShards() throws SQLException {
    // select all records
    SelectSqlBuilder ssb1 = new SelectSqlBuilder();
    ssb1.selectAll().from(TABLE_NAME).selectCount();
    Number count1 = dao.count(ssb1, new DalHints().inAllTableShards());
    assertEquals(10L, count1);
    // filter condition
    SelectSqlBuilder ssb2 = new SelectSqlBuilder();
    ssb2.selectAll().from(TABLE_NAME).where(" id = 1 ").selectCount();
    Number count2 = dao.count(ssb2, new DalHints().inAllTableShards());
    assertEquals(4L, count2);
}

Example 83 with SelectSqlBuilder

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

the class BaseDalTabelDaoShardByTableTest method testQueryObjectInTableShards.

@Test
public void testQueryObjectInTableShards() throws SQLException {
    Set<String> tableShards = new HashSet<>();
    tableShards.add("0");
    tableShards.add("1");
    SelectSqlBuilder ssb1 = new SelectSqlBuilder();
    ssb1.selectAll().from(TABLE_NAME).where(" id = 1 ").requireSingle();
    try {
        ClientTestModel model = dao.queryObject(ssb1, new DalHints().inTableShards(tableShards));
    } catch (Exception e) {
        assertEquals("It is expected to return only 1 result. But the actually count is more than 1", e.getMessage());
    }
    SelectSqlBuilder ssb2 = new SelectSqlBuilder();
    ssb2.selectAll().from(TABLE_NAME).where(" id = 1 ").requireFirst();
    try {
        ClientTestModel model = dao.queryObject(ssb2, new DalHints().inTableShards(tableShards));
        assertTrue(model != null);
    } catch (Exception e) {
        Assert.fail();
    }
}

Example 84 with SelectSqlBuilder

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

the class BaseDalTabelDaoShardByTableTest method testQueryListTableShardBy.

@Test
public void testQueryListTableShardBy() throws SQLException {
    int index = 1;
    // expected 1 record
    List<String> list1 = new ArrayList<>();
    list1.add("0");
    StatementParameters parameters1 = new StatementParameters();
    parameters1.setInParameter(index, "tableIndex", Types.INTEGER, list1);
    SelectSqlBuilder ssb1 = new SelectSqlBuilder();
    ssb1.selectAll().from(TABLE_NAME).where(" tableIndex in (?) ").with(parameters1);
    List<ClientTestModel> result1 = dao.query(ssb1, new DalHints().tableShardBy("tableIndex"));
    assertEquals(1, result1.size());
    // expected 3 record
    List<String> list2 = new ArrayList<>();
    list2.add("0");
    list2.add("1");
    StatementParameters parameters2 = new StatementParameters();
    parameters2.setInParameter(index, "tableIndex", Types.INTEGER, list2);
    SelectSqlBuilder ssb2 = new SelectSqlBuilder();
    ssb2.selectAll().from(TABLE_NAME).where(" tableIndex in (?) ").with(parameters2);
    List<ClientTestModel> result2 = dao.query(ssb2, new DalHints().tableShardBy("tableIndex"));
    assertEquals(3, result2.size());
    // expected 6 records
    List<String> list3 = new ArrayList<>();
    list3.add("0");
    list3.add("1");
    list3.add("2");
    StatementParameters parameters3 = new StatementParameters();
    parameters3.setInParameter(index, "tableIndex", Types.INTEGER, list3);
    SelectSqlBuilder ssb3 = new SelectSqlBuilder();
    ssb3.selectAll().from(TABLE_NAME).where(" tableIndex in (?) ").with(parameters3);
    List<ClientTestModel> result3 = dao.query(ssb3, new DalHints().tableShardBy("tableIndex"));
    assertEquals(6, result3.size());
    // expected 10 records
    List<String> list4 = new ArrayList<>();
    list4.add("0");
    list4.add("1");
    list4.add("2");
    list4.add("3");
    StatementParameters parameters4 = new StatementParameters();
    parameters4.setInParameter(index, "tableIndex", Types.INTEGER, list4);
    SelectSqlBuilder ssb4 = new SelectSqlBuilder();
    ssb4.selectAll().from(TABLE_NAME).where(" tableIndex in (?) ").with(parameters4);
    List<ClientTestModel> result4 = dao.query(ssb4, new DalHints().tableShardBy("tableIndex"));
    assertEquals(10, result4.size());
}

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