刚接手的项目中实现一个简单的分页查询功能,使用到了jpa,快速上手,提供两种思路:
第一种实现分页操作的方法(适合不太熟悉jpa,用nativeQuery的,sql是可以直接摘出来直接执行的,看起来比较方便,但查询结果需要自己转换,不够简洁,就是用pageNum,pageSize计算 limit条件 来限制查询结果)
----Controller层:
正常获取所需要的字段值调用对应Service即可
Long startTime = getLong(params, "startTime");
Long endTime = getLong(params, "endTime");
String query = getString(params, "query");
String type = getString(params, "type");
----Service层
PageBean<SearchHistoryVO> XXVO= new PageBean<>();
int startRow = pageNum * pageSize;
list = logAnalysisDao.getTnByQueryPage(startTime, endTime, query, startRow, pageSize);
total = logAnalysisDao.getTnByQueryPageCount(startTime, endTime, query);
XXVOS.setData(coverVO(list, type)); //转换为需要给前端展示的vo
XXVOS.setPageSize(pageSize);
XXVOS.setTotal(total);
XXVOS.setPageNo(pageNum);
public List<XXVO> coverVO(List<Object[]> list, String type) {
if (CollectionUtils.isEmpty(list)) {
return Collections.emptyList();
}
List<XXVO> answers = new ArrayList<>();
long i = 1L;
for (Object[] querys : list) {
Long num = Long.valueOf(String.valueOf(querys[1]));
String q = String.valueOf(querys[0]);
XXVO hqvo = new XXVO();
hqvo.setCount(num);
hqvo.setWord(q);
answers.add(hqvo);
}
return answers;
}
----Dao层
Query(value = "select query as word, COALESCE(SUM(count), 0) as count from tesla_log_analysis_hotquery "
+ "where date between :startTime and :endTime group by query ORDER BY count desc " +
" limit :startRow,:pageSize ",nativeQuery = true)
public List<Object[]> getHQBoePage(@Param("startTime") long startTime,
@Param("endTime") long endTime , @Param("startRow") int startRow,
@Param("pageSize") int pageSize);
@Query(value = "select " +
"count(a.query) from(select query from tesla_log_analysis_hotquery where date " +
"between :startTime and :endTime group by query ) a " ,nativeQuery = true)
public long getHQBoePageCount(@Param("startTime") long startTime,
@Param("endTime") long endTime );
第二种(pageable) 对应vo中需要注解标注对应数据库表和字段,通过pageable直接查询返回List<Vo>,处理比较方便
----Controller层
同样获取对应的数据
Long startTime = getLong(params, "startTime");
Long endTime = getLong(params, "endTime");
// 首尾点击位置,增加分页功能,默认每页10条,返回第一页
int pageNum = 1;
if (params.containsKey("pageNum")) {
pageNum = getInteger(params, "pageNum");
}
pageNum = pageNum >= 1 ? pageNum - 1 : 0;
int pageSize = 10;
if (params.containsKey("pageSize")) {
pageSize = getInteger(params, "pageSize");
}
----Service层
HashMap<String, Object> result = new HashMap<>();
result.put("content", new ArrayList<VO>());
result.put("totalElements", 0);
result.put("totalPages", 1);
result.put("page", 1);
Pageable pageable = new PageRequest(pageNum, pageSize);
Page<VO> searchPage = listByTimePageable(appCode, startTime, endTime, pageable );
if (searchPage == null) {
return result;
}
result.put("content", searchPage.getContent()));
result.put("totalElements", searchPage.getTotalElements());
result.put("totalPages", searchPage.getTotalPages());
result.put("page", searchPage.getNumber() + 1);
----Dao层
public interface XXDAO extends JpaRepository<Vo, Long>, JpaSpecificationExecutor {
@Query(value = "from XXwhere appCode = :appCode and date between :startTime and
:endTime order by date")
List<VO> listByTime(@Param("appCode") String appCode, @Param("startTime") long startTime, @Param("endTime") long endTime);
@Query(value = "from SearchQuality where appCode = :appCode and date between :startTime and :endTime order by date desc",
countQuery = "select count(id) from XX where appCode = :appCode and date between :startTime and :endTime")
Page<Vo> listByTimePageable(@Param("appCode") String appCode, @Param("startTime") long startTime, @Param("endTime") long endTime, Pageable pageable);