/**
* 支持Distinct的查询()
*
* @throws SQLException
*/
@Test
public void testSelectDistinct() throws SQLException {
Query<Person> t1 = QB.create(Person.class);
// 只选择指定的列
Selects select = QB.selectFrom(t1);
select.clearSelectItems();
select.setDistinct(true);
select.column(Person.Field.schoolId);
// select.guessColumn("schoolId",null);
select.columns(t1, "name,age,cell");
select.column(t1, "id");
t1.setCascade(false);
List<Person> map = db.select(t1.getInstance());
LogUtil.show(map.get(0));
}
/** @throws SQLException */
@Test
public void testSelect() throws SQLException {
System.out.println("=========== testSelect Begin ==========");
Query<Person> p = QB.create(Person.class);
List<Person> ps = db.select(p, new IntRange(1, 2));
assertTrue(ps.size() > 0);
System.out.println("=========== testSelect End ==========");
}
@Test
public void testSelectWithFunction() throws SQLException {
Query<Person> t1 = QB.create(Person.class);
// 只选择指定的列
t1.addCondition(new FBIField("MOD(age, 10)", t1), 2);
t1.setCascade(false);
List<Person> map = db.select(t1.getInstance());
}
/**
* 测试分组
*
* @throws SQLException
*/
@Test
public void testGroup() throws SQLException {
Transaction db = this.db.startTransaction();
Person p = RandomData.newInstance(Person.class);
p.setGender("F");
p.setAge(19);
db.insert(p);
Query<Person> t1 = QB.create(Person.class);
Selects select = QB.selectFrom(t1);
select.column(Person.Field.gender).group();
select.column(Person.Field.id).count().as("count");
select.column(Person.Field.age).min().as("minAge");
select.column(Person.Field.age).max().as("maxAge").having(Operator.GREAT, 0);
List<Map> map = db.selectAs(t1, Map.class);
db.rollback(true);
LogUtil.show(map);
}
/**
* 级联查询，驱动表的选择字段指定，其他表自动
*
* @throws SQLException
*/
@Test
public void testSelectColumnsInCascade() throws SQLException {
Query<Person> t1 = QB.create(Person.class);
// 只选择指定的列
Selects select = QB.selectFrom(t1);
select.column(t1, "schoolId");
select.columns(t1, "name,age,cell");
select.column(t1, "id");
List<Person> map = db.select(t1.getInstance());
Person p = map.get(0);
assertTrue(!p.getScores().isEmpty());
assertTrue(!p.getFriends().isEmpty());
for (Score score : p.getScores()) {
assertNotNull(score.getTestTime());
}
System.out.println("===========result:" + map.size() + "==============");
}
/**
* 测试JpqlExpression表达式的使用
*
* @throws SQLException
*/
@Test
public void testExpression1() throws SQLException {
System.out.println("=========== testExpression1 Start ==========");
Query<Person> p = QB.create(Person.class);
p.addCondition(
new FBIField("upper(name)||str(age)", p), new JpqlExpression("upper(name)||'22'", p));
List<Person> ps = db.select(p);
assertTrue(ps.size() > 0);
System.out.println("=========== testExpression1 End ==========");
}
/**
* 测试使用复杂的子查询过滤条件
*
* @throws SQLException
*/
@Test
public void testComplextFilterCondition() throws SQLException {
System.out.println("=========== testComplextFilterCondition End ==========");
//
// 添加过滤条件的
Person p = new Person();
p.getQuery().addCondition(Person.Field.id, 1);
Condition or =
QB.or(
QB.eq(Score.Field.subject, "语文"),
QB.eq(Score.Field.subject, "化学"),
QB.eq(Score.Field.subject, "英语"));
p.getQuery().addCascadeCondition("scores", or);
Person result = db.load(p);
System.out.println("loaded");
assertEquals(3, result.getScores().size());
System.out.println("=========== testComplextFilterCondition End ==========");
}
/**
* 针对一个带有级联关系的对象，实现指定选择列的查询
*
* @throws SQLException
*/
@Test
public void testAssignSelectColumn() throws SQLException {
db.createTable(Person.class);
Transaction db = this.db.startTransaction();
Person p = RandomData.newInstance(Person.class);
p.setGender("F");
p.setAge(19);
db.insert(p);
System.out.println("===========testAssignSelectColumn begin==============");
{
Query<Person> t1 = QB.create(Person.class);
// 只选择指定的列
t1.setCascadeViaOuterJoin(false);
Selects select = QB.selectFrom(t1);
// select.clearSelectItems();
select.guessColumn("schoolId");
// select.column(School.Field.name);
select.guessColumn("schoolName");
select.columns(t1, "name,age,cell");
select.column(t1, "id");
List<Person> result = db.select(t1);
LogUtil.show(result.get(0));
}
{
Query<Person> t1 = QB.create(Person.class);
// 只选择指定的列
// t1.setAutoOuterJoin(false);
Selects select = QB.selectFrom(t1);
// select.clearSelectItems();
select.guessColumn("schoolId");
// select.column(School.Field.name);
select.guessColumn("schoolName");
select.columns(t1, "name,age,cell");
select.column(t1, "id");
List<Person> result = db.select(t1);
LogUtil.show(result.get(0));
}
{
Query<Person> t1 = QB.create(Person.class);
// 只选择指定的列
// t1.setAutoOuterJoin(false);
Selects select = QB.selectFrom(t1);
// select.clearSelectItems();
select.guessColumn("schoolId");
// select.column(School.Field.name);
select.guessColumn("schoolName");
select.columns(t1, "name,age,cell");
select.column(t1, "id");
t1.setCascade(false);
List<Person> result = db.select(t1.getInstance());
LogUtil.show(result.get(0));
}
db.rollback(true);
System.out.println("===========result==============");
}
/**
* 在复杂条件中使用REF字段，并且测试在对一情况下，FilterField自动转换为RefField.
*
* @throws SQLException
*/
@Test
public void testComplextRefCondition() throws SQLException {
System.out.println("=========== testComplextRefCondition Start ==========");
Person q = new Person();
q.getQuery().addCondition(Person.Field.age, Operator.GREAT, 16);
Or or = new Or();
or.addCondition(new RefField(School.Field.id), 2);
or.addCondition(new RefField(School.Field.name), Operator.MATCH_ANY, "国");
q.getQuery().addCondition(or); // 凡是引用一个其他表的条件要用RefField包裹
q.getQuery().addCascadeCondition(QB.eq(School.Field.name, "战国高校"));
List<Person> result = db.select(q);
assertEquals(2, result.size());
System.out.println("=========== testComplextRefCondition End ==========");
}
/**
* 在一对多的关联中使用对子表的过滤条件
*
* @throws SQLException
*/
@Test
public void testFilterInOneToManyRef() throws SQLException {
ORMConfig.getInstance().setCacheDebug(true);
System.out.println("=========== testFilterInOneToManyRef Start ==========");
// 无过滤条件的
Person p1 = new Person();
p1.getQuery().addCondition(Person.Field.id, 1);
Person result = db.load(p1);
assertEquals(6, result.getScores().size());
// 添加过滤条件的
Person p = new Person();
p.getQuery().addCondition(Person.Field.id, 1);
p.getQuery().addCascadeCondition(QB.in(Score.Field.subject, new String[] {"语文", "化学", "英语"}));
result = db.load(p);
assertEquals(3, result.getScores().size());
System.out.println("=========== testFilterInOneToManyRef End ==========");
}