解析JPA的視圖查詢問題
昨天晚上遇到一個需求,每天早上要生成一份報告給各個部門的Leader。實現(xiàn)方式基本上確定為HTML格式的電子郵件。但是數(shù)據(jù)方面犯了難。原因在于數(shù)據(jù)庫中存儲的數(shù)據(jù)是跨表的,而且還要做count統(tǒng)計,這樣得到的結(jié)果就不是原生的MySQL表,我用的又是JPA技術(shù)。我們知道,使用JPA第一步就是映射實體,每一張表就至少對應(yīng)一個實體(力求嚴謹,因為聯(lián)合主鍵時一張表會對應(yīng)兩個對象)。可是對于靈活的查詢尤其是連接查詢,并不存在一個真正的表與其對應(yīng),怎么樣才能解決呢?來,我們來舉個“栗子”
假設(shè)我們有兩張表,一張學院表,一張學生表。學院表里存著學院ID和學院名稱,學生表里存著學生的基本信息,包括學號、學院ID和學生姓名(其它較復(fù)雜的屬性我們不看了),正如下面的建表語句所示:
-- ----------------------------
-- Table structure for `depts`
-- ----------------------------
DROP TABLE IF EXISTS `depts`;
CREATE TABLE `depts` (
`deptId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '學院ID',
`deptName` varchar(50) NOT NULL COMMENT '學院名稱',
PRIMARY KEY (`deptId`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of depts
-- ----------------------------
INSERT INTO `depts` VALUES ('1', '哲學院');
INSERT INTO `depts` VALUES ('2', '經(jīng)濟學院');
INSERT INTO `depts` VALUES ('3', '法學院');
INSERT INTO `depts` VALUES ('4', '教育學院');
INSERT INTO `depts` VALUES ('5', '文學院');
INSERT INTO `depts` VALUES ('6', '歷史學院');
INSERT INTO `depts` VALUES ('7', '理學院');
INSERT INTO `depts` VALUES ('8', '工學院');
INSERT INTO `depts` VALUES ('9', '農(nóng)學院');
INSERT INTO `depts` VALUES ('10', '醫(yī)學院');
INSERT INTO `depts` VALUES ('11', '軍事學院');
INSERT INTO `depts` VALUES ('12', '管理學院');
INSERT INTO `depts` VALUES ('13', '藝術(shù)學院');
再建立一個學生表,再隨便往里面插入點數(shù)據(jù):
-- ----------------------------
-- Table structure for `students`
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`stuNo` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '學號 從1000開始',
`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 AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('1000', '13', '鳥叔');
INSERT INTO `students` VALUES ('1001', '7', '喬布斯');
INSERT INTO `students` VALUES ('1002', '3', '阿湯哥');
INSERT INTO `students` VALUES ('1003', '3', '施瓦辛格');
INSERT INTO `students` VALUES ('1004', '2', '貝克漢姆');
INSERT INTO `students` VALUES ('1005', '3', '讓雷諾');
現(xiàn)在我們想統(tǒng)計一下各個學院都有多少學生。這個題目在我們學習SQL的時候再簡單不過了。兩種實現(xiàn)方法:
使用Group By和不使用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;
使用Group By之后,凡是沒有對應(yīng)學生記錄的學院都沒有顯示出來(我不明白為什么。。。如果有人知道的話麻煩告訴我好嗎?)
+--------+--------------+------------+
| deptId | deptName | totalCount |
+--------+--------------+------------+
| 2 | 經(jīng)濟學院 | 1 |
| 3 | 法學院 | 3 |
| 7 | 理學院 | 1 |
| 13 | 藝術(shù)學院 | 1 |
+--------+--------------+------------+
再來一個不使用Group By的查詢:
SELECT a.deptId, a.deptName, (SELECT count(*) FROM students b where b.deptId=a.deptId) as 'totalCount' FROM depts a;
這次就完全顯示出來了:
+--------+--------------+------------+
| deptId | deptName | totalCount |
+--------+--------------+------------+
| 1 | 哲學院 | 0 |
| 2 | 經(jīng)濟學院 | 1 |
| 3 | 法學院 | 3 |
| 4 | 教育學院 | 0 |
| 5 | 文學院 | 0 |
| 6 | 歷史學院 | 0 |
| 7 | 理學院 | 1 |
| 8 | 工學院 | 0 |
| 9 | 農(nóng)學院 | 0 |
| 10 | 醫(yī)學院 | 0 |
| 11 | 軍事學院 | 0 |
| 12 | 管理學院 | 0 |
| 13 | 藝術(shù)學院 | 1 |
+--------+--------------+------------+
至此,我們的SQL寫通了。但是怎么才能使用JPA來查詢出一樣的視圖呢?
我們按照往常編碼那樣,從一個主要的實體操作服務(wù)中暴露出EntityManager來:
package net.csdn.blog.chaijunkun.dao;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.stereotype.Service;
@Service
public class ObjectDaoServiceImpl implements ObjectDaoService {
@PersistenceContext
private EntityManager entityManager;
@Override
public EntityManager getEntityManager(){
return this.entityManager;
}
}
這樣做的好處就是所有的數(shù)據(jù)操作都來源于同一個實體管理器。將來若部署發(fā)生變化,只改這一處注入就可以了。
然后我們還需要和以前一樣構(gòu)造兩個表的實體類:
學院表的實體類:
package net.csdn.blog.chaijunkun.pojo;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="depts")
public class Depts implements Serializable {
/**
*
*/
private static final long serialVersionUID = 3602227759878736655L;
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
@Column(name= "deptId")
private Integer deptId;
@Column(name= "deptName", length= 50, nullable= false)
private String deptName;
//getters and setters...
}
學生表的實體類:
package net.csdn.blog.chaijunkun.pojo;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
@Entity
@Table(name= "students")
public class Students implements Serializable {
/**
*
*/
private static final long serialVersionUID = -5942212163629824609L;
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
@Column(name= "stuNo")
private Long stuNo;
@ManyToOne
@JoinColumn(name= "deptId", nullable= false)<SPAN style="WHITE-SPACE: pre"> </SPAN>
private Depts depts;
@Column(name= "stuName", length= 50, nullable= false)
private String stuName;
//getters and setters...
}
兩個實體類都構(gòu)造好了,我們接下來還要弄一個視圖類,屬性的類型完全由你想要的結(jié)構(gòu)來構(gòu)造。例如這個例子中我們要學院編號,學院名稱和總?cè)藬?shù)。那么我們就這么定義:
package net.csdn.blog.chaijunkun.pojo;
import java.io.Serializable;
public class Report implements Serializable {
/**
*
*/
private static final long serialVersionUID = 4497500574990765498L;
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...
}
可以說,視圖對象的定義比實體定義還要簡單,不需要注解,不需要映射(以上代碼為了減少代碼量均省去了各屬性的get和set方法,請自行添加)。但是唯一不同的是我們需要額外構(gòu)造一個帶有字段初始化的構(gòu)造函數(shù)。并且還不能覆蓋默認的無參構(gòu)造函數(shù)。然后我們就開始進入真正的查詢了(作為視圖來講,SQL規(guī)范中是不允許修改數(shù)據(jù)的。因此,視圖僅有SELECT特性。這也是為什么很多人使用JPA想通過實體映射數(shù)據(jù)庫內(nèi)建視圖的方式進行查詢,卻始終映射不成功的癥結(jié)所在。)
package net.csdn.blog.chaijunkun.dao;
import java.util.List;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import org.springframework.stereotype.Service;
import net.csdn.blog.chaijunkun.pojo.Depts;
import net.csdn.blog.chaijunkun.pojo.Report;
import net.csdn.blog.chaijunkun.pojo.Students;
@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中留出參數(shù)位置來(?1 ?2 ?3....),然后在這設(shè)置
//reportTypedQuery.setParameter(1, params);
List<Report> reports= reportTypedQuery.getResultList();
return reports;
}
}
在上面的代碼中我們構(gòu)造了JPQL中的視圖查詢語句。最重要的就是要在最初的select后面new出新的對象。然后把我們查詢到的結(jié)果通過視圖對象的構(gòu)造函數(shù)灌入各個屬性。由統(tǒng)計生成的字段最好用as重命名結(jié)果以保持和視圖對象屬性名稱相同。這樣,我們就得到了視圖數(shù)據(jù)。接下來就去嘗試遍歷這個List吧,操作非常方便。
另外,向大家推薦一本書——Apress出版社出版的《Pro JPA 2 Mastering the Java trade Persistence API》,這本書詳細介紹了JPA的相關(guān)技術(shù),非常實用。
相關(guān)文章
java數(shù)據(jù)庫操作類演示實例分享(java連接數(shù)據(jù)庫)
java數(shù)據(jù)庫操作類演示實例分享,大家參考使用吧2013-12-12Java結(jié)構(gòu)型設(shè)計模式之享元模式示例詳解
享元模式(FlyWeight?Pattern),也叫蠅量模式,運用共享技術(shù),有效的支持大量細粒度的對象,享元模式就是池技術(shù)的重要實現(xiàn)方式。本文將通過示例詳細講解享元模式,感興趣的可以了解一下2022-09-09如何使用Spring Boot實現(xiàn)自定義Spring Boot插件
在本文中,我們介紹了如何使用 Spring Boot 實現(xiàn)自定義插件,使用自定義插件可以幫助我們快速地添加一些額外的功能,提高系統(tǒng)的可擴展性和可維護性,感興趣的朋友跟隨小編一起看看吧2023-06-06利用Java的Struts框架實現(xiàn)電子郵件發(fā)送功能
這篇文章主要介紹了利用Java的Struts框架實現(xiàn)電子郵件發(fā)送功能,Struts框架是Java的SSH三大web開發(fā)框架之一,需要的朋友可以參考下2015-12-12Java并發(fā)編程之Condition源碼分析(推薦)
這篇文章主要介紹了Java并發(fā)編程之Condition源碼分析,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2019-03-03SpringFox實現(xiàn)自動生成RESTful?API文檔
在開發(fā)?RESTful?API?時,編寫?API?文檔是一個重要的任務(wù),這篇文章為大家介紹了如何使用?SpringFox?自動生成?RESTful?API?文檔,并提供示例代碼,需要的可以參考一下2023-06-06