Hibernate之五 HQL&QBC

一 HQL

1.1使用占位符

//1.创建Query对象
String hql = "FROM Employee e WHERE e.salary > ? AND e.email LIKE ?";
Query query = session.createQuery(hql);

//2.绑定参数
query.setFloat(0,6000);
query.setString(1,"%A%");

//3.执行查询
List<Employee> emps = query.list();

1.2 使用命名参数

//1.创建Query对象
String hql = "FROM Employee e WHERE e.salary > :sal AND e.email LIKE :email";
Query query = session.createQuery(hql);

//2.绑定参数
query.setFloat("sal",6000);
query.setString("email","%A%");

//3.执行查询
List<Employee> emps = query.list();

1.3 setEntity

//1.创建Query对象
String hql = "FROM Employee e WHERE e.salary > :sal AND e.email LIKE :email AND e.dept = :dept";
Query query = session.createQuery(hql);

//2.绑定参数
Department dept = new Department();
dept.setId(20);
query.setFloat("sal",6000)
     .setString("email","%A%")
	 .setEntity("dept",dept);

//3.执行查询
List<Employee> emps = query.list();

2 分页查询

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

3 命名查询

//1.在Employee.hbm.xml文件中配置
<query name="salaryEmps">
	<![CDATA[
		FROM Employee e WHERE e.salary > :minSal AND e WHERE e.salary < :maxSal
	]]>
</query>
Query query = session.getNamedQuery("salaryEmps");

List<Employee> emps = query.setFloat("minSal",6000)
						   .setFloat("maxSal",10000)
						   .list();

4.投影查询

String hql = "SELECT new Employee(e.email, e.salary, e.dept) "
		+ "FROM Employee e WHERE e.dept = :dept";
Query query = session.createQuery(hql);

Department dept = new Department();
dept.setId(20);
List<Employee> emps = query.setEntity("dept",dept)
	 .list();

5.报表查询

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", 5000);

List<Object[]> result = query.list();	 

二 QBC

1 查询

//1.创建一个Criteria对象
Criteria criteria = session.createCriteria(Employee.class);

//2.添加查询条件：在QBC中查询条件使用Criterion来表示
//Criterion可以通过Restrictions的静态方法得到
criteria.add(Restrictions.eq("email","SKUMAR");
criteria.add(Restrictions.gt("salary",5000F);

//执行查询
Employee employee = (Employee)criteria.uniqueResult();

2 AND OR

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

//1. AND: 使用 Conjunction 表示
//Conjunction 本身就是一个 Criterion 对象
//且其中还可以添加 Criterion 对象
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));
System.out.println(conjunction); 

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

criteria.add(disjunction);
criteria.add(conjunction);

criteria.list();

3 统计查询

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

//统计查询: 使用 Projection 来表示: 可以由 Projections 的静态方法得到
criteria.setProjection(Projections.max("salary"));

System.out.println(criteria.uniqueResult()); 	

4 翻页

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

//1. 添加排序
criteria.addOrder(Order.asc("salary"));
criteria.addOrder(Order.desc("email"));

//2. 添加翻页方法
int pageSize = 5;
int pageNo = 3;
criteria.setFirstResult((pageNo - 1) * pageSize)
		.setMaxResults(pageSize)
		.list();

4 INSERT

String sql = "INSERT INTO gg_department VALUES(?, ?)";
Query query = session.createSQLQuery(sql);

query.setInteger(0, 280)
	 .setString(1, "ATGUIGU")
	 .executeUpdate();

5 UPDATE

String hql = "DELETE FROM Department d WHERE d.id = :id";

session.createQuery(hql).setInteger("id", 280)
						.executeUpdate();