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做分页查询的缺点就不必说了…
各位哥哥们酌情参考。如果有更好的解决方案、批评指正欢迎在评论告诉我或者邮件给我…