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;

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论

打赏作者

faramita_of_mine

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值