1. Spring Data JPA 查询接收自定义对象 （非数据库对应的实体类）需要我们定义一个接收返回结果集的接口类
/**
-
账号 用户表关联查询 的结果集
*/
public interface UserInfoResultSets {//用户帐号ID
Long getId();
//用户名称
String getUserName();
//邮箱
String getUserEmail();
}
2.Repository 接口中的分页查询写法
@Query(value = "SELECT a.user_email as userEmail,a.id as id,u.user_name as userName FROM (sys_account a inner join sys_user_role r on a.id = r.user_id ) inner join user_info u on a.id = u.account_id where r.role_id = :roleId ",
countQuery = "SELECT count(*) FROM (sys_account a inner join sys_user_role r on a.id = r.user_id ) inner join user_info u on a.id = u.account_id where r.role_id = :roleId",
nativeQuery = true)
Page<UserInfoResultSets> findPageByRoleId(@Param("roleId") Long roleId, Pageable pageable);
查询字段：a.user_email as userEmail 对应结果集接口类的 getUserEmail
3. 定义一个 VO 类
@Data
@EqualsAndHashCode(callSuper = false)
public class UserInfoVO {
private Long id;
private String userEmail;
private String userName;
}
vo中的字段对应结果集接口类
4. 在业务逻辑中使用
private RestfulVo bulidAccountPage(Byte sign,UserRoleQuery userRoleQuery){
Long roleId = userRoleQuery.getRoleId();
//生成Pageable变量
Pageable pageable = PageRequest.of(userRoleQuery.getP().getPageNum()-1,userRoleQuery.getP().getPageSize());
Page<UserInfoResultSets> accountPage = sign == 1 ? this.accountRepository.findPageByRoleIdEliminate(roleId,pageable) : this.accountRepository.findPageByRoleId(roleId,pageable);
Long totalElements = accountPage.getTotalElements();
List<UserInfoVO> accountVOList = null;
if(totalElements > 0){
// 将 UserInfoResultSets结果集中的属性值 拷贝到UserInfoVO 中
accountVOList = DozerBeanMapperUtil.copyProperties(accountPage.getContent(),UserInfoVO.class);
}
RestfulVo restfulVo = ResultUtil.success(accountVOList);
restfulVo.setTotalElements(totalElements);
return restfulVo;
}
