Hibernate中不支持复杂子查询from (select ……)解决方案

雨点打透心脏的1/2处 2021-09-19 23:52 585阅读 0赞

## 问题分析 ##

楼主之前在维护公司之前一个项目时遇到一个坑，就是涉及到一个复杂子查询形如from（select......）形式的hql语句不支持，简单说就是先要通过子查询查询出来一张新的虚拟表，然后和其他表做关联才能得到业务所需要的最终数据。 原SQL语句如下：

SELECT k.term_id,
    		sum(k.work_time) worktime
    FROM 
        (SELECT o.term_id,
    		o.report_date,
    		o.work_time,
    		 o.term_brand,
    		o.model_name
        FROM rep_hardware_fault_rate o
        GROUP BY  o.term_id,o.report_date,
        o.work_time, o.term_brand,o.model_name) k, view_device_dept_info v
    WHERE k.term_id=v.term_id
    GROUP BY  k.term_brand;
    复制代码

我在网上查了大量资料，发现有一些求助的帖子中有类似的问题描述，但是都没有相应的解决方案。后面楼主想了下要不就简化SQL语句然后再代码中处理（这种效率很低，最笨的方法），或者在数据库中新建一个视图，但这种处理方法也不是十分完美，就这一块业务用到了，会增加数据库的开销，而且假如说有很多类似的业务，那不是得建很多张视图，这种办法可持续性也不好。后面楼主还是没放弃，就觉得应该有其他人也遇到过类似的问题，肯定有比较完美的解决方案~终于功夫不有心人，楼主参考大量的博客和资料终于找到了一种比较完美的解决方案，即建立虚拟视图法。

## 具体解决方案 ##

简单说就是将select子查询到的虚拟表建立一个实体类映射成一个虚拟视图，然后再进行关联查询操作。这里要用到一个@Subselect注解，即 `subselect (可选): 它将一个不可变（immutable）并且只读的实体映射到一个数据库的子查询中。当你想用视图代替一张基本表的时候，这是有用的，但最好不要这样做。` 对Hibernate映射来说视图和表是没有区别的，这是因为它们在数据层都是透明的（ 注意：一些数据库不支持视图属性，特别是更新的时候）。有时你想使用视图，但却不能在数据库中创建它（例如：在遗留的schema中）。这样的话，你可以映射一个不可变的（immutable）并且是只读的实体到一个给定的SQL子查询表达式：定义这个实体用到的表为同步（synchronize），确保自动刷新（auto-flush）正确执行， 并且依赖原实体的查询不会返回过期数据。subselect在属性元素和一个嵌套映射元素中都可见。

## 核心代码 ##

好啦，废话不多说，直接上核心代码，以供大家参考和借鉴。

1.  实体类

注意，虽然我们查询出来的视图没有id，但是这里必须加主键，否则hql无法正常映射，应该是必须遵从的规范。 这里的@Subselect注解是查询数据库的表数据结果，将其映射为一个实体类；@Synchronize是定义这个实体用到的表为同步（synchronize），确保自动刷新（auto-flush）正确执行。

@Entity
    @Subselect(" select o.TERM_ID,o.REPORT_DATE,o.WORK_TIME,o.TERM_BRAND,o.MODEL_NAME " +
               " from REP_HARDWARE_FAULT_RATE o " +
               " group by o.TERM_ID,o.REPORT_DATE,o.WORK_TIME,o.TERM_BRAND,o.MODEL_NAME ")
    /**
     *如果子查询涉及2个表，则这样写
     *@Synchronize( { "test_item", "test_bid" })
     */
    @Synchronize({
        "REP_HARDWARE_FAULT_RATE"})
    
    public class ViewDeviceForWorkTime {
        
        /**
         * 主键Id
         * 这里必须写，不写会报错，hql映射必须要加
         */
        @Id
        @GeneratedValue(generator = "system-uuid")
        @GenericGenerator(name = "system-uuid", strategy = "uuid")
        private String id;
    
        /**
         * 设备Id
         * 可以加Column，也可以不加，后台配置了驼峰映射法
         */
        @Column(name = "TERM_ID")
        private String termId;
    
        /**
         * 记录日期
         */
        private String reportDate;
    
        /**
         * 应工作时间
         */
        private String workTime;
    
        /**
         * 设备品牌
         */
        private String termBrand;
    
        /**
         * 设备型号
         */
        private String modelName;
    
    
    
        public String getId() {
            return id;
        }
    
        public void setId(String id) {
            this.id = id;
        }
    
        public String getTermId() {
            return termId;
        }
    
        public void setTermId(String termId) {
            this.termId = termId;
        }
    
        public String getReportDate() {
            return reportDate;
        }
    
        public void setReportDate(String reportDate) {
            this.reportDate = reportDate;
        }
    
        public String getWorkTime() {
            return workTime;
        }
    
        public void setWorkTime(String workTime) {
            this.workTime = workTime;
        }
    
        public String getTermBrand() {
            return termBrand;
        }
    
        public void setTermBrand(String termBrand) {
            this.termBrand = termBrand;
        }
    
        public String getModelName() {
            return modelName;
        }
    
        public void setModelName(String modelName) {
            this.modelName = modelName;
        }
    }
    
    复制代码

映射数据库中的表view\_device\_dept\_info。

@Entity
    @Table(name = "VIEW_DEVICE_DEPT_INFO")
    public class ViewDeviceDeptInfoForOpenRate {
    	@Id
    	private String deviceId;
    	private String termId;
    	private String termSeq;
    	private String counterCode;
    	private String termAddr;
    	private String typeId;
    	private String brandId;
    	private String modelId;
    	private String termIp;
    	private String areaAddr;
    	private String status;
    	private String companyId;
    	private String companyName;
    	private String deptId;
    	private String deptCode;
    	private String deptName;
    	private Integer deptLevel;
    	private String deptAddr;
    	private String deptId1;
    	private String deptName1;
    	private String deptId2;
    	private String deptName2;
    	private String deptId3;
    	private String deptName3;
    	private String deptId4;
    	private String deptName4;
    	private String deptId5;
    	private String deptName5;
    	private String deptId6;
    	private String deptName6;
    
    	public String getDeviceId() {
    		return deviceId;
    	}
    
    	public void setDeviceId(String deviceId) {
    		this.deviceId = deviceId;
    	}
    
       ......这里省略后面的get,set方法
    
    }
    复制代码

2.业务处理

这里和大家的写法可能有所差别，这里只贴出楼主实际的业务逻辑，供大家参考，只要大家理解这个思路就好了。

//查询应工作时间
     StringBuffer wql = new StringBuffer();
                wql.append(" select o.termBrand,sum(o.workTime) as workTime ");
                wql.append(" from ViewDeviceForWorkTime o,ViewDeviceDeptInfoForOpenRate v ");
                wql.append(" where o.termId = v.termId ");
                //这里是设置查询的参数，省略
                wql.append(paramsSql);
                wql.append(" group by o.termBrand ");
    复制代码

// 设置查询的参数
      Query queryWorkTime = createQuery(wql.toString());
                for (int i = 0; i < queryObj.length; i++) {
                    if (!"".equals(queryObj[i])) {
                        queryWorkTime.setParameter(i, queryObj[i]);   
                    }
                }
     Object[] list = queryWorkTime .list().toArray();
    复制代码

## 小结 ##

这里我们就很好的解决了hql的这类子查询问题，总的来说就是hql不直接支持类似from(select ......)这类单独成一个虚拟表的子查询，所以我们就把这个子查询查询出来的虚拟表给它建立一个虚拟视图的实体映射类，而且不会影响数据库的真实操作，再让它随着数据库对应的表同步刷新即可。

## 参考博客 ##

[Hibernate中子查询（subselect）的使用][Hibernate_subselect]

[hibernate使用from (select ……)子查询的方法][hibernate_from _select]

[Hibernate_subselect]: https://link.juejin.im?target=https%3A%2F%2Fblog.csdn.net%2Fqiuzhi__ke%2Farticle%2Fdetails%2F51952707
[hibernate_from _select]: https://link.juejin.im?target=https%3A%2F%2Fblog.csdn.net%2Ffableking%2Farticle%2Fdetails%2F3167081