【知识整理】Hibernate-HQL查询之分页查询、投影查询、报表查询

1.HQL分页查询
（1）setFirstResult(int firstResult):设定从哪一个对象开始检索，参数firstResult表示这个对象在查询结果中的索引位置，索引位置的起始值为0，默认情况下，Query从查询结果中的第一个对象开始检索。

（2）setMaxResults(int maxResults):设定一次最多检索出的对象数目，在默认情况下，Query和Criteria接口检索出查询结果中的所有对象

String hql = "from Employee";  //从Employee的持久化类中查询数据
Query query = session.createQuery(hql); //创建查询语句
List<Employee> employee = query.setFirstResult(3).setMaxResults(4).list();  //设定从索引为3（索引从0开始算起）的记录开始，共查询出4条记录
System.out.println(employee);

2.投影查询：查询结果仅包含实体的部分属性，通过select关键字实现。
//前提是为Employee类建立带如下参数的构造器

package com.lyn.hibernate.hql;

public class Employee {
	public Employee() {
		super();
	}
	public Employee(float salary, String email, Department department) {
		super();
		this.salary = salary;
		this.email = email;
		this.department = department;
	}
	
	private Integer id;
	private String name;
	private float salary;
	private String email;
	private Department department;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public float getSalary() {
		return salary;
	}
	public void setSalary(float salary) {
		this.salary = salary;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Department getDepartment() {
		return department;
	}
	public void setDepartment(Department department) {
		this.department = department;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", name=" + name + "]";
	}
}

例：查询Employee的持久化类中的salary、email、department字段，并输出。

String hql = "select new Employee(e.salary, e.email, e.department) from Employee e where e.department = :department";
Query query = session.createQuery(hql);
Department department = new Department();
department.setId(5);
List<Employee> result = query.setEntity("department", department).list();
for(Employee emp : result){
	System.out.println(emp.getId() + ", " + emp.getEmail() + ", " + 	emp.getDepartment());
}

3.报表查询：报表查询用于对数据分组和统计，与SQL一样，HQL利用group by关键字对数据进行分组，用having关键字对分组数据设定约束条件。
（1）在HQL查询中可以调用一下聚合函数count();,min();,max();,sum();,avg();

例：根据Employee持久化类中的deartment字段中进行分组找出其中最低工资大于Employee持久化类中的最低工资的记录，从而分组查询出它们的最低、最高工资。

String hql = "select min(e.salary), max(e.salary) from Employee e group by e.department having min(e.salary) > :minSal";
Query query = session.createQuery(hql);
query.setFloat("minSal", 5000);
List<Object[]> list = query.list();
for(Object[] obj : list){
	System.out.println(Arrays.asList(obj));
}