hibernate之HQL 查询与Criteria 查询

  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();