Hibernate使用LEFT JOIN和GROUP BY关联与分组统计数据。
【示例】将员工信息表与部门信息表关联,并分组统计相关员工信息。
关键代码:
//根据部门分组,汇总员工信息
String hql_bmqk = "SELECT staff.departmentInfo.departmentCode,staff.departmentInfo.departmentName,COUNT(staff.id)\n" +
",AVG(staff.age),MAX(staff.age),MIN(staff.age)\n" +
"FROM StaffInfo staff\n" +
"LEFT JOIN staff.departmentInfo\n" +
"GROUP BY staff.departmentInfo.departmentCode";
List<Object[]> summarizeList = staffDao.find(hql_bmqk);
执行结果:
完整代码:
(1)创建 StaffInfo.java(员工信息持久化类)。
package com.pjb.ssh.entity;
/**
* 员工信息持久化类
*
* @author pan_junbiao
**/
public class StaffInfo
{
private int id; //员工ID(主键、自增)
private String name; //员工姓名
private int age; //年龄
private DepartmentInfo departmentInfo; //部门信息
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 int getAge()
{
return age;
}
public void setAge(int age)
{
this.age = age;
}
public DepartmentInfo getDepartmentInfo()
{
return departmentInfo;
}
public void setDepartmentInfo(DepartmentInfo departmentInfo)
{
this.departmentInfo = departmentInfo;
}
}
(2)创建 StaffInfo.hbm.xml (员工信息的Hibernate对象关系映射文件)。
<?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="com.pjb.ssh.entity.StaffInfo" table="tb_staff">
<!-- 员工ID(主键、自增) -->
<id name="id" column="id" type="int">
<!-- 持久化类的唯一性标识 -->
<generator class="native"/>
</id>
<!-- 员工姓名 -->
<property name="name" type="string" not-null="true" length="50">
<column name="NAME"/>
</property>
<!-- 年龄 -->
<property name="age" type="int">
<column name="age"/>
</property>
<!-- 多对一关联映射 -->
<many-to-one name="departmentInfo" class="com.pjb.ssh.entity.DepartmentInfo" property-ref="departmentCode" cascade="none">
<column name="department_code"/>
</many-to-one>
</class>
</hibernate-mapping>
(3)创建 DepartmentInfo.java(部门信息持久化类)。
package com.pjb.ssh.entity;
import java.util.Set;
/**
* 部门信息持久化类
* @author pan_junbiao
**/
public class DepartmentInfo
{
private int id; //部门ID(主键、自增)
private String departmentCode; //部门编号
private String departmentName; //部门名称
private Set<StaffInfo> staffInfoSet; //员工实体对象集合
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getDepartmentCode()
{
return departmentCode;
}
public void setDepartmentCode(String departmentCode)
{
this.departmentCode = departmentCode;
}
public String getDepartmentName()
{
return departmentName;
}
public void setDepartmentName(String departmentName)
{
this.departmentName = departmentName;
}
public Set<StaffInfo> getStaffInfoSet()
{
return staffInfoSet;
}
public void setStaffInfoSet(Set<StaffInfo> staffInfoSet)
{
this.staffInfoSet = staffInfoSet;
}
}
(4)创建 DepartmentInfo.hbm.xml(部门信息的Hibernate对象关系映射文件)。
<?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="com.pjb.ssh.entity.DepartmentInfo" table="tb_department">
<!-- 部门ID(主键、自增) -->
<id name="id" column="id" type="int">
<!-- 持久化类的唯一性标识 -->
<generator class="native"/>
</id>
<!-- 部门编号 -->
<property name="departmentCode" type="string" not-null="true" length="50">
<column name="department_code"/>
</property>
<!-- 部门名称 -->
<property name="departmentName" type="string" not-null="true" length="50">
<column name="department_name"/>
</property>
<!-- 一对多 员工列表 -->
<set name="staffInfoSet" inverse="true">
<key column="department_code" property-ref="departmentCode"/>
<one-to-many class="com.pjb.ssh.entity.StaffInfo"/>
</set>
</class>
</hibernate-mapping>
(5)编写执行方法
/**
* 员工信息汇总
*
* @author pan_junbiao
*/
@Override
public void summarizeStaff()
{
//根据部门分组,汇总员工信息
String hql_bmqk = "SELECT staff.departmentInfo.departmentCode,staff.departmentInfo.departmentName,COUNT(staff.id)\n" +
",AVG(staff.age),MAX(staff.age),MIN(staff.age)\n" +
"FROM StaffInfo staff\n" +
"LEFT JOIN staff.departmentInfo\n" +
"GROUP BY staff.departmentInfo.departmentCode";
List<Object[]> summarizeList = staffDao.find(hql_bmqk);
//显示结果
for (Object[] item : summarizeList)
{
System.out.println("部门编号:" + item[0] + " 部门名称:" + item[1]);
System.out.println("员工数量:" + item[2] + ";平均年龄:" + item[3] + ";最大年龄:" + item[4] + ";最小年龄:" + item[5]);
System.out.println("=======================================================");
}
}
执行结果:
Hibernate输出的SQL执行语句:
数据库脚本:
(1)创建 tb_department(部门信息表),并添加数据。
-- 创建数据表:tb_department(部门信息表)
DROP TABLE IF EXISTS tb_department;
CREATE TABLE IF NOT EXISTS tb_department
(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '部门ID(主键、自增)',
department_code VARCHAR(50) UNIQUE NOT NULL COMMENT '部门编号',
department_name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT = '部门信息表';
-- 添加数据:tb_department(部门信息表)
INSERT INTO tb_department(department_code,department_name) VALUES('1001','研发部');
INSERT INTO tb_department(department_code,department_name) VALUES('1002','财务部');
INSERT INTO tb_department(department_code,department_name) VALUES('1003','人事部');
(2)创建 tb_staff(员工信息表),并添加数据。
-- 创建数据表:tb_staff(员工信息表)
DROP TABLE IF EXISTS tb_staff;
CREATE TABLE IF NOT EXISTS tb_staff
(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID(主键、自增)',
NAME VARCHAR(50) NOT NULL COMMENT '员工姓名',
age INT COMMENT '年龄',
department_code VARCHAR(50) NOT NULL COMMENT '部门编号'
) COMMENT = '员工信息表';
-- 添加数据:tb_staff(员工信息表)
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_001',32,'1001');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_002',21,'1001');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_003',45,'1001');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_004',28,'1001');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_005',31,'1001');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_006',18,'1002');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_007',23,'1002');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_008',36,'1002');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_009',29,'1002');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_010',56,'1003');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_011',25,'1003');
INSERT INTO tb_staff(NAME,age,department_code) VALUES('pan_junbiao的博客_012',41,'1003');