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;}@Overridepublic 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 {@Testpublic 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();}}//单表查询@Testpublic 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();}}//多表查询@Testpublic 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();}}}