/**
* 针对一个带有级联关系的对象，实现指定选择列的查询
*
* @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==============");
}
/**
* 插入Person表
*
* @throws SQLException
*/
private void testInserPerson(Session db) throws SQLException {
Person p1 = new Person();
p1.setAge(22);
p1.setBirthDay(new Date());
p1.setCell("135gg876");
p1.setGender("M");
p1.setHomeTown("BEIJING");
p1.setLastModified(new Date());
p1.setName("爸爸" + StringUtils.randomString());
p1.setSchoolId(2);
p1.setPhone("(083-2233)88778800");
db.insert(p1);
Person p2 = new Person();
p2.setAge(22);
p2.setBirthDay(new Date());
p2.setCell("13506877");
p2.setGender("M");
p2.setHomeTown("BEIJING");
p2.setLastModified(new Date());
p2.setName("JinWang" + StringUtils.randomString());
p2.setPhone("(083-87ss0");
p2.setSchoolId(3);
// p2.setPhoto(new File("c:/NTDETECT.COM"));
db.insert(p2);
Person p3 = new Person();
p3.setAge(22);
p3.setBirthDay(new Date());
p3.setCell("1350ff876");
p3.setGender("M");
p3.setHomeTown("BEIJING");
p3.setLastModified(new Date());
p3.setName("儿子" + StringUtils.randomString());
p3.setPhone("(083-28800)");
p3.setSchoolId(2);
p3.setParentId(1);
db.insert(p3);
assertEquals(1, p1.getId().intValue());
assertEquals(2, p2.getId().intValue());
assertEquals(3, p3.getId().intValue());
// System.out.println(JSON.toJSONString(Arrays.asList(p1,p2,p3)));
}
@Test
public void testDeleteCascade() throws SQLException {
Transaction db = this.db.startTransaction();
Person p = new Person();
p.setAge(20);
db.select(p);
System.out.println(p.getScores());
db.deleteCascade(p);
db.rollback(true);
}
/**
* 测试级联的更新操作
*
* @throws SQLException
*/
@Test
public void testRefUpdate() throws SQLException {
System.out.println("=========== testRefUpdate Begin ==========");
Transaction db = this.db.startTransaction();
Person p = new Person();
p.setId(2);
p = db.load(p);
//
p.setSchoolId(0);
p.setSchool(new School("华南大学"));
p.setAge(123);
db.updateCascade(p);
db.rollback(true);
System.out.println("=========== testRefUpdate End ==========");
}
/**
* 测试级联对象的插入操作
*
* @throws SQLException
*/
@Test
public void testRefInsert() throws SQLException {
System.out.println("=========== testRefInsert Begin ==========");
Person p = new Person();
p.setName("张三");
p.setGender("M");
p.setSchool(new School("浙江大学"));
p.setAge(22);
db.insertCascade(p);
System.out.println(p.getId());
Person query = new Person();
query.setId(p.getId());
query = db.load(query);
assertEquals("浙江大学", query.getSchoolName());
System.out.println("=========== testRefInsert End ==========");
}
/**
* 测试分组
*
* @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);
}
@Test
public void testInsertCascade() throws SQLException {
Transaction db = this.db.startTransaction();
List<School> schools = db.selectAll(School.class);
School s = schools.get(0);
Person p = new Person();
p.setAge(12);
p.setBirthDay(new Date());
p.setCell("123433454");
p.setFriendComment(new String[] {"AA"});
p.setGender("M");
p.setLastModified(new Date());
p.setParentId(1);
p.setSchool(s);
p.setName("刘备");
p.setSchoolId(3);
db.insertCascade(p);
System.out.println(p.getSchoolId());
db.rollback(true);
}
