Resolve JPA's view query problems

I met a demand last night. Every morning, a report should be generated to the leaders of each department. The implementation is basically defined as an HTML E-mail. But the Numbers are tricky. The reason is that the data stored in the database is cross-table, and the count count is also required, so the result is not a native MySQL table, but a JPA technique. As we know, the first step in using JPA is to map entities, with at least one entity for each table (be careful, because when you combine primary keys, a table corresponds to two objects). However, for flexible queries, especially join queries, there is no real table corresponding to it, how to solve it? Here, let's say "chestnut."

Let's say we have two tables, one for colleges and one for students. The table of colleges contains the college ID and the college name, and the table of students contains the basic information of students, including the student number, the college ID and the student name (we will not look at other complex attributes), as shown in the following predicative sentence:

-- ----------------------------
-- Table structure for `depts`
-- ----------------------------
DROP TABLE IF EXISTS `depts`;
CREATE TABLE `depts` (
  `deptId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ' college ID',
  `deptName` varchar(50) NOT NULL COMMENT ' Name of college ',
  PRIMARY KEY (`deptId`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of depts
-- ----------------------------
INSERT INTO `depts` VALUES ('1', ' Philosophy department ');
INSERT INTO `depts` VALUES ('2', ' School of economics ');
INSERT INTO `depts` VALUES ('3', ' Law school, ');
INSERT INTO `depts` VALUES ('4', ' Institute of education ');
INSERT INTO `depts` VALUES ('5', ' The faculty of arts ');
INSERT INTO `depts` VALUES ('6', ' Institute of history ');
INSERT INTO `depts` VALUES ('7', ' Faculty of science ');
INSERT INTO `depts` VALUES ('8', ' Institute of technology ');
INSERT INTO `depts` VALUES ('9', ' Agronomy courtyard ');
INSERT INTO `depts` VALUES ('10', ' School of medicine, ');
INSERT INTO `depts` VALUES ('11', ' Military academy ');
INSERT INTO `depts` VALUES ('12', ' School of management, ');
INSERT INTO `depts` VALUES ('13', ' College of art ');

Create a student table and insert points into it:

-- ----------------------------
-- Table structure for `students`
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `stuNo` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ' Student id   from 1000 start ',
  `deptId` int(10) unsigned NOT NULL COMMENT ' college ID',
  `stuName` varchar(50) NOT NULL COMMENT ' The student's name ',
  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', ' Bird uncle ');
INSERT INTO `students` VALUES ('1001', '7', ' Steve jobs ');
INSERT INTO `students` VALUES ('1002', '3', ' Tom ');
INSERT INTO `students` VALUES ('1003', '3', ' Arnold Schwarzenegger, ');
INSERT INTO `students` VALUES ('1004', '2', ' David Beckham ');
INSERT INTO `students` VALUES ('1005', '3', ' The Renault ');

Now we want to count the number of students in each college. This topic is very simple when we study SQL. Two implementation methods:

Using Group By and not using 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;

After using Group By, all the colleges that do not have corresponding student records are not shown (I don't understand why... Could you please let me know if anyone knows?

+--------+--------------+------------+
| deptId | deptName     | totalCount |
+--------+--------------+------------+
|      2 |  School of economics      |          1 |
|      3 |  Law school,        |          3 |
|      7 |  Faculty of science        |          1 |
|     13 |  College of art      |          1 |
+--------+--------------+------------+

Another query that does not use Group By:

SELECT a.deptId, a.deptName, (SELECT count(*) FROM students b where b.deptId=a.deptId) as 'totalCount' FROM depts a;

This time it was all on display:

+--------+--------------+------------+
| deptId | deptName     | totalCount |
+--------+--------------+------------+
|      1 |  Philosophy department        |          0 |
|      2 |  School of economics      |          1 |
|      3 |  Law school,        |          3 |
|      4 |  Institute of education      |          0 |
|      5 |  The faculty of arts        |          0 |
|      6 |  Institute of history      |          0 |
|      7 |  Faculty of science        |          1 |
|      8 |  Institute of technology        |          0 |
|      9 |  Agronomy courtyard        |          0 |
|     10 |  School of medicine,        |          0 |
|     11 |  Military academy      |          0 |
|     12 |  School of management,      |          0 |
|     13 |  College of art      |          1 |
+--------+--------------+------------+

At this point, our SQL writes through. But how do you use JPA to query the same view?

We expose the EntityManager from a major entity operation service as we normally code it:

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;
 }
}

The advantage of this is that all data operations originate from the same entity manager. If the deployment changes in the future, just change the injection at this point.

Then we need to construct the two table entity classes as before:

Entity class of college table:

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

Entity class of student table:

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

}

Now that the two entity classes have been constructed, we're going to have a view class, and the type of the properties is going to be constructed exactly by the structure you want. For example, in this case we want the college number, the name of the college and the total number of students. So let's define it this way:

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

}

It is safe to say that the definition of view object is even simpler than entity definition, no annotations are required, and no mapping is required. (the above code omits the get and set methods of each property in order to reduce the amount of code, please add them by yourself.) The only difference is that we need to construct an extra constructor with field initialization. And you can't override the default argument free constructor. Then we move on to the actual query (which, as a view, is not allowed to modify data in the SQL specification). Therefore, the view has only the SELECT feature. This is why many people who use JPA want to query in the way that an entity-mapped database has a built-in view, but the mapping is never successful.

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();
  //Create typed queries
  TypedQuery<Report> reportTypedQuery= entityManager.createQuery(jpql, Report.class);
  //In addition, those with detailed query conditions set aside parameter positions in JPQL to (? 1? 2? 3)... And then set it here
  //reportTypedQuery.setParameter(1, params);
  List<Report> reports= reportTypedQuery.getResultList();
  return reports;
 }
}

In the code above we constructed the view query statement in JPQL. The most important thing is to create a new object after the initial select. We then feed the results of our query into the properties through the constructor of the view object. Fields generated by statistics are best renamed with as to keep the same name as the view object property. So, we get the view data. So let's try to go through the List, which is pretty easy.

In addition, I recommend a book, Pro JPA 2 Mastering the Java trade Persistence API, published by Apress, which is a practical and detailed introduction to JPA related technologies.