Resolve View queries in JPA

Resolve View queries in JPA

This article mainly analyzes and introduces the View query problem of JPA in detail. If you need a friend, please refer to it and hope to help you.

When I met a requirement last night, I had to generate a report to the leaders of various departments every morning. The implementation method is basically determined as HTML-format email. However, the data is difficult. The reason is that the data stored in the database is cross-table and count statistics are required. The result is not a native MySQL table, but a JPA technology. We know that the first step to using JPA is to map objects. Each table corresponds to at least one object (to be rigorous, because a table corresponds to two objects when the primary key is joined ). But for flexible queries, especially connection queries, there is no real table corresponding to them. How can this problem be solved? Come, let's give you a chestnut"

 

Suppose we have two tables, one is a school Table and the other is a student table. The school table contains the school ID and school name, and the student table contains the basic information of the student, including the student ID, school ID, and student name (we will not read other complex attributes ), as shown in the following table creation statement:

 

 

The Code is as follows:

------------------------------

-- Table structure for 'dept'

------------------------------

Drop table if exists 'dept ';

Create table 'dept '(

'Deptid' int (11) unsigned not null AUTO_INCREMENT COMMENT 'school id ',

'Deptname' varchar (50) not null comment 'school name ',

Primary key ('deptid ')

) ENGINE = InnoDB AUTO_INCREMENT = 14 default charset = utf8;

 

------------------------------

-- Records of depts

------------------------------

Insert into 'dept' VALUES ('1', 'School of philosophy ');

Insert into 'dept' VALUES ('2', 'School of economics ');

Insert into 'dept' VALUES ('3', 'law ');

Insert into 'dept' VALUES ('4', 'School of Education ');

Insert into 'depps' VALUES ('5', 'School of literature ');

Insert into 'depps' VALUES ('6', 'School of history ');

Insert into 'dept' VALUES ('7', 'Emy of science ');

Insert into 'dest' VALUES ('8', 'Emy of engineering ');

Insert into 'dept' VALUES ('9', 'agricultural College ');

Insert into 'dept' VALUES ('10', 'Medical school ');

Insert into 'dept' VALUES ('11', 'military Emy ');

Insert into 'dept' VALUES ('12', 'School of Management ');

Insert into 'dept' VALUES ('13', 'art school ');

 

 

Create a student table and insert some data to it:

The Code is as follows:

------------------------------

-- Table structure for 'students'

------------------------------

Drop table if exists 'students ';

Create table 'students '(

'Std' bigint (20) unsigned not null AUTO_INCREMENT comment' student ID starting from 1000 ',

'Deptid' int (10) unsigned not null comment 'school id ',

'Stdame' varchar (50) not null comment' student name ',

Primary key ('std '),

KEY 'fk _ deptid' ('demotid '),

CONSTRAINT 'fk _ DEPTID 'foreign key ('demotid') REFERENCES 'dept' ('demotid') ON UPDATE CASCADE

ENGINE = InnoDB AUTO_INCREMENT = 1006 default charset = utf8;

 

------------------------------

-- Records of students

------------------------------

Insert into 'students' VALUES ('20170101', '13', 'birds ');

Insert into 'students' VALUES ('20170101', '7', 'jobs ');

Insert into 'students' VALUES ('20170101', '3', 'atange ');

Insert into 'students' VALUES ('20170101', '3', 'barwws ');

Insert into 'students' VALUES ('201312', '2', 'beckham ');

Insert into 'students' VALUES ('20170101', '3', 'Let Renault ');

 

 

Now we want to count the number of students in each school. This is a simple question when we are learning SQL. Two implementation methods:

 

Use Group By and do not use Group:

 

 

The Code is as follows:

SELECT B. deptId, B. deptName, count (*) as 'totalcount' FROM students a left join depts B ON. deptId = B. deptId group by B. deptId order by B. deptId;

 

After Group By is used, none of the schools with no corresponding student records are displayed (I don't understand why... If anyone knows, can you tell me ?)

The Code is as follows:

+ -------- + -------------- + ------------ +

| DeptId | deptName | totalCount |

+ -------- + -------------- + ------------ +

| 2 | School of Economics | 1 |

| 3 | law school | 3 |

| 7 | faculty | 1 |

| 13 | Art Institute | 1 |

+ -------- + -------------- + ------------ +

 

Another query without using Group:

The Code is as follows:

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

 

This time it is completely displayed:

Copy the Code as follows:

+ -------- + -------------- + ------------ +

| DeptId | deptName | totalCount |

+ -------- + -------------- + ------------ +

| 1 | Philosophy Institute | 0 |

| 2 | School of Economics | 1 |

| 3 | law school | 3 |

| 4 | School of Education | 0 |

| 5 | College of literature | 0 |

| 6 | History School | 0 |

| 7 | faculty | 1 |

| 8 | Institute of Engineering | 0 |

| 9 | Agricultural College | 0 |

| 10 | Medical School | 0 |

| 11 | Military College | 0 |

| 12 | School of Management | 0 |

| 13 | Art Institute | 1 |

+ -------- + -------------- + ------------ +

 

So far, our SQL statements have been written. But how can I use JPA to query the same view?

 

As usual, EntityManager is exposed from a major entity operation service:

 

The Code is 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 come from the same entity manager. If the deployment changes in the future, only this injection can be modified.

 

Then we need to construct the object classes of the two tables as before:

 

Entity category of the school table:

 

 

The Code is 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 = 50, nullable = false)

Private String deptName;

 

// Getters and setters...

}

 

 

Student table entity class:

The Code is 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 =-59422121636291_609l;

 

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

 

}

 

 

After the two object classes are constructed, we need to create a View class. The type of the attribute is completely constructed by the structure you want. In this example, we need the school number, school name, and total number. So we define it as follows:

The Code is as follows:

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 can be said that the definition of a view object is simpler than that of an object, without annotations or ing. (to reduce the amount of code, the get and set methods for each attribute are omitted, add it on your own ). However, the only difference is that we need to construct an extra constructor with field initialization. The default no-argument constructor cannot be overwritten. Then we started to enter the real query (as a view, data cannot be modified in the SQL specification. Therefore, the view only has the SELECT feature. This is why many people want to use JPA to perform queries by ing the built-in views of the database, but the ing is always unsuccessful .)

Copy the 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 (. deptId,. deptName, (select count (*) from % 2 $ s B where B. deptId =. deptId) as totalCount) from % 1 $ s ",

Depts. class. getName (),

Students. class. getName (),

Report. class. getName ());

 

EntityManager entityManager = objectDaoService. getEntityManager ();

// Create a type Query

TypedQuery <Report> reportTypedQuery = entityManager. createQuery (jpql, Report. class );

// If you have specific query conditions, set the parameter location in jpql (? 1? 2? 3...), and then set

// ReportTypedQuery. setParameter (1, params );

List <Report> reports = reportTypedQuery. getResultList ();

Return reports;

}

 

}

 

 

In the above Code, we constructed View query statements in JPQL. The most important thing is to create a new object after the initial select statement. Then, add the query results to each attribute through the constructor of the view object. It is best to rename the field generated by statistics using as to keep it the same as the attribute name of the view object. In this way, we get the View data. Next, we will try to traverse this List, which is very convenient.

 

In addition, I would like to recommend a book called "Pro JPA 2 Mastering the Java trade Persistence API" published by Apress. This book details related JPA technologies and is very practical.