【SpringDataJPA工作笔记】: 先排序后分组的分页条件查询

1、需求说明

有如下数据表：
用户的登录日志记录，需要查询出每个用户最新一条的登录信息，即分组前先排序，获取每个人login_time字段值最大的那一行。

log_id	login_user_id	login_user_account	login_time	logout_time	logout_way
1	1	zhangsan	2020-01-01 10:01	2020-01-01 11:22	1
2	1	zhangsan	2020-01-04 8:01	2020-01-05 10:12	1
3	2	lisi	2020-02-08 2:01	2020-02-08 9:14	2
…	… .	…	…	…	…

期望得到结果：

login_user_id	login_user_account	login_time	logout_time
1	zhangsan	2020-01-04 8:01	2020-01-05 10:12
2	lisi	2020-02-08 2:01	2020-02-08 9:14

2、纯SQL实现（数据库语句）

使用纯sql方式实现（不带分页和条件）：

select a.login_user_id, a.login_user_account, a.login_time, a.logout_time 
from ( select * from sys_login_log order by login_time desc ) a group by a.login_user_account

此种方式可使用mybatis 框架很容易写出来，但是SpringDataJPA就有些心有余力不足，至少我的知识储备搞不出来…

3、SpringDataJPA nativeSql实现

(1）表实体

@Entity
@Setter
@Table(name = "sys_login_log")
public class SysLoginLog {
	// 日志ID
	private Long logId;
	// 登录用户ID
    private Long loginUserId;
    // 登录用户账号
    private String loginUserAccount;
    // 登录时间
    private Long loginTime;
    // 登出时间
    private Long logoutTime;
    // 登录方式
    private Integer logoutWay;
    @Id
    @Column(name = "log_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long getLogId() { return logId; }
    @Basic
    @Column(name = "login_user_id")
    public Long getLoginUserId() { return loginUserId; }
    @Basic
    @Column(name = "login_user_account")
    public String getLoginUserAccount() { return loginUserAccount; }
    @Basic
    @Column(name = "login_time")
    public Long getLoginTime() { return loginTime; }
    @Basic
    @Column(name = "logout_time")
    public Long getLogoutTime() { return logoutTime; }
    @Basic
    @Column(name = "logout_way")
    public Integer getLogoutWay() { return logoutWay; }
}

(2) VO对象

@Data
public class LoginLogOutput {
	// 登录用户ID
    private Long loginUserId;
    // 登录用户账号
    private String loginUserAccount;
    // 登录时间
    private Long loginTime;
    // 登出时间
    private Long logoutTime;
}

(3) Service层实现

@Autowired
private EntityManager entityManager;

public MyPage<LoginLogOutput> listLog(Integer pageIndex,Integer pageSize,String userAccount){
	// 定义分页参数
    Pageable pageable = PageRequest.of(pageIndex, pageSize);
    // 定义基础nativeSQL
    StringBuilder sqlStrBuilder = new StringBuilder("select * ")
                .append("from ( select * from sys_login_log order by login_time desc ) a ")
                .append("where 1=1 ");
    // 填充条件查询参数
    if(userAccount != null && !"".equals(userAccount)){
		sqlStrBuilder.append("and a.login_user_account like ")
                    .append("'%'").append(input.getLoginUserAccount()).append("'%'");
	}
	// 添加GroupBy 条件
    sqlStrBuilder.append(" group by a.login_user_account");
    // 添加OrderBy 条件
    sqlStrBuilder.append(" order by a.login_time desc");
    // 执行查询
    Query query = entityManager.createNativeQuery(sqlStrBuilder.toString(),SysLoginLog.class);
    // 获取匹配数据总数，即total
    Integer total = query.getResultList() == null ? 0 : query.getResultList().size();
    // 对结果进行分页
    query.setFirstResult(pageIndex  * pageSize);
    query.setMaxResults(pageSize);
    Page<SysLoginLog> userLogPage = new PageImpl<SysLoginLog>(query.getResultList(),pageable,total);
    // 进行VO字段转换
  	List<LoginLogOutput> userLogPageOutputList = userLogPage.getContent().stream().map(s -> {
            LoginLogOutputoutput = new LoginLogOutput();
            BeanUtils.copyProperties(s,output);
            return output;
        }).collect(Collectors.toList());
	
	// 省略分页组件数据装配(每个人的分页组件都不太一样)... 
}

至此就完成了对数据的查询，以上代码中：
当前页数据：userLogPageOutputList
当前页：pageIndex
每页数量：pageSize
数据总行数：userLogPage.getTotalElements()
数据总页数：userLogPage.getTotalPages()

4、写在最后

这次的工作阻塞点共持续了5个多小时，十分可怕… 后续还是要继续深入学习SpringDataJPA，一定有其他更好的实现方式，比如Specification。

由于老弟我学艺不精，个人当前的分页实现方式实在是low，不过好歹完成了需求功能的开发，里面隐含的问题有：
1、会触发两次查询（一次带limit，一次不带）
2、nativeSQL做分页查询的缺点就不必说了…

各位哥哥们酌情参考。如果有更好的解决方案、批评指正欢迎在评论告诉我或者邮件给我…