hibernate(三)之HQL查询

一、HQL

测试实体如下

@Entity
@Table(name="tb_department")
public class Department  {
    @Id
    @GeneratedValue
    private int id;
    private String name;

    @OneToMany(mappedBy="department")//由对方的classroom这个属性来维护关系
    @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 提供了以下几种检索对象的方式

1.基于Query的参数绑定查询

String hql = "FROM Employee e WHERE e.salary > ? AND e.name LIKE ? AND e.department = ? ORDER BY e.salary";
Query query = session.createQuery(hql);
Department dept = new Department();
dept.setId(1);
List<Employee> employees=query.setFloat(0, 6000).setString(1, "张%").setEntity(2, dept).list();

2.基于命名参数的查询

String hql = "FROM Employee e WHERE e.salary > :sal AND e.name LIKE :name";
Query query = session.createQuery(hql);
List<Employee> employees = query.setFloat( "sal", 7000 ).setString( "name", "张%" ).list();

3.分页查询

String hql = "FROM Employee";
Query query = session.createQuery(hql);
int pageNo = 2;
int pageSize = 5;
List<Employee> emps = query.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize).list();

4.对象查询

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.setEntity("department", department).list();

5.构造控制查询

String hql = "SELECT new Employee(e.email, e.salary, e.department) FROM Employee e WHERE e.department= :department";
Query query = session.createQuery(hql);
Department department= new Department();
department.setId(80);
List<Employee> result = query.setEntity("department", department).list();

6.分组查询

String hql = "SELECT min(e.salary), max(e.salary) FROM Employee e GROUP BY e.dept HAVING min(salary) > :minSal";
Query query = session.createQuery(hql).setFloat("minSal", 8000);
List<Object[]> result = query.list();

7.左连接查询

String hql = "FROM Department d INNER JOIN FETCH d.employees";
Query query = session.createQuery(hql);

List<Department> depts = query.list();
depts = new ArrayList<>(new LinkedHashSet(depts));//去重
System.out.println(depts.size());

for (Department dept : depts) {
	System.out.println(dept.getName() + "-" + dept.getEmps().size());
}
或者用DISTINCT 去重
String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.emps";

8.测试fetch=FetchType.EAGER与LAZY

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="dept_id")
private Department dept;

String hql = "SELECT e FROM Employee e INNER JOIN e.dept";
Query query = session.createQuery(hql);

List<Employee> emps = query.list();
System.out.println(emps.size());

for (Employee emp : emps) {
	System.out.println(emp.getName() + ", " + emp.getDept().getName());
}

9.Criteria查询

Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.eq("email", "SKUMAR"));//等于
criteria.add(Restrictions.gt("salary", 5000F));//大于
Employee employee = (Employee) criteria.uniqueResult();

criteria 可以添加大量的 Restrictions里面的静态方法，用于条件查询

Restrictions.isNull(“email”)

Restrictions.ge(“salary”, 6000F)

9.Criteria查询与或查询

Criteria criteria = session.createCriteria(Employee.class);
Conjunction conjunction = Restrictions.conjunction();
conjunction.add(Restrictions.like("name", "a", MatchMode.ANYWHERE));
Department dept = new Department();
dept.setId(80);
conjunction.add(Restrictions.eq("dept", dept));
criteria.add(conjunction);

// 2. OR
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Restrictions.ge("salary", 6000F));
disjunction.add(Restrictions.isNull("email"));
criteria.add(disjunction);
criteria.list();

10.Criteria统计查询

Criteria criteria = session.createCriteria(Employee.class);
// 统计查询: 使用 Projection 来表示: 可以由 Projections 的静态方法得到
criteria.setProjection(Projections.max("salary"));
System.out.println(criteria.uniqueResult());

11.Criteria排序与分页

Criteria criteria = session.createCriteria(Employee.class);
criteria.addOrder(Order.asc("salary"));
criteria.addOrder(Order.desc("email"));

int pageSize = 5;
int pageNo = 3;
criteria.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize).list();

12.普通SQL执行

String sql = "INSERT INTO gg_department VALUES(?, ?)";
Query query = session.createSQLQuery(sql);
query.setInteger(0, 280).setString(1, "hhh").executeUpdate();

三、缓存

一级缓存是 Session 级别的缓存，它是属于事务范围的缓存。这一级别的缓存由 hibernate 管理的

二级缓存是 SessionFactory 级别的缓存，它是属于进程范围的缓存

导入hibernate里面ehcache三个jar包

<dependency>
    <groupId>net.sf.ehcache</groupId>
    <artifactId>ehcache-core</artifactId>
    <version>2.4.4</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-ehcache</artifactId>
    <version>4.2.4.Final</version>
</dependency>

配置文件里面

<!-- 启用二级缓存 -->
<property name="cache.use_second_level_cache">true</property>
    	
<!-- 配置使用的二级缓存的产品 -->
<property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property>

实体文件里面

@Entity
@Table(name="gg_employee")
@Cache(usage=CacheConcurrencyStrategy.READ_WRITE)
public class Employee {

READ_WRITE：实体读/写缓存

允许更新，更新后自动同步到缓存。
允许新增，新增记录后自动同步到缓存。
保证read committed隔离级别及可重复读隔离级别（通过时间戳实现）
整个过程加锁，如果当前事务的时间戳早于二级缓存中的条目的时间戳，说明该条目已经被别的
事务修改了，此时重新查询一次数据库，否则才使用缓存数据，因此保证可重复读隔离级别。
读写缓存和不严格读写缓存在实现上的区别在于，读写缓存更新缓存的时候会把缓存里面的数据换成一个锁

NONSTRICT_READ_WRITE：实体非严格读/写缓存
  允许更新，更新后缓存失效，需再查询一次。
  允许新增，新增记录自动加到二级缓存中。
  整个过程不加锁。

TRANSACTIONAL：实体事务缓存
  缓存支持事务，发生异常的时候，缓存也能够回滚，只支持jta环境

READ_ONLY：实体只读缓存
  只读缓存不允许更新，将报错Can’t write to a readonly object。
  允许新增，（从2.0以后新增直接添加到二级缓存）

最好将fetch=FetchType.LAZY

Employee employee = (Employee) session.get(Employee.class, 1);
System.out.println(employee.getName());

transaction.commit();
session.close();
session = sessionFactory.openSession();
transaction = session.beginTransaction();

Employee employee2 = (Employee) session.get(Employee.class, 1);
System.out.println(employee2.getName());

Query 与Criteria的缓存需要设置

Query query = session.createQuery("FROM Employee");
query.setCacheable(true);

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

调用存储过程

session.doWork(new Work() {
@Override
public void execute(Connection connection) throws SQLException {
    // 通过 JDBC 原生的 API 进行操作, 效率最高, 速度最快!
    CallableStatement cStmt = connection.prepareCall("{call demoSp(?, ?)}");
    cStmt.setInt(1, 0);
    cStmt.registerOutParameter(2, Types.INTEGER);
    cStmt.execute();
    System.out.println(cStmt.getString(2));
    }
});

创建存储过程

DELIMITER $
CREATE 
PROCEDURE `hibernate`.`demoSp` (IN sex_id INT, OUT user_count INT) 
BEGIN
  IF sex_id = 0 
  THEN 
  SELECT 
    COUNT(*) 
  FROM
    hibernate.gg_employee
  WHERE gg_employee.name = '张三' INTO user_count ;
  ELSE 
  SELECT 
    COUNT(*) 
  FROM
    hibernate.gg_employee
  WHERE gg_employee.name = '李四' INTO user_count ;
  END IF ;
END $
DELIMITER ;

调用存储过程

DELIMITER ;
SET @user_count = 0;
CALL `hibernate`.`demoSp`(0, @user_count);
SELECT @user_count;