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;执行接口：
@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);
}
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
最近做项目用到了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就行
测试
@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);
}
@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);
}
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
