使用JPA自定義VO接收返回結(jié)果集(unwrap)

更新時(shí)間：2021年11月22日 09:24:54 作者：樂聞x

這篇文章主要介紹了使用JPA自定義VO接收返回結(jié)果集(unwrap)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

JPA自定義VO接收返回結(jié)果集(unwrap)

JPA跟mybitis比較，簡(jiǎn)單的業(yè)務(wù)搜索是方便的，但是設(shè)計(jì)到復(fù)雜的SQL搜索時(shí)，我們需要自定義SQL。

1.@Query直接寫SQL,缺點(diǎn)是無法動(dòng)態(tài)的組裝條件

2.JPA的Specification對(duì)象動(dòng)態(tài)組裝where搜索條件

3.entityManager執(zhí)行CriteriaBuilder

4.entityManger直接使用createNativeQuery，執(zhí)行原生SQL。這里設(shè)計(jì)到返回結(jié)果集的承載體必須是數(shù)據(jù)庫(kù)對(duì)應(yīng)的實(shí)體。

這里說一個(gè)自定義的VO承接返回結(jié)果集的方法

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;

結(jié)果集承接VO (AttendancePoSzVO)

    private String poId; 
    private String poName; 
    private String zhName;

執(zhí)行接口：

/**
     * 批量修改項(xiàng)目名稱
     * @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);
        //通過id查詢數(shù)據(jù)
        List<ProjectAttendanceEntity> projects = projectAttendanceEntityRepository.findByIdIn(ids);
        //獲取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();
        //初始化結(jié)果集
        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);
    } 
 
    /**
     * 組裝查詢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 ");
            }
            //截掉最后一個(gè)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 ");
            }
            //截取最后一個(gè)AND
            sql = new StringBuilder(sql.substring(0,sql.length()-4));
            sql.append(" LIMIT ").append(pn).append(",").append((pn+1)*ps);
        return sql.toString();
    }

核心代碼：

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

但是這里的 setResultTransformer 已經(jīng)過時(shí)了。

所以接下來尋找 setResultTransformer的替代API。

JPA返回自定義VO

最近做項(xiàng)目用到了JPA，很多地方需要返回自定義vo，最開始用@Query注解返回自定義List<Objec[]>，在用forEach遍歷存入List實(shí)在是不方便，找了一些資料做下筆記。

一般需要返回自定VO都是做連表動(dòng)態(tài)查詢，下面直接貼測(cè)試代碼

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(自定義VO)

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

JPA接口不說了，繼承JpaRepository和JpaSpecificationExecutor就行

測(cè)試

    /**
     * 返回Entity對(duì)象，要求是數(shù)據(jù)庫(kù)中字段全部查詢 即findAll或者理解為select *
     */
    @Test
    public void t2() {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from jpa_user where 1=1 ");
        //自行根據(jù)條件動(dòng)態(tài)拼接，僅做演示
        sb.append("  and name like '%李%' ");
        Query nativeQueryPo = entityManager.createNativeQuery(sb.toString(), User.class);
        List resultList = nativeQueryPo.getResultList();
        System.out.println(resultList);
    }
    /**
     * 返回自定義VO對(duì)象，要求是查詢別名必須和VO中屬性名一致
     */
    @Test
    //注意，很重要，事務(wù)必須開啟，不開啟會(huì)報(bào)錯(cuò)提示無法轉(zhuǎn)化，具體原因和動(dòng)態(tài)代理有關(guān)系
    @Transactional(readOnly = true)
    public void t3() {
        StringBuilder sb = new StringBuilder();
        sb.append("select name myname,card mycard from jpa_user where 1=1 ");
        //自行根據(jù)條件動(dòng)態(tài)拼接，僅做演示
        sb.append("  and name like '%李%' ");
        Query nativeQuery = entityManager.createNativeQuery(sb.toString());
        List list = nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(UserRespDto.class)).list();
        System.out.println(list);
    }

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: