問題分析
樓主之前在維護(hù)公司之前一個(gè)項(xiàng)目時(shí)遇到一個(gè)坑，就是涉及到一個(gè)復(fù)雜子查詢形如from（select......）形式的hql語句不支持，簡(jiǎn)單說就是先要通過子查詢查詢出來一張新的虛擬表，然后和其他表做關(guān)聯(lián)才能得到業(yè)務(wù)所需要的最終數(shù)據(jù)。
原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;
我在網(wǎng)上查了大量資料，發(fā)現(xiàn)有一些求助的帖子中有類似的問題描述，但是都沒有相應(yīng)的解決方案。后面樓主想了下要不就簡(jiǎn)化SQL語句然后再代碼中處理（這種效率很低，最笨的方法），或者在數(shù)據(jù)庫中新建一個(gè)視圖，但這種處理方法也不是十分完美，就這一塊業(yè)務(wù)用到了，會(huì)增加數(shù)據(jù)庫的開銷，而且假如說有很多類似的業(yè)務(wù)，那不是得建很多張視圖，這種辦法可持續(xù)性也不好。后面樓主還是沒放棄，就覺得應(yīng)該有其他人也遇到過類似的問題，肯定有比較完美的解決方案~終于功夫不有心人，樓主參考大量的博客和資料終于找到了一種比較完美的解決方案，即建立虛擬視圖法。
具體解決方案
簡(jiǎn)單說就是將select子查詢到的虛擬表建立一個(gè)實(shí)體類映射成一個(gè)虛擬視圖，然后再進(jìn)行關(guān)聯(lián)查詢操作。這里要用到一個(gè)@Subselect注解，即
subselect (可選): 它將一個(gè)不可變（immutable）并且只讀的實(shí)體映射到一個(gè)數(shù)據(jù)庫的子查詢中。當(dāng)你想用視圖代替一張基本表的時(shí)候，這是有用的，但最好不要這樣做。
對(duì)Hibernate映射來說視圖和表是沒有區(qū)別的，這是因?yàn)樗鼈冊(cè)跀?shù)據(jù)層都是透明的（ 注意：一些數(shù)據(jù)庫不支持視圖屬性，特別是更新的時(shí)候）。有時(shí)你想使用視圖，但卻不能在數(shù)據(jù)庫中創(chuàng)建它（例如：在遺留的schema中）。這樣的話，你可以映射一個(gè)不可變的（immutable）并且是只讀的實(shí)體到一個(gè)給定的SQL子查詢表達(dá)式：定義這個(gè)實(shí)體用到的表為同步（synchronize），確保自動(dòng)刷新（auto-flush）正確執(zhí)行， 并且依賴原實(shí)體的查詢不會(huì)返回過期數(shù)據(jù)。subselect在屬性元素和一個(gè)嵌套映射元素中都可見。
核心代碼
好啦，廢話不多說，直接上核心代碼，以供大家參考和借鑒。
- 實(shí)體類
注意，雖然我們查詢出來的視圖沒有id，但是這里必須加主鍵，否則hql無法正常映射，應(yīng)該是必須遵從的規(guī)范。
這里的@Subselect注解是查詢數(shù)據(jù)庫的表數(shù)據(jù)結(jié)果，將其映射為一個(gè)實(shí)體類；@Synchronize是定義這個(gè)實(shí)體用到的表為同步（synchronize），確保自動(dòng)刷新（auto-flush）正確執(zhí)行。
@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個(gè)表，則這樣寫
*@Synchronize( { "test_item", "test_bid" })
*/
@Synchronize({"REP_HARDWARE_FAULT_RATE"})
public class ViewDeviceForWorkTime {
/**
* 主鍵Id
* 這里必須寫，不寫會(huì)報(bào)錯(cuò)，hql映射必須要加
*/
@Id
@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "uuid")
private String id;
/**
* 設(shè)備Id
* 可以加Column，也可以不加，后臺(tái)配置了駝峰映射法
*/
@Column(name = "TERM_ID")
private String termId;
/**
* 記錄日期
*/
private String reportDate;
/**
* 應(yīng)工作時(shí)間
*/
private String workTime;
/**
* 設(shè)備品牌
*/
private String termBrand;
/**
* 設(shè)備型號(hào)
*/
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;
}
}
映射數(shù)據(jù)庫中的表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.業(yè)務(wù)處理
這里和大家的寫法可能有所差別，這里只貼出樓主實(shí)際的業(yè)務(wù)邏輯，供大家參考，只要大家理解這個(gè)思路就好了。
//查詢應(yīng)工作時(shí)間
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 ");
//這里是設(shè)置查詢的參數(shù)，省略
wql.append(paramsSql);
wql.append(" group by o.termBrand ");
// 設(shè)置查詢的參數(shù)
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();
小結(jié)
這里我們就很好的解決了hql的這類子查詢問題，總的來說就是hql不直接支持類似from(select ......)這類單獨(dú)成一個(gè)虛擬表的子查詢，所以我們就把這個(gè)子查詢查詢出來的虛擬表給它建立一個(gè)虛擬視圖的實(shí)體映射類，而且不會(huì)影響數(shù)據(jù)庫的真實(shí)操作，再讓它隨著數(shù)據(jù)庫對(duì)應(yīng)的表同步刷新即可。
