hibernate的hq,sql,qbc举例

最新推荐文章于 2019-09-06 18:58:05 发布

原创最新推荐文章于 2019-09-06 18:58:05 发布 · 962 阅读

0 ·

CC 4.0 BY-SA版权

Hibernate 专栏收录该内容

26 篇文章

订阅专栏

本文介绍了一种使用Hibernate Query Language (HQL)进行复杂查询的方法，包括分页查询、条件组合查询、统计查询、命名查询等高级特性，并展示了如何通过Hibernate Criteria API实现更灵活的数据检索。

一.Employee

package cn.edu.sdut.hibernate.hql;

public class Employee {
	
	private int id;
	private String name;
	private float salary;
	private String email;
	private Department dept;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public float getSalary() {
		return salary;
	}
	public void setSalary(float salary) {
		this.salary = salary;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Department getDept() {
		return dept;
	}
	public void setDept(Department dept) {
		this.dept = dept;
	}
	public Employee(float salary, String email, Department dept) {
		super();
		this.salary = salary;
		this.email = email;
		this.dept = dept;
	}
	public Employee() {
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", name=" + name + ", salary=" + salary + ", email=" + email + ", dept=" + dept
				+ "]";
	}
	
	
}

二.Department

package cn.edu.sdut.hibernate.hql;

import java.util.HashSet;
import java.util.Set;


public class Department {
	
	private int id;
	private String name;
	private Set<Employee> emps = new HashSet<>();
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Set<Employee> getEmps() {
		return emps;
	}
	public void setEmps(Set<Employee> emps) {
		this.emps = emps;
	}
	
	
}

3.Department.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2016-11-3 17:12:04 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="cn.edu.sdut.hibernate.hql.Department" table="GG_DEPARTMENT">
        <id name="id" type="int">
            <column name="ID" />
            <generator class="native" />
        </id>
        <property name="name" type="java.lang.String">
            <column name="NAME" />
        </property>
        <set name="emps" table="GG_EMPLOYEE" inverse="false" lazy="true">
            <key>
                <column name="DEPT_ID" />
            </key>
            <one-to-many class="cn.edu.sdut.hibernate.hql.Employee" />
        </set>
    </class>
</hibernate-mapping>

4.Employee.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2016-11-3 17:12:04 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="cn.edu.sdut.hibernate.hql.Employee" table="GG_EMPLOYEE">
        <id name="id" type="int">
            <column name="ID" />
            <generator class="native" />
        </id>
        <property name="name" type="java.lang.String">
            <column name="NAME" />
        </property>
        <property name="salary" type="float">
            <column name="SALARY" />
        </property>
        <property name="email" type="java.lang.String">
            <column name="EMAIL" />
        </property>
        <many-to-one name="dept" class="cn.edu.sdut.hibernate.hql.Department">
            <column name="DEPT_ID" />
        </many-to-one>
    </class>
    
    
    <!-- 制定命名查询 -->
    <query name="hql"><![CDATA[from Employee e where e.salary > :sal and e.email like :email]]></query>
</hibernate-mapping>

5.test

package cn.edu.sdut.hibernate.hql;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashSet;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;


import sun.reflect.generics.tree.VoidDescriptor;


public class Test {

	private SessionFactory sessionFactory;
	private Session session;
	private Transaction transaction;
	
	@Before
	public void init(){
		Configuration configuration = new Configuration().configure();
		ServiceRegistry serviceRegistry = 
				new ServiceRegistryBuilder().applySettings(configuration.getProperties())
											.buildServiceRegistry();
		sessionFactory = configuration.buildSessionFactory(serviceRegistry);
		session = sessionFactory.openSession();
		transaction = session.beginTransaction();
	}
	
	@After
	public void destory(){
		transaction.commit();
		session.close();
		sessionFactory.close();
	}
	
	@org.junit.Test
	public void testQBC4(){
		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();
	}
	
	
	@org.junit.Test
	public void testQBC3(){
		Criteria criteria = session.createCriteria(Employee.class);
		
		//统计查询: 使用 Projection 来表示: 可以由 Projections 的静态方法得到
		criteria.setProjection(Projections.max("salary"));
		System.out.println(criteria.uniqueResult()); 
		
	}
	
	
	
	@org.junit.Test
	public void testQBC2(){
//		Criteria criteria = session.createCriteria(Employee.class);
//		Conjunction conjunction = Restrictions.conjunction();
		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();
		
	}
	
	
	
	@org.junit.Test
	public void testQBC(){
		//1. 创建一个 Criteria 对象
		Criteria criteria = session.createCriteria(Employee.class);
		//2. 添加查询条件: 在 QBC 中查询条件使用 Criterion 来表示
		//Criterion 可以通过 Restrictions 的静态方法得到
		criteria.add(Restrictions.eq("email", "SKUMAR"));
		criteria.add(Restrictions.ge("salary", 5000F));
		//3. 执行查询
		Employee employee = (Employee) criteria.uniqueResult();
		System.out.println(employee.getDept().getName());
	}
	
	
	
	@org.junit.Test
	public void testNativeSQL(){
		//本地 SQL 检索
		//本地SQL查询来完善HQL不能涵盖所有的查询特性
		String sql = "insert into gg_department values(? ,?)";
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		sqlQuery.setInteger(0, 290)
				.setString(1, "CSC");
		sqlQuery.executeUpdate();
	}
	
	@org.junit.Test
	public void testHqlExecuteUpdate(){
		//hql语句不能进行插入操作，能进行查询，更新，修改操作
		//String hql = "update Department e set e.id = 290 where e.id = 280";
		String hql = "delete Department e where id = 290";
		Query query = session.createQuery(hql);
		query.executeUpdate();
	}
	
	
	@org.junit.Test
	public void testInnerJoinFetch(){
		//INNER JOIN fetch为迫切内连接，迫切内连接返回的是左表和右表符合条件的
		//INNER JOIN为内连接，内连接返回的是左表和右表符合条件的
		//如果不加select返回的是数组的list，如果加上select返回的是对象的list
		String hql = "SELECT e FROM Employee e INNER JOIN fetch 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());
		}
	}
	
	
	@org.junit.Test
	public void testLeftJoin(){
		//左连接不会把右表的数据立刻检索出来，例如：Department的属性emps不会立即检索出来，而是用的时候才检索
		//查重方式为：在hql语句里面加上 distinct关键字,不能用list = new ArrayList<>(new LinkedHashSet<>(list));这种方式
		//左外连接如果不加select就会查询出来两个表的对象，query.list返回的是对象数组的list;
		//如果加上selectquery.list返回的是对象的list
		String hql = "from Department e left join e.emps";
		Query query = session.createQuery(hql);
		List<Object[]> list = session.createQuery(hql).list();
//		List<Department> list = query.list();
//		for(Department department : list){
//			System.out.println(department.getName());
//		}
		
		for(Object[] objects : list){
			System.out.println(Arrays.asList(objects));
		}
	}
	
	@org.junit.Test
	public void testLeftJoinFetch(){
		//left join fetch为迫切左外连接，会把左边表的全部数据输出即：
		//会把左表和右表符合条件的输出来也会把左表中不符合条件的输出来
		//String hql = "from Department e left join fetch e.emps";
		//迫切左外连接的去重方式为：①.在hql语句里面加上 distinct关键字
		//						 ②.list = new ArrayList<>(new LinkedHashSet<>(list));
		String hql = "select distinct e from Department e left join fetch e.emps";
		Query query = session.createQuery(hql);
		List<Department> list = query.list();
		//list = new ArrayList<>(new LinkedHashSet<>(list));
		System.out.println(list.size());
		for(Department department : list){
			System.out.println(department.getName());
		}
	}
	
	@org.junit.Test
	public void testGroupBy(){
		String hql = "select min(e.salary), max(e.salary) from Employee e group by e.dept having min(e.salary) > :sal";
		Query query = session.createQuery(hql)
	             .setFloat("sal", 8000);
		List<Object []> result = query.list();
		for(Object [] objs: result){
		System.out.println(Arrays.asList(objs));
		}
	}
	
	@org.junit.Test
	public void testFieldQuery2(){
		String hql = "select new Employee (e.salary, e.email, e.dept) from Employee e where e.dept = :dept";
		Query query = session.createQuery(hql);
		Department dept = new Department();
		dept.setId(80);
		//对象的比较是通过id进行比较的
		query.setEntity("dept", dept);
		List<Employee> list = query.list();
		for(Employee employee :list){
			System.out.println(employee.getEmail() + employee.getId());
		}
	}
	
	
	@org.junit.Test
	public void testFieldQuery(){
		String hql = "select e.email, e.salary, e.dept from Employee e where e.dept = :dept";
		Query query = session.createQuery(hql);
		Department dept = new Department();
		dept.setId(80);
		//对象的比较是通过id进行比较的
		query.setEntity("dept", dept);
		List<Object[]> list = query.list();
		
		for(Object[] objects : list){
			System.out.println(objects);
		}
		
		
		
	}
	
	@org.junit.Test
	//制定命名查询
	public void testNamedQuery(){
		Query query = session.getNamedQuery("hql");
		query = query.setFloat("sal", 6000).setString("email", "%A%");
		List<Employee> list = query.list();
		System.out.println(list);
	}
	
	@org.junit.Test
	public void testPageQuery(){
		String hql = "from Employee";
		int pageNo = 3;
		int pageSize = 10;
		//分页查询   setFirstResult((pageNo - 1) * pageSize)设置开始查询的起始页
		//setMaxResults(pageSize)设置分页的最大数量
		List<Employee> list = session.createQuery(hql).setFirstResult((pageNo - 1) * pageSize)
								.setMaxResults(pageSize).list();
		System.out.println(list);
	}
	
	@org.junit.Test
	public void test(){
		//String hql = "from Employee e where e.salary > ? and e.email like ?";
		String hql = "from Employee e where e.salary > :sal and e.email like :email";
		Query query = session.createQuery(hql);
//		query.setFloat(0, 6000)
//			 .setString(1, "%%");
		query.setFloat("sal", 6000)
			 .setString("email", "%A%");
		List<Employee> list = query.list();
		System.out.println(list);
	}
}