Hibernate使用LEFT JOIN和GROUP BY关联与分组统计数据

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');