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

This article mainly introduces how to use JPA custom VO to receive the returned result set, the article is very detailed, has a certain reference value, interested friends must read it!

JPA Custom VO receive return result set (unwrap)

Compared with mybitis, simple business search is convenient for JPA, but when designing complex SQL search, we need to customize SQL.

1.@Query writes SQL directly, but the disadvantage is that it can not dynamically assemble the conditions.

Where search conditions for dynamic assembly of Specification objects in 2.JPA

3.entityManager executes CriteriaBuilder

4.entityManger uses createNativeQuery directly to execute native SQL. Here the carrier designed to return the result set must be the corresponding entity of the database.

Here is a custom VO method that accepts the return of the result set.

ProjectAttendanceEntity @ Id @ GeneratedValue (strategy = GenerationType.IDENTITY) private Long id; @ Column (name = "f_id") private Long fId; @ Column (name = "user_id") private Integer userId; @ Column (name = "zh_name") private String zhName; @ Column (name = "po_code") private String poCode; @ Column (name = "po_name") private String poName @ Column (name = "punch_date") private String punchDate; @ Column (name = "is_original") private String isOriginal; @ Column (name = "attendance_hours") private String attendanceHours; @ Column (name = "work_hours") private String workHours; @ Column (name = "punch_area") private String punchArea

Result set inherits VO (AttendancePoSzVO)

Private String poId; private String poName; private String zhName

Execution interface:

/ * * batch modify project name * @ return * / @ PostMapping ("/ po/sz/batch/ {project} / {pn} / {ps}") public PageResultVO findBatchPoInfoByUserIdAndDate (@ RequestBody List ids,@PathVariable String project,@PathVariable Integer pn,@PathVariable Integer ps) {log.info ("url:/po/sz/batch/" + "| param:" + ids) / / query data List projects = projectAttendanceEntityRepository.findByIdIn (ids) through id; / / get SQL String sql = getSQL (projects,pn,ps); Query query = entityManager.createNativeQuery (sql); List list = query.unwrap (NativeQuery.class) .setResultTransformer (Transformers.aliasToBean (AttendancePoSzVO.class)). GetResultList (); / / initialize the result set List result = new ArrayList () For (AttendancePoSzVO poSz: list) {result.add (new DropDownVO (poSz.getPoName (), poSz.getPoId ();} return new PageResultVO (GlobalReturnCode.SUCCESS_CODE, "SUCCESS", ps,pn,result) } / * Assembly query SQL * @ return * / public String getSQL (List poStatus, Integer pn, Integer ps) {StringBuilder sql = new StringBuilder ("SELECT DISTINCT res.po_id as poId,res.po_name as poName, GROUP_CONCAT (DISTINCT res.user_id) AS zhName"); sql.append ("FROM (") Sql.append ("SELECT tt.po_name,tt.po_id,tt.user_id"); sql.append ("FROM sie_sz_po_attendance_v tt"); sql.append ("WHERE") For (ProjectAttendanceEntity po: poStatus) {sql.append ("(tt.user_id =") .append (po.getUserId ()) .append ("and tt.rt_begin_date ='") .append (po.getPunchDate ()) .append ("') OR");} / / truncate the last OR sql = new StringBuilder (sql.substring (0dsql.length ()-3)) Sql.append (") res"); sql.append ("GROUP BY res.po_name,res.po_id"); sql.append ("HAVING"); for (ProjectAttendanceEntity po: poStatus) {sql.append ("INSTR (zhName,") .append (po.getUserId ()) .append (") > 0"). Append ("AND") } / / intercept the last AND sql = new StringBuilder (sql.substring (0Magazine sql.length ()-4); sql.append ("LIMIT") .append (pn) .append (",") .append ((pn+1) * ps); return sql.toString ();}

Core code:

List list = query.unwrap (NativeQuery.class) .setResultTransformer (Transformers.aliasToBean (AttendancePoSzVO.class)) .getResultList ()

But the setResultTransformer here is out of date.

So next look for an alternative to API for setResultTransformer.

JPA returns custom VO

Recently, I have used JPA to do the project, and I need to return custom vo in many places. At first, I use @ Query annotation to return custom List. It is really inconvenient to use forEach to traverse and store List. I have found some materials to take notes.

Generally, you need to return a custom VO to make a dynamic query of the linked table. The test code is directly posted below.

User (Entity)

@ Data@Entity@Table (name = "jpa_user") @ AllArgsConstructor@NoArgsConstructor@Accessors (chain = true) public class User {@ Id @ GeneratedValue (strategy = GenerationType.IDENTITY) private Integer id; @ Column (name = "name") private String name; @ Column (name = "age") private Integer age; @ Column (name = "sex") private String sex; @ Column (name = "card") private String card @ Column (name = "children") private Boolean children;}

UserRespDto (Custom VO)

@ Datapublic class UserRespDto implements Serializable {private String myname; private String mycard;}

Forget the JPA interface, just inherit JpaRepository and JpaSpecificationExecutor

test

/ * the Entity object is returned, which requires that all fields in the database are queried, that is, findAll or understood as select * * / @ Test public void T2 () {StringBuilder sb = new StringBuilder (); sb.append ("select * from jpa_user where 1x1"); / / dynamically spliced according to the conditions, only for demonstration sb.append ("and name like'% Li%'") Query nativeQueryPo = entityManager.createNativeQuery (sb.toString (), User.class); List resultList = nativeQueryPo.getResultList (); System.out.println (resultList) } / * the custom VO object is returned, which requires that the query alias must be the same as the attribute name in VO * / @ Test / / Note, it is very important that the transaction must be opened. If it is not opened, the error message cannot be converted. The specific reason is related to the dynamic proxy @ Transactional (readOnly = true) public void T3 () {StringBuilder sb = new StringBuilder () Sb.append ("select name myname,card mycard from jpa_user where 1mm 1"); / / dynamic splicing according to the conditions, only do the demonstration sb.append ("and name like'% Li%'"); Query nativeQuery = entityManager.createNativeQuery (sb.toString ()); List list = nativeQuery.unwrap (NativeQueryImpl.class) .setResultTransformer (Transformers.aliasToBean (UserRespDto.class)). List (); System.out.println (list) } the above is all the contents of the article "how to use JPA Custom VO to receive and return result sets". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.