解析JPA的視圖查詢問題

2019-11-26 15:51:57

字體：大中小

來源：轉載

供稿：網友

昨天晚上遇到一個需求，每天早上要生成一份報告給各個部門的Leader。實現方式基本上確定為HTML格式的電子郵件。但是數據方面犯了難。原因在于數據庫中存儲的數據是跨表的，而且還要做count統計，這樣得到的結果就不是原生的MySQL表，我用的又是JPA技術。我們知道，使用JPA第一步就是映射實體，每一張表就至少對應一個實體（力求嚴謹，因為聯合主鍵時一張表會對應兩個對象）。可是對于靈活的查詢尤其是連接查詢，并不存在一個真正的表與其對應，怎么樣才能解決呢？來，我們來舉個“栗子”

假設我們有兩張表，一張學院表，一張學生表。學院表里存著學院ID和學院名稱，學生表里存著學生的基本信息，包括學號、學院ID和學生姓名（其它較復雜的屬性我們不看了），正如下面的建表語句所示：

復制代碼代碼如下:

-- ----------------------------
-- 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', '經濟學院');
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', '農學院');
INSERT INTO `depts` VALUES ('10', '醫學院');
INSERT INTO `depts` VALUES ('11', '軍事學院');
INSERT INTO `depts` VALUES ('12', '管理學院');
INSERT INTO `depts` VALUES ('13', '藝術學院');

再建立一個學生表，再隨便往里面插入點數據：

復制代碼代碼如下:

-- ----------------------------
-- 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', '讓雷諾');

現在我們想統計一下各個學院都有多少學生。這個題目在我們學習SQL的時候再簡單不過了。兩種實現方法：

使用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之后，凡是沒有對應學生記錄的學院都沒有顯示出來（我不明白為什么。。。如果有人知道的話麻煩告訴我好嗎？）

復制代碼代碼如下:

+--------+--------------+------------+
| deptId | deptName     | totalCount |
+--------+--------------+------------+
|      2 | 經濟學院     |          1 |
|      3 | 法學院       |          3 |
|      7 | 理學院       |          1 |
|     13 | 藝術學院     |          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 | 經濟學院     |          1 |
|      3 | 法學院       |          3 |
|      4 | 教育學院     |          0 |
|      5 | 文學院       |          0 |
|      6 | 歷史學院     |          0 |
|      7 | 理學院       |          1 |
|      8 | 工學院       |          0 |
|      9 | 農學院       |          0 |
|     10 | 醫學院       |          0 |
|     11 | 軍事學院     |          0 |
|     12 | 管理學院     |          0 |
|     13 | 藝術學院     |          1 |
+--------+--------------+------------+

至此，我們的SQL寫通了。但是怎么才能使用JPA來查詢出一樣的視圖呢？

我們按照往常編碼那樣，從一個主要的實體操作服務中暴露出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;
}

}

這樣做的好處就是所有的數據操作都來源于同一個實體管理器。將來若部署發生變化，只改這一處注入就可以了。

然后我們還需要和以前一樣構造兩個表的實體類：

學院表的實體類：

復制代碼代碼如下:

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;

@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...

}

兩個實體類都構造好了，我們接下來還要弄一個視圖類，屬性的類型完全由你想要的結構來構造。例如這個例子中我們要學院編號，學院名稱和總人數。那么我們就這么定義：

復制代碼代碼如下:

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方法，請自行添加）。但是唯一不同的是我們需要額外構造一個帶有字段初始化的構造函數。并且還不能覆蓋默認的無參構造函數。然后我們就開始進入真正的查詢了（作為視圖來講，SQL規范中是不允許修改數據的。因此，視圖僅有SELECT特性。這也是為什么很多人使用JPA想通過實體映射數據庫內建視圖的方式進行查詢，卻始終映射不成功的癥結所在。）

復制代碼代碼如下:

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中留出參數位置來(?1 ?2 ?3....)，然后在這設置
  //reportTypedQuery.setParameter(1, params);
  List<Report> reports= reportTypedQuery.getResultList();
  return reports;
}

}

在上面的代碼中我們構造了JPQL中的視圖查詢語句。最重要的就是要在最初的select后面new出新的對象。然后把我們查詢到的結果通過視圖對象的構造函數灌入各個屬性。由統計生成的字段最好用as重命名結果以保持和視圖對象屬性名稱相同。這樣，我們就得到了視圖數據。接下來就去嘗試遍歷這個List吧，操作非常方便。

另外，向大家推薦一本書――Apress出版社出版的《Pro JPA 2 Mastering the Java trade Persistence API》，這本書詳細介紹了JPA的相關技術，非常實用。

上一篇：如何讓Jackson JSON生成的數據包含的中文以unicode方式編碼

下一篇：日歷顯示讀出輸入的年月的java代碼