- <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();
- }
- }
- }
Hibernate主要支持两种查询方式:HQL查询和Criteria查询。前者应用较为广发,后者也只是调用封装好的接口。 现在有一个问题,就是实现多表连接查询,且查询结果集不与任何一个实体类对应,怎么解决呢? 举个例子: 现在有两个表,一个users用户表, 一个goods商品表,每个用户可以有多个商品,而一个商品只能对应一个用户。 users表中的字段:userId,userName,telephone,address goods表中的字段:goodsId,goodsName,userId 现在要实现两表连接查询,查出每个用户所拥有的商品,并把该用户的信息和其商品信息显示出来。 使用Hibernate反向生成的实体类分别是Users和Goods。 有两种方式: (1)使用传统方式: String hql="select u.userName, u.telephone, u.address, g.goodsName from Users u, Goods g where u.userId=g.userId"; 根据这个查询语句,调用query.list()方法得到一个List值,这个List中的每一个值都是Object[]类型的,里面包含了查询出来的所有值,剩下的自个儿去处理就行了
例如:需要将查询的结果集 进行一下转换:
List stuList = scoreService.findAllScore(queryScore, null); // 返回的结果集
if(stuList != null && stuList.size()>0){
list = new LinkedList();
StudentScore st;
for(int i = 0; i < stuList.size();i++){
st = new StudentScore();
Object[] object = (Object[])stuList.get(i);// 每行记录不在是一个对象 而是一个数组
String userId = (String)object[0];
String username = (String)object[1];
String truename = (String)object[2];
String sex = (String)object[3];
String idnum = (String)object[4];
String level = (String)object[5];
Double sumScore = Double.parseDouble(String.valueOf(object[6]));
String paperId = (String)object[7];
// 重新封装在一个javabean里面
st.setUserId(userId);
st.setUsername(username);
st.setTruename(truename);
st.setIdnum(idnum);
st.setLevel(DictSwitch.getValue("DICT_LEVEL",level));
st.setSex(DictSwitch.getValue("DICT_SEX",sex));
st.setPaperId(paperId);
st.setSumScore(sumScore);
st.setExamplace(DictSwitch.getValue("DICT_EXAMSTATION",examplace));
list.add(st); // 最终封装在list中 传到前台。
}(2)增加一个映射类 增加一个映射类UsersVoGoods.java,添加需要查询的信息相关的所有属性,本例中添加userName, telephone, address, goodsName。并为这几个属性添加setter和getter方法,增加构造函数,参数与这四个属性对应,那么可以用hql查询方式: String hql = "select new com.test.UsersVoGoods(u.userName, u.teltphone, u.address, g.goodsName) from Users u, Goods g where u.userId=g.userId"; query.list()的返回值List中的值都是UsersVoGoods型的,直接使用get()就能获取。 其实不增加映射类也是可以的,只需要在Users.java实体类里增加一个构造函数,函数参数还是需要的所有字段,并为这些参数中Users实体原来没有的字段添加属性和getter() setter()即可。