Hibernate笔记-----hql

1、DML风格：批量操作

// HQL的批量修改
String hql = "update Student set name=?1 where age>=?2";
session.createQuery(hql).setParameter(1, "测试").setParameter(2, 38).executeUpdate();

// HQL的批量删除
String hql = "delete from Student where age>=?1";
session.createQuery(hql).setParameter(1, 38).executeUpdate();

2、单表查询

//String hql = "from Student where age>=?1";
//String hql = "from Student s where s.age>=?1";
String hql = "select s from Student s where s.age>=?1";

List<Student> list = session.createQuery(hql, Student.class).setParameter(1, 30).list();

// 查询部分字段：每条记录是Object[]
String hql = "select id, name, age from Student where age>=?1";

List<Object[]> list = session.createQuery(hql, Object[].class).setParameter(1, 30).list();

// 查询部分字段：每条记录是 List
String hql = "select new list(id, name, age) from Student where age>=?1";

List<List> list = session.createQuery(hql, List.class).setParameter(1, 30).list();

// 查询部分字段：每条记录是 List
String hql = "select new map(id as id, name as name, age as age) from Student where age>=?1";

List<Map> list = session.createQuery(hql, Map.class).setParameter(1, 30).list();

3、连接查询

// 存在笛卡尔积
// String hql = "from Student, Clazz";
String hql = "select s, c from Student s, Clazz c";

List<Object[]> list = session.createQuery(hql, Object[].class).list();

// 模仿sql，解决笛卡尔积（糟糕方式）
String hql = "from Student s, Clazz c where s.clazz.id=c.id and c.name=?1 and s.age<?2";

List<Object[]> list = session.createQuery(hql, Object[].class).setParameter(1, "17-1班").setParameter(2, 30).list();

// 隐式的关联：某班级中年龄<=30
String hql = "from Student where clazz.name=?1 and age<?2";

List<Student> list = session.createQuery(hql, Student.class).setParameter(1, "17-1班").setParameter(2, 30).list();

// 显式的关联：连接查询
// 内连接：join, 外连接（左连接：left join、右连接：right join）
// sql的连接查询：select x.*, y.* from tb_student x join tb_clazz on x.clazz_id=y.id where ...
String hql = "from Student s join s.clazz c where c.name=?1 and s.age<=?2";

// form前省略：select s, c
List<Object[]> list = session.createQuery(hql, Object[].class).setParameter(1, "17-1班").setParameter(2, 30).list();

// 连接查询（内连接）
String hql = "select s from Student s join s.clazz c where c.name=?1 and s.age<=?2";

List<Student> list = session.createQuery(hql, Student.class).setParameter(1, "17-1班").setParameter(2, 30).list();

4、分页和排序

// 排序和分页
// 先按age降序排，如果age相同则再按name升序排
String hql = "from Student order by age desc, name asc";

List<Student> list = session.createQuery(hql, Student.class)
                  .setFirstResult(0)   //从第几条记录开始， 从0开始算
                  .setMaxResults(5)    //每页大小
                  .list();

5、迫切连接查询

// join：连接查询，返回Object[]
// join fetch：迫切连接查询，返回单个对象
// 迫切连接查询: join fetch（性能优化）
String hql = "from Student s join fetch s.clazz c where c.name=?1 and s.age<=?2";
// 返回Student集合，并将clazz对象填充到student中

List<Student> list = session.createQuery(hql, Student.class).setParameter(1, "17-1班").setParameter(2, 30).list();

6、聚合查询

一般与分组group by 一起使用

• count()
• avg()
• max()
• min()
• sum()

//查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
String hql="select sc.student, avg(sc.num) from Score sc group by sc.student having avg(sc.num)>60";
List <Object[]> list = session.createQuery(hql, Object[].class).list();
for(Object[] s: list){
	Student st = (Student)s[0];
	System.out.println(st.getName()+","+st.getId()+","+s[1]);
}

7、子查询

• where xx in(select xx from tb)

8、分组查询

• group by user
• group by user.name having xxx