本文詳細分析 JPA 中如何進行視圖查詢,需要的朋友可以參考,希望能對大家有所幫助。
有一個需求:每天早上生成一份報告發給各部門的 Leader,實現方式確定為 HTML 格式的電子郵件。但數據方面犯了難——數據庫中存儲的數據是跨表的,還要做 count 統計,得到的結果並非原生的 MySQL 表,而項目用的又是 JPA。我們知道,使用 JPA 的第一步就是映射實體,每張表至少對應一個實體;可是對於靈活的連接查詢,並不存在一個真正的表與之對應,該怎麼辦呢?
假設有兩張表:學院表 depts 存放學院 ID 和名稱,學生表 students 存放學號、學院 ID 和姓名。
-- 學院表
CREATE TABLE `depts` (
`deptId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '學院ID',
`deptName` varchar(50) NOT NULL COMMENT '學院名稱',
PRIMARY KEY (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 學生表
CREATE TABLE `students` (
`stuNo` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '學號',
`deptId` int(10) unsigned NOT NULL COMMENT '學院ID',
`stuName` varchar(50) NOT NULL COMMENT '學生姓名',
PRIMARY KEY (`stuNo`),
KEY `FK_DEPTID` (`deptId`),
CONSTRAINT `FK_DEPTID` FOREIGN KEY (`deptId`) REFERENCES `depts` (`deptId`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
統計各個學院有多少學生,可以用兩種方式。使用 Group By 時,沒有對應學生記錄的學院不會顯示出來:
SELECT b.deptId, b.deptName, count(*) as 'totalCount'
FROM students a LEFT JOIN depts b ON a.deptId = b.deptId
GROUP BY b.deptId ORDER BY b.deptId;
而使用子查詢則能完整顯示所有學院(人數為 0 的也會列出):
SELECT a.deptId, a.deptName,
(SELECT count(*) FROM students b WHERE b.deptId = a.deptId) as 'totalCount'
FROM depts a;
首先從一個主要的實體操作服務中暴露出 EntityManager:
@Service
public class ObjectDaoServiceImpl implements ObjectDaoService {
@PersistenceContext
private EntityManager entityManager;
@Override
public EntityManager getEntityManager() {
return this.entityManager;
}
}
這樣做的好處是所有數據操作都來源於同一個實體管理器,將來部署發生變化時只改這一處注入即可。接著構造兩個表的實體類 Depts 與 Students(此處從略),然後再定義一個視圖類 Report,其屬性類型完全由需要的結構決定:
public class Report implements Serializable {
private Integer deptId;
private String deptName;
private Integer totalCount;
public Report() {};
public Report(Integer deptId, String deptName, Integer totalCount) {
this.deptId = deptId;
this.deptName = deptName;
this.totalCount = totalCount;
}
// getters and setters...
}
可以說,視圖對象的定義比實體還簡單,不需要注解、不需要映射,但唯一不同的是需要額外構造一個帶字段初始化的構造函數,並且不能覆蓋默認的無參構造函數。(作為視圖,SQL 規範中不允許修改數據,因此視圖僅有 SELECT 特性,這也是很多人試圖用實體映射數據庫內建視圖卻始終失敗的癥結所在。)
最後進入真正的查詢:
@Service
public class ReportServiceImpl implements ReportService {
@Resource
private ObjectDaoService objectDaoService;
@Override
public List<Report> getReport() {
String jpql = String.format(
"select new %3$s(a.deptId, a.deptName, (select count(*) from %2$s b where b.deptId = a.deptId) as totalCount) from %1$s a",
Depts.class.getName(),
Students.class.getName(),
Report.class.getName());
EntityManager entityManager = objectDaoService.getEntityManager();
TypedQuery<Report> reportTypedQuery = entityManager.createQuery(jpql, Report.class);
// 若有詳細查詢條件,可在 jpql 中留出參數位置(如 ?1 ?2 ?3),再在此設置
// reportTypedQuery.setParameter(1, params);
List<Report> reports = reportTypedQuery.getResultList();
return reports;
}
}
關鍵在於:在最初的 select 後面用 new 構造出新對象,並把查詢結果通過視圖對象的構造函數灌入各個屬性。由統計生成的字段最好用 as 重命名,以保持與視圖對象屬性名一致。這樣就得到了視圖數據,後續遍歷這個 List 十分方便。
另外推薦一本書——Apress 出版社的《Pro JPA 2 Mastering the Java Persistence API》,它詳細介紹了 JPA 相關技術,非常實用。