Shulou(Shulou.com)06/02 Report--

This article mainly introduces how JPA uses nativequery multi-table association query to return custom entity classes, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

JPA nativequery multi-table association query returns custom entity class

The multi-table association query officially recommended by JPA is inconvenient to use. Some items contacted may use JPA for simple query and Mybaits for complex query. So I want to find a solution that works well.

Implementation of Multi-table Association in JPA

1. Use Specification to achieve mapping matching, such as @ ManyToOne, etc.

two。 Use sql or hql such as NativeQuery to implement

Comparison of advantages and disadvantages

1. Mapping is the foundation of hibernate, and many people will get used to it. Individuals do not like this approach, reusability is too weak, and inflexible, especially in the case of multi-table complex business.

two。 To use Specification, you need to inherit the JpaSpecificationExecutor interface, construct the corresponding method and pass in the Specification object that encapsulates the query condition. The logic is simple and easy to understand, but constructing Specification objects requires stitching format conditions that are very tedious.

3. Individuals like to directly use NativeQuery to implement complex queries, which is intuitive and convenient, but the disadvantage is that they can not return custom entity classes. You need to manually encapsulate the utility class to reflect the Object to the target object.

Use sql and return a custom entity class

Personal preference for implementation, not to mention looking at the code

Import org.springframework.stereotype.Repository;import javax.persistence.EntityManager;import javax.persistence.PersistenceContext;import javax.transaction.Transactional; @ Repositorypublic class EntityManagerDAO {@ PersistenceContext private EntityManager entityManager / * return * / @ Transactional public List listUser () {String sql = "select a.create_time createTime,"+" a.mobilephone phoneNum, "+" a.email email,a.uid uid, "+" a.enabled enabled, "+" c.id_number idNumber "+" (case b.`status` when 1 then 1 else 0 end) status "+" from tbl_sys_user a "+" LEFT JOIN user_high_qic b on a.uid=b.u_id "+" LEFT JOIN user_qic c on a.uid=c.uid "+" ORDER BY status desc " SQLQuery sqlQuery = entityManager.createNativeQuery (sql) .unwrap (SQLQuery.class); Query query = sqlQuery.setResultTransformer (Transformers.aliasToBean (BackstageUserListDTO.class)); List list = query.list (); entityManager.clear (); return list;}} public class BackstageUserListDTO implements Serializable {private static final long serid = 1L; private String createTime; private String phoneNum; private String email; private BigInteger uid; private Integer enabled; private String idNumber Private BigInteger status; / / GETTER SETTER}

If such a requirement is implemented in the first two ways, it will undoubtedly be very troublesome. In this way, you can directly reflect the required custom entity classes.

According to the requirements can be organized and packaged into different methods, adding sorting, paging and so on. Here I mainly provide a convenient solution.

JPA multi-table association dynamic query (custom sql statement)

Project requirements, query requirement data requires multi-table links-> filter the queried data according to a variety of conditions, look up a lot of information on the Internet, and finally choose this string splicing query

Dynamic query like this

The following is a summary of the use in my project:

Entity Class / * * order Table * / @ Entity@Table (name = "signedorder") @ Getter@Setter@NoArgsConstructor@EntityListeners (AuditingEntityListener.class) public class SignedOrder {@ Id @ GeneratedValue (strategy = GenerationType.IDENTITY) @ Column private Integer id;//id @ CreatedDate @ Column (updatable = false) private Date createTime;// creation time @ LastModifiedDate @ Column private Date lastModifiedTime / / modification time @ ManyToOne (fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH}) @ JoinTable (name = "staff_signedorder", joinColumns = @ JoinColumn (name = "signedorder_id"), inverseJoinColumns = @ JoinColumn (name = "staff_id")) user to which private Staff staff;// belongs @ JoinColumn (name = "industry_id") private Integer industryId / / Industry Id} / * user Table * / @ Entity@Table (name = "staff") @ Getter@Setter@NoArgsConstructorpublic class Staff {@ Id @ GeneratedValue (strategy = GenerationType.IDENTITY) @ Column (name = "id") private Integer id;//id @ Column (name = "name", length = 25) private String name;// name @ JoinColumn (name = "city_id") private Integer cityId / / id/** * city table * / @ Entity@Table (name = "city") @ Getter@Setter@NoArgsConstructor@Accessors (chain = true) public class City {@ Id @ GeneratedValue (strategy = GenerationType.IDENTITY) @ Column (name = "id") private Integer id;//id @ Column (name = "name", length = 50) private String name;// name} / / the industry table is consistent with the city table.

Explanation of related annotations in entity classes:

@ Entity: comment on the entity. Any Hibernate mapping object should have this comment

Table: declares that this object is mapped to the database's data table, this comment is not required, if not, the system uses the default value (the short class name of the entity)

@ Getter, @ Setter, @ NoArgsConstructor:lombok provide annotations, get, set methods and no-parameter construction

@ EntityListeners (AuditingEntityListener.class): with this note, the time notes @ LastModifiedDate and @ CreatedDate will not take effect.

@ Id: declare this property as the primary key

@ GeneratedValue (strategy = GenerationType.IDENTITY): specify the primary key

TABLE: use a specific database table to hold the primary key

IDENTITY: the primary key is automatically generated by the database (mainly auto-growing)

SEQUENCR: primary keys are generated based on the sequence of the underlying database, provided that the database supports sequences

AUTO: primary key is controlled by program

@ CreatedDate (updatable = false): create the time time field. In insert, the value is set; when update, the time is the same.

@ LastModifiedDate: modify the time period. The value will be modified when update.

@ ManyToOne (fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH}): many to one

FetchType.EAGER: load immediately and get associated entities

CascadeType.MERGE: cascading updates

CascadeType.PERSIST: cascading new

CascadeType.REFRESH: cascading refresh

@ JoinTable: JoinColumn: the field that holds the foreign key of the associated relationship; inverseJoinColumns: another foreign key word that saves the relationship

@ Column: used to identify the correspondence between attributes in an entity class and fields in a data table

The test class @ RunWith (SpringRunner.class) @ SpringBootTestpublic class SprinBootMarketingsystemApplicationTests {@ PersistenceContext//jpa 's database operation class private EntityManager entityManger; @ Testpublic void queryDb () {/ / assigns the parameter Integer cityId = 1; Integer industryId = 2; Integer staffId = 16; Date startTime = DateUtil.parse ("1970-01-01") / / string time is converted to date type Date endTime = Calendar.getInstance () .getTime () / / get the date type for the current time of the system / / create the SQL statement body StringBuffer stringBuffer = new StringBuffer ("\ tSELECT\ n" + "\ tcount (*) count) \ n "+"\ tci.NAME cityName\ n "+"\ tFROM\ n "+"\ tsignedorder s\ n "+"\ tLEFT JOIN staff_signedorder t ON s.id = t.signedorder_id\ n "+"\ tLEFT JOIN staff sta ON t.staff_id = sta.id\ n "+" \ tLEFT JOIN city ci ON sta.city_id = ci.id\ n "+"\ tWHERE\ n "+"\ T1 = 1 ") Map map = new HashMap (); / / stitching dynamic parameters if (industryId! = null) {/ * the first way to assign values to parameters 1 represents the order of parameters passed in, and assigns values to parameters nativeQuery.setParameter (1, industryId); stringBuffer.append ("and s.industryId =? 1") * / industryId represents the passed parameter name and assigns values to the parameter nativeQuery.setParameter ("industryId", industryId); stringBuffer.append ("and s.industry_id =: industryId"); map.put ("industryId", industryId);} if (cityId! = null) {stringBuffer.append ("and ci.id =: cityId") Map.put ("cityId", cityId);} if (staffId! = null) {stringBuffer.append ("and sta.id =: staffId"); map.put ("staffId", staffId) } if {/ / using this assignment method, the time type needs to be given three parameters, parameter name, parameter value, specific mapping type TemporalType.DATE / / nativeQuery.setParameter ("create_time", startTime,TemporalType.DATE); stringBuffer.append ("and s.create_time BETWEEN: startTime and: endTime") Map.put ("startTime", startTime); map.put ("endTime", endTime);} Query nativeQuery = entityManger.createNativeQuery (stringBuffer.toString ()); for (String key: map.keySet ()) {nativeQuery.setParameter (key, map.get (key)) } / / three ways to accept the returned results (the first way) / * nativeQuery.unwrap (SQLQuery.class) .setResultTransformer (Transformers.TO_LIST); List resultList1 = nativeQuery.getResultList (); for (Object o: resultList1) {System.out.println (o.toString ()) The second method is similar to the first one / * nativeQuery.unwrap (SQLQuery.class) .setResultTransformer (Transformers.ALIAS_TO_ENTITY_MAP); List resultList = nativeQuery.getResultList (); for (Map map1: resultList) {System.out.println (map1) The third way: entity class accepts nativeQuery.unwrap (SQLQuery.class) .setResultTransformer (Transformers.aliasToBean (TestVo.class)); List resultList = nativeQuery.getResultList (); for (TestVo svo:resultList) {System.out.println (svo.toString ());}} print the result

The first way to print the result

The second way to print the result

The third way to print the result

TestVo entity receives class @ Datapublic class TestVo {private String cityName;// city name private BigInteger count / / number of signatures (must be accepted using BigInteger type)} Thank you for reading this article carefully. I hope the article "how to use nativequery multi-table association query to return custom entity classes" shared by the editor is helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.