使用JPA自定义VO接收返回结果集(unwrap)

使用JPA自定义VO接收返回结果集(unwrap)

目录JPA自定义VO接收返回结果集(unwrap)JPA返回自定义VO

JPA自定义VO接收返回结果集(unwrap)

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 ids,@PathVariable String project,@PathVariable Integer pn,@PathVariable Integer ps){

log.info("url:/po/sz/batch/"+"|param:"+ids);

//通过id查询数据

List projects = projectAttendanceEntityRepository.findByIdIn(ids);

//获取SQL

String sql = getSQL(projects,pn,ps);

Query query = entityManager.createNativeQuery(sql);

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

//初始化结果集

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);

}

/**

* 组装查询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("' 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 list = query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AttendancePoSzVO.class)).getResultList();

但是这里的 setResultTransformer 已经过时了。

所以接下来寻找 setResultTransformer的替代API。

JPA返回自定义VO

最近做项目用到了JPA，很多地方需要返回自定义vo，最开始用@Query注解返回自定义List，在用forEach遍历存入List实在是不方便，找了一些资料做下笔记。

一般需要返回自定VO都是做连表动态查询，下面直接贴测试代码

User（Entity）

@Data

@Entity

@Table(name = "jpa_user")

@AllArgsConstZHmyYjXjoBructor

@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);

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。