Hibernate--HQL、QBC以及本地SQl查询

Hibernate中提供了HQL、QBC以及SQL的三种形式查询，HQL查询是一种根据映射文件的便捷查询，只要写部分SQL语句即可。QBC查询则是借助Criteria 进行快捷的查询。SQL查询是基于SQL语句的查询

以下案例用于查询的两个表结构

CREATE TABLE `department` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `employee` (
  `Id` int(2) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(30) DEFAULT NULL,
  `SALARY` float(10,2) DEFAULT NULL,
  `EMAIL` varchar(30) DEFAULT NULL,
  `DEPT_Id` int(2) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `dept_id_fk` (`DEPT_Id`),
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`DEPT_Id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

department实体以及映射文件

public class Department implements java.io.Serializable {

	private static final long serialVersionUID = 1L;
	private Integer id;
	private String name;
	private Set<Employee> employees = new HashSet<>();
}

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- 
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="com.zhuojing.hibernate.hql.holleworld.Department" table="department" catalog="hibernate2">
        <id name="id" type="java.lang.Integer">
            <column name="id" />
            <generator class="identity" />
        </id>
        <property name="name" type="java.lang.String">
            <column name="NAME" length="30" />
        </property>
        <set name="employees" inverse="true">
            <key>
                <column name="DEPT_Id" />
            </key>
            <one-to-many class="com.zhuojing.hibernate.hql.holleworld.Employee" />
        </set>
    </class>
</hibernate-mapping>

employee实体以及映射文件

public class Employee implements java.io.Serializable {

	private static final long serialVersionUID = 1L;
	private Integer id;
	private Department department;
	private String name;
	private Float salary;
	private String email;
}

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- 
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="com.zhuojing.hibernate.hql.holleworld.Employee" table="employee" catalog="hibernate2">
        <id name="id" type="java.lang.Integer">
            <column name="Id" />
            <generator class="identity" />
        </id>
        <many-to-one name="department" class="com.zhuojing.hibernate.hql.holleworld.Department" fetch="select">
            <column name="DEPT_Id" />
        </many-to-one>
        
        <property name="name" type="java.lang.String">
            <column name="NAME" length="30" />
        </property>
        <property name="salary" type="java.lang.Float">
            <column name="SALARY" precision="5" scale="0" />
        </property>
        <property name="email" type="java.lang.String">
            <column name="EMAIL" length="30" />
        </property>
    </class>
    
    <!-- 配置命名方式检查的 SQL 语句 -->
    <query name="salaryEmp"><![CDATA[FROM Employee e WHERE e.salary > :minSay AND e.salary < :maxSay]]></query>
</hibernate-mapping>

一、HQL

    1.命名参数：

@Test
public void testHQLParameter(){
		
		//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", 7000)
		     .setString("email", "%com");
		//3. 执行查询
		List<Employee> employees = query.list();
		
		System.out.println(employees.size());
		
	}

2.分页查询

@Test
public void testPageQuery(){
		String hql = "FROM Employee";
		Query query = session.createQuery(hql);
		
		int pageNo = 3;
		int pageSize = 2;
		List<Employee> employees = query.setFirstResult((pageNo -1 )*pageSize)
				                        .setMaxResults(pageSize)
				                        .list();
		System.out.println(employees.toString());
}

3.命名方式：在映射文件中写SQL

//*映射文件
<!-- 配置命名方式检查的 SQL 语句 -->
    <query name="salaryEmp"><![CDATA[FROM Employee e WHERE e.salary > :minSay AND e.salary < :maxSay]]></query>
*/
@Test
	public void testNamedQuery(){
		Query query = session.getNamedQuery("salaryEmp");
		List<Employee> employees = query.setFloat("minSay", 6000)
				                        .setFloat("maxSay", 10000)
				                        .list();
		System.out.println(employees.size());
	}

4.查找部分属性

@Test
public void testFieldQuery(){
		String hql = "SELECT e.name, e.email, e.department FROM Employee e WHERE e.department = :dept";
		
		Query query = session.createQuery(hql);
		Department department = new Department();
		department.setId(3);
		List<Object[]> objects = query.setEntity("dept", department).list();
		for(Object[] obj : objects){
			System.out.println(Arrays.asList(obj));
		}
}

5.查找部分属性，返回结果为一个实体类：实体类需要提供对应的构造方法

@Test
public void testFieldQuery2(){
		String hql = "SELECT new Employee(e.name, e.email, e.department) FROM Employee e WHERE e.department = :dept";
	
		Query query = session.createQuery(hql);
		Department department = new Department();
		department.setId(3);
		
		List<Employee> employees = query.setEntity("dept", department).list();
		
		for(Employee employee : employees){
			System.out.println(employee);
		}
	}

6.迫切左外连接：使用LEFT JOIN FETCH 进行迫切左外连接

用于：查询一个实体的一个实体类的成员变量时，不会在使用 SQL 语句去查询，在第一次就将结果初始化好

@Test
public void testLeftJoinFetch(){
		String hql = "FROM Department d LEFT JOIN FETCH d.employees";
		Query query = session.createQuery(hql);
		
		List<Department> departments = query.list();
		departments = new ArrayList<>(new LinkedHashSet<>(departments));
		for(Department department : departments){
			System.out.println(department.getName() +"--"+department.getEmployees().size());
		}
	}

7.左外连接

@Test
public void testLefJoin(){
		String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.employees";
		Query query = session.createQuery(hql);
		
		List<Department> departments = query.list();
		
		for(Department department : departments){
			System.out.println(department.getName() +"--"+department.getEmployees().size());
		}
		
	}

二、QBC查询主要使用Criteria进行查询

  1.QBC的HelloWorld

@Test
	public void testQBC(){
		//创建一个 Criteria 对象
		Criteria criteria = session.createCriteria(Employee.class);
		
		//2.添加查询条件：在QBC 中查询条件使用 Criterion 表示
		// Criterion 可以通过Restrictions 的静态方法得到
		criteria.add(Restrictions.eq("email", "bb@zhuoojing.com"));
		criteria.add(Restrictions.ge("salary", 6000F));
		
		//3.执行查询
		Employee employee = (Employee)criteria.uniqueResult();
		System.out.println(employee);
	}

2.QBC 统计查询：

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

3.排序和分页

@Test
public void testQBC4(){
		Criteria criteria = session.createCriteria(Employee.class);
		
		//添加排序
		criteria.addOrder(Order.asc("salary"));
		criteria.addOrder(Order.desc("email"));
		List<Employee> employees = criteria.list();
		System.out.println(employees);
		
		//2.添加分页方法
		int pageSize = 2;
		int pageNo = 3;
		Criteria criteria3 = session.createCriteria(Employee.class);
		criteria3.setFirstResult((pageNo - 1) *pageSize)
		         .setMaxResults(pageSize);
		List<Employee> employees2 = criteria3.list();
		System.out.println(employees2);
		         
}
	

三、SQL查询（使用sql语句）

@Test
	public void testNativeSQL(){
		//String sql = "INSERT INTO employee(NAME,SALARY,EMAIL,DEPT_Id) VALUES('ZZ',7000,'zz@zhuojing.com',5)";
		String sql = "INSERT INTO department(NAME) VALUES('ZHUOJING')";
		Query query = session.createSQLQuery(sql);
		
		/*query.setString(0, "ZZ")
		     .setFloat(1, 7000F)
		     .setString(2, "zz@zhuojing.com")
		     .setInteger(3, 4)
		     .executeUpdate();*/
		query.executeUpdate();
	}