Hibernate中的HQL的基本常用小例子，单表查询与多表查询

<span style="font-size:24px;color:#3366ff;">本文章实现HQL的以下功能：</span>
/**
 * hql语法：
 *   1）单表查询
 *   	 1.1 全表查询
 *       1.2 指定字段查询
 *       1.3 排除重复记录
 *       1.4 条件查询（重点）
 *       1.5 分页查询
 *       1.6 聚合查询
 *       1.7 查询排序
 *       1.8 分组查询
 *       1.9 分组后筛选
 *       
 *   2）多表查询
 *       1.1 内连接
 *       1.2 左外连接/右外连接   
*/
</span>

首先要配置Hibernate的xml文件，让两个数据库建立联系，是一对多的映射连接

Employee.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
	<class name="star.july.d_hql.Employee" table="employee">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name"></property>
		<property name="gender"></property>
		<property name="title"></property>
		<property name="email"></property>
		<property name="salary"></property>
		<many-to-one name="dept" 
			class="star.july.d_hql.Dept"
			column="deptId"
		></many-to-one>
	</class>
</hibernate-mapping>

Dept.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
	<class name="star.july.d_hql.Dept" table="dept">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="deptName" column="name">
		</property>
		<set name="employee"
			cascade="all"
		>
		<key column="id"></key>
			<one-to-many class="star.july.d_hql.Employee"
			/>
		</set>
	</class>
</hibernate-mapping>

再建立两个实体类

Employee.java

package star.july.d_hql;

public class Employee {
	private int id;
	private String name;
	private String gender;
	private String title;
	private String email;
	private double salary;
	private Dept dept = new Dept();
	
	public Dept getDept() {
		return dept;
	}
	public void setDept(Dept dept) {
		this.dept = 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 String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", name=" + name + ", gender=" + gender
				+ ", title=" + title + ", email=" + email + ", salary="
				+ salary + "]";
	}
	
	
}

Dept.java

package star.july.d_hql;

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

public class Dept {
	private int id;
	private String deptName;
	private Set<Employee> employee = new HashSet<Employee>();
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getDeptName() {
		return deptName;
	}
	public void setDeptName(String deptName) {
		this.deptName = deptName;
	}
	public Set<Employee> getEmployee() {
		return employee;
	}
	public void setEmployee(Set<Employee> employee) {
		this.employee = employee;
	}
	
	
}	

最后测试HQL：

Demo.java

package star.july.d_hql;

import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import star.july.util.HibernateUtil;
/**
 * hql语法：
 *   1）单表查询
 *   	 1.1 全表查询
 *       1.2 指定字段查询
 *       1.3 排除重复记录
 *       1.4 条件查询（重点）
 *       1.5 分页查询
 *       1.6 聚合查询
 *       1.7 查询排序
 *       1.8 分组查询
 *       1.9 分组后筛选
 *       
 *   2）多表查询
 *       1.1 内连接
 *       1.2 左外连接/右外连接   
 *       *
 */      
public class Demo {
	@Test
	public void test(){
		Session session = HibernateUtil.getSession();
		Transaction ts = session.getTransaction();
		try{
			ts.begin();
			//hql基本语法
			//1、创建一个Query对象
			//参数：需要执行的hql语句
			String hql = "select e from Employee e where id = 1";
			Query query = session.createQuery(hql);
			//2、执行查询
			//2、1封装所有
			List<Employee> emp = query.list();
			for(Employee e : emp){
				System.out.println(e);
			}
			
			//2、2 封装一个（第一个）
			Employee empl = (Employee)query.uniqueResult();
			System.out.println(empl);
			
			ts.commit();
			
		}catch(Exception e){
			e.printStackTrace();
			ts.rollback();
		}
	}
	
	
	//单表查询
	@Test
	public void test3(){
		Session session = HibernateUtil.getSession();
		Transaction ts = session.getTransaction();
		try{
			ts.begin();
//			 *  1）单表查询
//			 *   	 1.1 全表查询
		//	String hql = "from star.july.d_hql.Employee";
			//auto-import:自动到爆，自动在每个包下面搜索对应这个类的包，多个同名类的报会冲突
//			String hql = "select e from Employee e";
			
//			 *       1.2 指定字段查询
			//返回对象数组
//			String hql = "select e.name,e.title from Employee e";
			
//			 *       1.3 排除重复记录
//			String hql = "select distinct(e.gender) from Employee e";
			
//			 *       1.4 条件查询（重点）(where)
			//逻辑条件：and  or
			// 模糊查询：like: % _
			//比较查询： < > <= >= between and <>
			//判空查询： is null ,is not null, ='',<>'';
//			String hql = "select e from Employee e where name like '张%'";
//			String hql = "select e from Employee e where e.gender is null or e.gender=''";
			
//			 *       1.5 分页查询
		/*	String hql = "from Employee ";
			Query query = session.createQuery(hql);
			//设置开始读取行
			query.setFirstResult(0);
			//每页读取多少条信息
			query.setMaxResults(3);*/
			
//			 *       1.6 聚合查询
			//avg,count,max,min,uniqueResult
//			String hql = "select max(e.salary) from Employee e";
			
//			 *       1.7 查询排序
			//order by
			//desc：降序  asc：升序
//			String hql = "select e from Employee e order by id desc ";
			
			
//			 *       1.8 分组查询
//			String hql = "select e from Employee e group by e.gender";
		
//			 *       1.9 分组后筛选
			String hql = "select e from Employee e  where e.gender is not null and e.gender<>'' group by e.gender having count(e.gender)>1";
			
			Query query = session.createQuery(hql);
			
			
			//集合对象
			List<Object> e = query.list();
			for(Object emp : e){
				System.out.println(emp);
			}
			
			
			//对象数组
		/*	List<Object[]> objects = query.list();
			for(Object[] object : objects){
				for(Object obj:object ){
				System.out.print(obj);
				}
				System.out.println();
			}	*/		
			
			
			//封装一个对象
			/*Object unique = query.uniqueResult();
			System.out.println(unique);*/
			
			
			
			ts.commit();
		}catch(Exception e){
			e.printStackTrace();
			ts.rollback();
		}
	}
	
	
	//多表查询
		@Test
		public void test2(){
			Session session = HibernateUtil.getSession();
			Transaction ts = session.getTransaction();
			try{
				ts.begin();
				
				/**
				 *步骤
				 *1、确定查询哪些对象
				 *2、确定擦汗寻哪些属性
				 *3、确定连接条件
				 *4、业务条件 
				 */
				//1、内连接查询
				//效果：只有满足条件的数据才会被显示出来
				//查询员工及其部门：显示员工名称，部门名称
//				String hql = "select e.name,d.deptName from Employee e , Dept d where e.dept.id=d.id";
				//另一种写法
//				String hql = "select e.name,d.deptName from Employee e inner join e.dept d";
				
				
				//左外连接
				//效果：优先显示左表，右表的数据匹配显示，不匹配则显示null
				//查询所有部门的员工(没有员工的部门也要显示出来)
				String hql = "select d.deptName, e.name from Dept d left outer join d.employee e";
				
				//右外连接
//				String hql = "select d.deptName,e.name from Employee e right outer join e.dept d";
				Query query = session.createQuery(hql);
				
				/*List<Object>  object = query.list();
				for(Object obj:object){
					System.out.println(obj);
				}*/
				
				List<Object[]> objects = query.list();
				for(Object[] object:objects){
					for(Object obj : object){
						System.out.print(obj);
					}
					System.out.println();
				}
				
				
				
				
				ts.commit();
				
			}catch(Exception e){
				e.printStackTrace();
				ts.rollback();
			}
		}
}