【知识整理】Hibernate-QBC查询

QBC查询：QBC查询就是使用hibernate提供的Query By Criteria API来查询对象，这种API封装了SQL语句的动态拼装，对查询提供了更加面向对象的功能接口。 

一.QBC查询步骤：

  Employee、Department持久化类代码如下：(Employee与Department为双向多对一关联关系)

package com.lyn.hibernate.qbc;

public class Employee {
	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 + "]";
	}
}

package com.lyn.hibernate.qbc;

import java.util.HashSet;
import java.util.Set;

public class Department {
	private Integer id;
	private String name;
	private Set<Employee> employee = new HashSet<Employee>();
	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 Set<Employee> getEmployee() {
		return employee;
	}
	public void setEmployee(Set<Employee> employee) {
		this.employee = employee;
	}
	@Override
	public String toString() {
		return "Department [id=" + id + ", name=" + name + ", employee=" + employee + "]";
	}
}

 1.创建一个Criteria对象

 Criteria criteria = session.createCriteria(Employee.class);

 2.添加查询条件，在QBC中查询条件使用Criteria来表示

//Criteria可以通过Restrictions的静态方法得到
criteria.add(Restrictions.eq("email", "d.163.com")); //查询出EMPLOYEE表中email为d.163.com且salary大于8000的记录
criteria.add(Restrictions.gt("salary", 8000f));

 3.执行查询

Employee emp = (Employee) criteria.uniqueResult();
System.out.println(emp);

二.and条件查询

Criteria criteria = session.createCriteria(Employee.class);
Conjunction conjunction = Restrictions.conjunction(); //逻辑与
conjunction.add(Restrictions.like("name", "e", MatchMode.ANYWHERE));
Department department = new Department();
department.setId(6);
conjunction.add(Restrictions.eq("department", department));  //查询EMOLOYEE表中name字段中含"e"字母且department字段为id=6的Department对象
criteria.add(conjunction);
List<Employee> list = criteria.list();
System.out.println(list);

三.or条件查询

Criteria criteria = session.createCriteria(Department.class);
Disjunction disjunction = Restrictions.disjunction(); //逻辑或
disjunction.add(Restrictions.like("name", "术", MatchMode.ANYWHERE));
disjunction.add(Restrictions.idEq(2));   //查询DEPARTMENT表中name字段含"术"字或id为2的记录
criteria.add(disjunction);
List<Department> dept = criteria.list();
System.out.println(dept);

四.统计查询（使用Projection表示,可以由Projections的静态方法得到）

Criteria criteria = session.createCriteria(Employee.class);
criteria.setProjection(Projections.min("salary")); //查询EMPLOYEE表中salary最低的记录
System.out.println(criteria.uniqueResult());

五.排序查询

Criteria criteria = session.createCriteria(Employee.class);
criteria.addOrder(Order.asc("salary"));  //按salary升序
criteria.addOrder(Order.desc("email"));  //按email降序

//添加分页
criteria.setFirstResult(2).setMaxResults(9); //从第3条记录开始共查询9条记录
List<Employee> list = criteria.list();
System.out.println(list);