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

喜欢ヅ旅行 2021-07-24 13:15 573阅读 0赞

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

**执行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70][]

**完整代码：**

（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 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 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("=======================================================");
        }
    }

**执行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 1][]

**Hibernate输出的SQL执行语句：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 2][]

**数据库脚本：**

（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','人事部');

![20191023125602455.png][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 3][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70]: /images/20210724/6370c886d864429ea8d3cd821264768f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 1]: /images/20210724/b817ef012aaf4a45a51017db061b58f0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 2]: /images/20210724/1e22e13d542b463a8ec638d344fd4bd4.png
[20191023125602455.png]: /images/20210724/fc436debf65648dcab64a89467a4c320.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 3]: /images/20210724/858380e1f77343afac21301205d36f80.png