Parsing JPA View Query issues

         This article is mainly on the JPA view query issues for detailed analysis, the need for friends can come to the reference, I hope to help you.

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 take a "chestnut"           Suppose 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 read), is as follows: The     code is the following:--------------------- -----------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 ' , "The Institute of Philosophy"); 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 ', ' 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:   code as follows:--------------------------------Table structure for ' students '------- -----------------------DROP TABLE IF EXISTS ' students '; CREATE TABLE ' Students ' (  ' Stuno ' bigint () unsigned not NULL auto_increment ' School number starting from 1000 ',   ' COMMENT ' int (a) unsigned NOT NULL COMMENT ' College ID ',   ' stuname ' varchar NOT null COMMENT ' student name ',   PRIMARY KEY (' StuN O '),   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 ' VALUE S (' 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 implementation methods:    use GROUP BY and do not use GROUP by:       Code as follows: SELECT B.deptid, B.deptname, COUNT (*) as ' Totalcoun T ' from students A-left JOIN depts B to A.deptid=b.deptid GROUP by B.deptid order by B.deptid;   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? ) code as follows: +--------+--------------+------------+ | DeptID | Deptname     | TotalCount | +--------+--------------+------------+ |      2 | School of economics     |          1 | |      3 | Law school       |          3 | |      7 | College of Science       |          1 | |     13 | College of Art     |          1 | +--------+--------------+------------+   Another query that does not use GROUP by: The code is 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 all shows up.:  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 | College of Science       |          1 | |      8 | Institute of Technology       |          0 | |      9 | Agricultural College       |          0 | |     10 | Medical College       |          0 | |     11 | Military Academy     |          0 | |     12 | School of management     |          0 | |     13 | College of Art     |          1 | +--------+--------------+------------+   At this point, our SQL is written through. But how can you use JPA to query for the same view?     We follow the usual code, from a major entity operations services exposed Entitymanager to:   Code as follows: Package Net.csdn.blog.chaijunkun.dao;   Import Javax.persistence.EntityManager; Import Javax.persistence.PersistenceContext; &nbSp 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 doing so 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 need to construct two-table entity classes:   College Table entity class:     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 ...}     Student Table entity class:  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 {   /**   *  &N Bsp * *  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 then we're going to get a view class, which is constructed entirely of the structure you want. For example In this example we have to institute number, college name and total number. So let's just define the:  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; &NBSP}    //getters and setters ...  }     can say that the View object'sDefinitions are simpler than entity definitions, do not require annotations, and do not need to be mapped (the above code eliminates the get and set methods of each property in order to reduce the amount of code, add it yourself). But the only difference is that we need to construct an extra constructor with field initialization. And you cannot overwrite the default parameterless constructor. Then we started to go into the real query (as a view, the SQL specification is not allowed to modify data.) 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 Objectdaos Ervice 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 $ A ",     Depts.class.getName (),     Students.class.getName (),     Report.class.getName ());     Entitymanager entitymanager= Objectdaoservice.getentitymanager ();  //Establish a type of query   typedquery<report> reporttypedquery= entitymanager.createquery (JPQL, Report.class);  //Other detailed query conditions in the JPQL to set aside the parameter position to (? 1 2 3 ...), then in this setting  //reporttypedquery.setparameter (1, params);   list<report> reports= reporttypedquery.getresultlist ();   Return reports; &NBSP}  }     in the above code, we constructed 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.