您好,登录后才能下订单哦!
这篇文章主要介绍如何使用JPA自定义VO接收返回结果集,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!
JPA跟mybitis比较,简单的业务搜索是方便的,但是设计到复杂的SQL搜索时,我们需要自定义SQL。
1.@Query直接写SQL,缺点是无法动态的组装条件
2.JPA的Specification对象动态组装where搜索条件
3.entityManager执行CriteriaBuilder
4.entityManger直接使用createNativeQuery,执行原生SQL。这里设计到返回结果集的承载体必须是数据库对应的实体。
这里说一个自定义的VO承接返回结果集的方法
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;
结果集承接VO (AttendancePoSzVO)
private String poId; private String poName; private String zhName;
执行接口:
/** * 批量修改项目名称 * @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查询数据 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(); //初始化结果集 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 "); } //截掉最后一个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 "); } //截取最后一个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 已经过时了。
所以接下来寻找 setResultTransformer的替代API。
最近做项目用到了JPA,很多地方需要返回自定义vo,最开始用@Query注解返回自定义List<Objec[]>,在用forEach遍历存入List实在是不方便,找了一些资料做下笔记。
一般需要返回自定VO都是做连表动态查询,下面直接贴测试代码
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就行
测试
/** * 返回Entity对象,要求是数据库中字段全部查询 即findAll或者理解为select * */ @Test public void t2() { StringBuilder sb = new StringBuilder(); sb.append("select * from jpa_user where 1=1 "); //自行根据条件动态拼接,仅做演示 sb.append(" and name like '%李%' "); Query nativeQueryPo = entityManager.createNativeQuery(sb.toString(), User.class); List resultList = nativeQueryPo.getResultList(); System.out.println(resultList); } /** * 返回自定义VO对象,要求是查询别名必须和VO中属性名一致 */ @Test //注意,很重要,事务必须开启,不开启会报错提示无法转化,具体原因和动态代理有关系 @Transactional(readOnly = true) public void t3() { StringBuilder sb = new StringBuilder(); sb.append("select name myname,card mycard from jpa_user where 1=1 "); //自行根据条件动态拼接,仅做演示 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); }
以上是“如何使用JPA自定义VO接收返回结果集”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注亿速云行业资讯频道!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。