解析JPA的视图查询问题_java

昨天晚上遇到一个需求，每天早上要生成一份报告给各个部门的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;

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

}

两个实体类都构造好了，我们接下来还要弄一个视图类，属性的类型完全由你想要的结构来构造。例如这个例子中我们要学院编号，学院名称和总人数。那么我们就这么定义：

复制代码 代码如下:

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的相关技术，非常实用。

时间： 2024-11-01 19:29:04

解析JPA的视图查询问题_java的相关文章

解析JPA的视图查询问题

        这篇文章主要是对JPA的视图查询问题进行了详细的分析介绍,需要的朋友可以过来参考下,希望对大家有所帮助 昨天晚上遇到一个需求,每天早上要生成一份报告给各个部门的Leader.实现方式基本上确定为HTML格式的电子邮件.但是数据方面犯了难.原因在于数据库中存储的数据是跨表的,而且还要做count统计,这样得到的结果就不是原生的MySQL表,我用的又是JPA技术.我们知道,使用JPA第一步就是映射实体,每一张表就至少对应一个实体(力求严谨,因为联合主键时一张表会对应两个对象).可是对

Oracle隐式转换会影响物化视图查询重写

今天有人问我一个物化视图查询重写的问题,最后发现问题其实和物化视图的功能没有多大的关系,而是隐式转换导致的问题. 还是通过例子来说明这个问题: SQL> create table t ( 2  id number, 3  time date, 4  other varchar2(4000)) 5  partition by range (time) 6  (partition p1 values less than (to_date('2008-1-1', 'yyyy-mm-dd')), 7  

SQL点滴17—使用数据库引擎存储过程，系统视图查询，DBA，BI开发人员必备基础知识

原文:SQL点滴17-使用数据库引擎存储过程,系统视图查询,DBA,BI开发人员必备基础知识 在开发过程中会遇到需要弄清楚这个数据库什么时候建的,这个数据库中有多少表,这个存储过程长的什么样子等等信息,今天把自己工作过程中经常用到的一些数据库引擎存储过程,系统视图等等总结一下以备不时之用.下面的知识多是自己总结,有一些参考了MSDN. sp_help 有时候想尽快查出数据库对象的相关信息,这个存储过程就很有用了.使用它可以查询出整个数据库中所有对象的相关信息.直接运行sp_help结果如下图1,

oracle-Oracle 关于视图查询问题

问题描述 Oracle 关于视图查询问题 CREATE OR REPLACE VIEW V_HZY_SALARYASSELECT D.DEPTNO DNOD.DNAME DN MAX(E.SAL)MAX_SAL MIN(E.SAL)MIN_SAL AVG(E.SAL)AVG_SAL SUM(E.SAL)SUM_SALFROM EMP_HZY E DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNOD.DNAME SELECT E.ENAMEE.SALE

group by-Spring JPA CriteriaQuery分组查询

问题描述 Spring JPA CriteriaQuery分组查询 Spring JPA CriteriaQuery 设置select 查询的字段丝毫没起到作用,很郁闷.网上也有很多类似的问题,都没有解决方法.有没有大神用JPA做过分组查询的?求教... 解决方案 CriteriaQuery q = cb.createQuery(Country.class); Root c = q.from(Country.class); q.select(c.get(""currency"

spring data jpa 多条件查询判读问题

问题描述 spring data jpa 多条件查询判读问题 在使用Spring Data JPA时发现的问题在实际应用中,我们通常会在页面上进行组合查询,例如用户这个对象 页面上会列出用户姓名.年龄.学历.性别.地址.电话等等的一系列查询条件 最近在学习Spring Data JPA,不知这个怎么实现这种组合条件的查询啊 只能手动判断后写SQL吗 解决方案 Spring jpa data多条件分页查询spring data jpa 分页查询 解决方案二: 不用手动写SQL的,SpringJPA

ThinkPHP视图查询详解_php实例

ThinkPHP提供的视图查询应用功能十分强大,用户利用视图查询功能可以将多个数据表的字段内容按需要进行指定和筛选,组织成一个基于这些数据表的视图模型,然后就可以通过该模型直接进行多表联合查询,非常方便和简单. 例如在项目中,我们定义有三个表: user          用户基础表, user_info   用户详细信息表, dept          部门分类表 现在我们需要获取某个用户信息, 该信息要包括用户的帐号名称和相关资料与及所在部门的名称, 这时候我们可以利用视图查询进行处理. 下

JPA where in查询的问题

问题描述 我的业务场景是这样的,我有一个流程节点,有对应的状态节点1,状态1,状态2,状态3节点2,状态4...所以,我们的ql="selectefromEntityewheree.statusin(:status)";这个in里面的内容,需要动态的决定说是否进行查找,可能是1个参数,可能是3个参数.我在网络上查询,发现jpa可以对集合进行操作引用 "9.JPA的集合查询:我们知道在SQL语句中,查找某元素是否在某个集合中时常用类似:in(a,b,c)的查询语句,在JDBC中

mysql 视图查询 与删除

本教程要来讲一下关于mysql教程 视图查询 与删除哦,查询视图用show 视图名称,删除mysql视图用drop也很简单,但是删除视图要有drop 权限才行.下面我们先看查看视图 查看视图语法 show tables; 或show tables status 后者可以不但可以查到表里的视图名称,同时可以查看其它相关信息,如是实例 ->show tables; st stff staroo 26 row in set( 0.00 sec); ->show tables status like