Problem analysis

A complex subquery such as from (select……) was involved in the maintenance of a previous project. Form HQL statements are not supported. To put it simply, you need to query a new virtual table through a subquery and associate it with other tables to obtain the final data required by services. The original SQL statement is as follows:

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;
Copy the code

I did a lot of research on the Internet and found that there were some help posts describing similar problems, but no corresponding solutions. Behind the building to the next or simplify the SQL statement and then code processing, the efficiency is low, the most stupid method), or create a new view in the database, but this kind of treatment method is not perfect, is this a business USES, will increase the cost of database, and if there are a lot of similar business, that is not built a lot of views, Nor is it sustainable. There must be a perfect solution to this problem. Finally, I found a perfect solution by referring to a large number of blogs and materials, that is, creating a virtual view method.

Specific Solutions

To put it simply, the virtual table obtained from the SELECT sub-query is mapped to a virtual view by an entity class, and then associated query operations are performed. A @subselect annotation, Subselect (optional), is used here: it maps an immutable and read-only entity into a database subquery. This is useful when you want to replace a base table with a view, but it is best not to do so. There is no difference between a view and a table for a Hibernate mapping because they are both transparent at the data layer (note: some databases do not support view attributes, especially when updated). Sometimes you want to use a view, but can’t create it in a database (for example, in a legacy schema). In this way, you can map an immutable and read-only entity to a given SQL subquery expression: The table used by this entity is defined as Synchronize to ensure that auto-flush is performed correctly and that queries that rely on the original entity do not return outdated data. Subselect is visible in both the attribute element and a nested mapping element.

The core code

Well, nonsense not to say, directly on the core code, for everyone’s reference and reference.

  1. Entity class

Note that although the queried view does not have an ID, the primary key must be added here. Otherwise, HQL cannot be mapped properly. The @SubSelect annotation here is to query the table data result of the database and map it to an entity class; Synchronize specifies the table used by this entity to Synchronize, ensuring that auto-flush is performed correctly.

@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 "** * If a subquery involves two tables, write * @synchronize ({"test_item"."test_bid" })
 */
@Synchronize({"REP_HARDWARE_FAULT_RATE"}) public class ViewDeviceForWorkTime {/** * primary key Id */ @id@generatedValue (Generator ="system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "uuid") private String id; /** * The device Id can be Column or not, and the camel camel mapping method is configured in the background */ @column (name ="TERM_ID") private String termId; /** * private String reportDate; /** * private String workTime; /** * private String termBrand; /** * private String modelName; public StringgetId() {
        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; }}Copy the code

Map the table view_DEVICe_DEPt_info in the database.

@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; }... I'm going to omit the get,setMethods}Copy the code

2. Service processing

Here and everyone’s writing may be different, here only posted the actual business logic of the building Lord, for everyone’s reference, as long as we understand this idea.

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 "); // Set query parameters, omit wqL.append (paramsSql); wql.append(" group by o.termBrand ");
Copy the code
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();
Copy the code

summary

HQL does not support subqueries such as from(select……). This kind of subquery separately into a virtual table, so we put this subquery query out of the virtual table to it to establish a virtual view of the entity mapping class, and will not affect the real operation of the database, and then let it with the database corresponding table synchronous refresh.

Refer to the blog

Use of Hibernate subselect

Hibernate uses from (select…) Subquery methods