Use JPA to customize VO to receive and return the result set of unwrap

Directory JPA Custom VO Receive Return Result Set (unwrap) JPA Return Custom VO

JPA Custom VO Receive Return Result Set (unwrap)

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

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

2. Specification object of JPA dynamically assembles where search conditions

3. entityManager Execute CriteriaBuilder

4. entityManger executes native SQL using createNativeQuery directly. Here, the bearer designed to return the result set must be the entity corresponding to the database.

It is said here that a custom VO undertakes the method of returning 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;

The result set carries 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<Long> ids,@PathVariable String project,@PathVariable Integer pn,@PathVariable Integer ps){
        log.info("url:/po/sz/batch/"+"|param:"+ids);
        // Pass id Query data 
        List<ProjectAttendanceEntity> projects = projectAttendanceEntityRepository.findByIdIn(ids);
        // Get SQL
        String sql = getSQL(projects,pn,ps);
        Query query = entityManager.createNativeQuery(sql);
        List<AttendancePoSzVO> list = query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AttendancePoSzVO.class)).getResultList();
        // Initialize the result set 
        List<DropDownVO> 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<ProjectAttendanceEntity> 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("' and tt.rt_end_date >= '").append(po.getPunchDate()).append("') OR ");
            }
            // Cut off the last 1 A OR
            sql = new StringBuilder(sql.substring(0,sql.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 1 A AND
            sql = new StringBuilder(sql.substring(0,sql.length()-4));
            sql.append(" LIMIT ").append(pn).append(",").append((pn+1)*ps);
        return sql.toString();
    }

Core code:

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

But the setResultTransformer here is out of date.

So next, look for setResultTransformer to replace API.

JPA Returns Custom VO

Recently, JPA was used in the project, and many places need to return custom vo. At the beginning, @ Query annotation was used to return custom List < Objec[] > In the use of forEach traversal into List is really inconvenient, looking for some information to make notes.

1 general need to return to the custom VO is to do dynamic query linked to the table, and 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)

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

Don't mention the JPA interface, just inherit JpaRepository and JpaSpecificationExecutor

Test

    /**
     *  Return Entity Object, which requires all fields in the database to be queried   I.e. findAll Or understood as select *
     */
    @Test
    public void t2() {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from jpa_user where 1=1 ");
        // Self-stitching dynamically according to 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);
    }
    /**
     *  Return to Custom VO Object, the requirement is that the query alias must be the same as the VO Attribute name in 1 To 
     */
    @Test
    // Note that it is very important that the transaction must be opened. If it is not opened, it will report an error and prompt that it cannot be converted. The specific reason is related to the dynamic agent 
    @Transactional(readOnly = true)
    public void t3() {
        StringBuilder sb = new StringBuilder();
        sb.append("select name myname,card mycard from jpa_user where 1=1 ");
        // Self-stitching dynamically according to conditions, only for 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);
    }