Parsing JPA's view query problem _java

Last night I met a demand, and every morning I had to produce a report to the leader of every department. The implementation is basically determined to be an HTML-formatted email. But the data are hard to make. The reason is that the data stored in the database is a cross table, and also do count statistics, so that the result is not the original MySQL table, I used the JPA technology. We know that the first step to using JPA is to map entities, and each table corresponds to at least one entity (to be rigorous, because a table corresponds to two objects when the primary key is combined). But for the flexible query, especially the connection query, there is no real table and its corresponding, how to solve it? Come on, let's give you a "chestnut."

Let's say we have two tables, a college table and a student table. The college table contains the college ID and the college name, and the students ' basic information, including the student number, the college ID and the student's name (other more complex attributes we don't see), is as shown in the following building statement:

Copy Code code as follows:

-- ----------------------------
--Table structure for ' depts '
-- ----------------------------
DROP TABLE IF EXISTS ' depts ';
CREATE TABLE ' depts ' (
' DeptID ' int (one) unsigned not NULL auto_increment COMMENT ' College ID ',
' deptname ' varchar not NULL COMMENT ' College name ',
PRIMARY KEY (' DeptID ')
) Engine=innodb auto_increment=14 DEFAULT Charset=utf8;

-- ----------------------------
--Records of Depts
-- ----------------------------
INSERT into ' depts ' VALUES (' 1 ', ' philosophical Yuan ');
INSERT into ' depts ' VALUES (' 2 ', ' School of Economics ');
INSERT into ' depts ' VALUES (' 3 ', ' Law school ');
INSERT into ' depts ' VALUES (' 4 ', ' College of Education ');
INSERT into ' depts ' VALUES (' 5 ', ' College of Arts ');
INSERT into ' depts ' VALUES (' 6 ', ' History College ');
INSERT into ' depts ' VALUES (' 7 ', ' College of Science ');
INSERT into ' depts ' VALUES (' 8 ', ' Institute of Technology ');
INSERT into ' depts ' VALUES (' 9 ', ' Agricultural College ');
INSERT into ' depts ' VALUES (' 10 ', ' Medical school ');
INSERT into ' depts ' VALUES (' 11 ', ' Military Academy ');
INSERT into ' depts ' VALUES (' 12 ', ' Management College ');
INSERT into ' depts ' VALUES (' 13 ', ' Art Institute ');

Create a student table, and then randomly insert point data into it:

Copy Code code as follows:

-- ----------------------------
--Table structure for ' students '
-- ----------------------------
DROP TABLE IF EXISTS ' students ';
CREATE TABLE ' Students ' (
' Stuno ' bigint unsigned not NULL auto_increment COMMENT ' School number starts from 1000 ',
' DeptID ' int (a) unsigned not NULL COMMENT ' College ID ',
' stuname ' varchar not NULL COMMENT ' student 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 ', ' Uncle Bird ');
INSERT into ' students ' VALUES (' 1001 ', ' 7 ', ' Jobs ');
INSERT into ' students ' VALUES (' 1002 ', ' 3 ', ' Brother Tang ');
INSERT into ' students ' VALUES (' 1003 ', ' 3 ', ' Arnold Schwarzenegger ');
INSERT into ' students ' VALUES (' 1004 ', ' 2 ', ' Beckham ');
INSERT into ' students ' VALUES (' 1005 ', ' 3 ', ' let Renault ');

Now we want to count how many students there are in each college. This topic is simpler when we are learning SQL. Two methods of implementation:

Use GROUP BY and do not use GROUP by:

Copy Code code as follows:

SELECT B.deptid, B.deptname, COUNT (*) as ' totalcount ' from students A-left JOIN depts B in A.deptid=b.deptid GROUP by b.de Ptid ORDER by B.deptid;

After using group by, colleges that don't have a corresponding student record are not shown (I don't understand why ...). If anyone knows, please tell me, okay? ）

Copy Code code as follows:

+--------+--------------+------------+
| DeptID | Deptname | TotalCount |
+--------+--------------+------------+
| 2 |          School of Economics | 1 |
| 3 |          Law School | 3 |
| 7 |          Faculty of Science | 1 |
| 13 |          Art Institute | 1 |
+--------+--------------+------------+

One more query that does not use GROUP by:

Copy Code code as follows:

Select A.deptid, A.deptname, (SELECT COUNT (*) from students b where b.deptid=a.deptid) as ' TotalCount ' from depts A;

This time it was completely displayed:

Copy Code code as follows:

+--------+--------------+------------+
| DeptID | Deptname | TotalCount |
+--------+--------------+------------+
| 1 |          Philosophy Institute | 0 |
| 2 |          School of Economics | 1 |
| 3 |          Law School | 3 |
| 4 |          College of Education | 0 |
| 5 |          College of Arts | 0 |
| 6 |          History Institute | 0 |
| 7 |          Faculty of Science | 1 |
| 8 |          Institute of Technology | 0 |
| 9 |          Agricultural College | 0 |
| 10 |          Medical School | 0 |
| 11 |          Military Academy | 0 |
| 12 |          School of Management | 0 |
| 13 |          Art Institute | 1 |
+--------+--------------+------------+

So far, our SQL has been written through. But how can you use JPA to query for the same view?

As we always encode, we expose entitymanager from a major entity operations service:

Copy Code code as follows:

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 this one place to inject.

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

Entity classes of the College table:

Copy Code code as follows:

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=, nullable= false)
Private String Deptname;

Getters and setters ...
}

Entity classes for Student tables:

Copy Code code as follows:

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=, nullable= false)
Private String Stuname;

Getters and setters ...

}

Two entity classes are all constructed, and we're going to get a view class, and the type of the attribute is constructed entirely by the structure you want. For example In this example we have to institute number, college name and total number. So that's what we're defining:

Copy Code code as follows:

Package Net.csdn.blog.chaijunkun.pojo;

Import java.io.Serializable;

public class implements Serializable {

/**
*
*/
Private static final long serialversionuid = 4497500574990765498L;

Private Integer DeptID;

Private String Deptname;

Private Integer TotalCount;

Public () {};

Public (integer deptid, String deptname, integer totalcount) {
This.deptid = DeptID;
This.deptname = Deptname;
This.totalcount = TotalCount;
}

Getters and setters ...

}

It can be said that the definition of a view object is simpler than an entity definition, requires no annotations, and does not require a mapping ( The above code to reduce the amount of code to eliminate the various properties of the get and set method, please add）。 But the only difference is that we need to construct an extra constructor with field initialization. And you cannot overwrite the default parameterless constructor. And then we start going into the real query ( as a view, the data is not allowed to be modified in the SQL specification. Therefore, the view has only a select attribute. That's why many people use JPA to query through an entity-mapped database, but always map the crux of the problem. ）

Copy Code code as follows:

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) a" S TotalCount) from%1 $ A ",
Depts.class.getName (),
Students.class.getName (),
Report.class.getName ());

Entitymanager entitymanager= Objectdaoservice.getentitymanager ();
Create a type of query
Typedquery<report> reporttypedquery= entitymanager.createquery (JPQL, Report.class);
In addition to the detailed query conditions in the JPQL set aside the parameter position to (? 1? 2? 3 ...), and then in this setting
Reporttypedquery.setparameter (1, params);
List<report> reports= reporttypedquery.getresultlist ();
return reports;
}

}

In the above code we construct the view query statement in JPQL. The most important thing is to create a new object after the initial select. The results of our query are then poured into the properties by the constructor of the View object. Fields generated by statistics best use as to rename the result to maintain the same property name as the View object. In this way, we get the view data. The next step is to try to traverse the list, which is very convenient to operate.

In addition, we recommend a book--apress publishing "Pro JPA 2 Mastering the Java trade Persistence API", this book details the JPA related technology, very practical.