hibernate之HQL 查询
HQL 是 Hibernate Query Language 的缩写,提供更加丰富灵活、更为强大的查询能力;HQL 更接近 SQL 语句查询语法。HQL 相当于简化版的面向对象的 SQL。
一、实体
@Entity
@Table(name="tb_department")
public class Department {
@Id
@GeneratedValue
private int id;
private String name;
@OneToMany(mappedBy="department")
@LazyCollection(LazyCollectionOption.EXTRA)
private Set<Employee> employees;
}
@Entity
@Table(name="tb_employee")
public class Employee {
@Id
@GeneratedValue
private int id;
private String name;
private String no;
private Double salary;
@ManyToOne(fetch= FetchType.LAZY)//延迟加载
@JoinColumn(name="cid")//外键
private Department department;
}
二、Hibernate 提供了以下几种检索对象的方式
①基于Query 的参数绑定查询
String hql = "FROM Employee e WHERE e.salary > ?1 AND e.name LIKE ?2 AND e.department = ?3 ORDER BY e.salary";
Query<Employee> query = session.createQuery(hql);
Department dept = new Department();
dept.setId(1);
List<Employee> employees = query
.setParameter(1, 300.0)
.setParameter(2, "张%")
.setParameter(3, dept).list();
System.out.println(employees);
②基于命名参数的查询
String hql = "FROM Employee e WHERE e.salary > :sal AND e.name LIKE :name";
Query<Employee> query = session.createQuery(hql);
List<Employee> employees = query
.setParameter("sal", 300.0)
.setParameter("name", "张%").list();
System.out.println(employees);
③分页查询
String hql = "FROM Employee";
Query<Employee> query = session.createQuery(hql);
int pageNo = 2;
int pageSize = 2;
List<Employee> employees = query
.setFirstResult((pageNo - 1) * pageSize)
.setMaxResults(pageSize).list();
System.out.println(employees);
④对象查询
String hql = "SELECT e.name, e.salary, e.department FROM Employee e WHERE e.department = :department";
Query query = session.createQuery(hql);
Department department = new Department();
department.setId(1);
List<Object[]> result = query.setParameter("department", department).list();
for (Object[] objects : result) {
System.out.println(Arrays.toString(objects));
}
⑤构造控制查询
String hql = "SELECT new Employee(e.name, e.salary, e.department) FROM Employee e WHERE e.department = :department";
Query<Employee> query = session.createQuery(hql);
Department department = new Department();
department.setId(1);
List<Employee> result = query.setParameter("department", department).list();
⑥分组查询
String hql = "SELECT e.department, min(e.salary), max(e.salary) FROM Employee e GROUP BY e.department HAVING min(salary) > :minSal";
Query query = session.createQuery(hql).setParameter("minSal", 200.0);
List<Object[]> result = query.list();
for (Object[] objects : result) {
System.out.println(Arrays.toString(objects));
}
⑦左连接查询
String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.employees";
//String hql = "FROM Department d INNER JOIN FETCH d.employees";
List<Department> departments = session.createQuery(hql).list();
// departments = new ArrayList<>(new HashSet<>(departments));
for (Department dept : departments) {
System.out.println(dept.getName() + "-" + dept.getEmployees().size());
}
三、Criteria 查询
hibernate 增加了 criteria[kraɪ’tɪəriə] 可以添加大量的 Restrictions[rɪsˈtrɪkʃənz] 里面的静态方法,用于各种条件查询。
Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.eq("name", "张三"));
criteria.add(Restrictions.ge("salary", 500.0));
Employee employee = (Employee) criteria.uniqueResult();
Criteria and 和 or 查询需要使用另外两个对象,Conjunction 用于添加 and 条件,Disjunction 用于添加 or 条件。
Criteria criteria = session.createCriteria(Employee.class);
Conjunction conjunction = Restrictions.conjunction();
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Restrictions.ge("salary", 500.0));
disjunction.add(Restrictions.isNull("no"));
criteria.add(disjunction);
List list = criteria.list();
System.out.println(list);
Criteria 统计查询
Criteria criteria = session.createCriteria(Employee.class);
// 统计查询: 使用 Projection 来表示: 可以由 Projections 的静态方法得到
criteria.setProjection(Projections.max("salary"));
System.out.println(criteria.uniqueResult());
Criteria 排序与分页
Criteria criteria = session.createCriteria(Employee.class);
criteria.addOrder(Order.asc("salary"));
criteria.addOrder(Order.desc("name"));
int pageSize = 2;
int pageNo = 1;
List list = criteria.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize).list();
System.out.println(list);
原生 SQL 执行
String sql = "INSERT INTO tb_department VALUES(?, ?)";
Query query = session.createSQLQuery(sql);
query.setParameter(1, 280).setParameter(2, "hhh").executeUpdate();