JPA自定义SQL分页查询

【存在的问题】

在使用Spring JPA原生SQL进行分页查询时存在如下问题：

1、使用List转Page工具类导致查询速度较慢，原因在于先把所有数据查询出来返回成List，然后再进行分页转换

常用ListConvertToPageUtils如下：

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import java.util.ArrayList;
import java.util.List;

public class ListConvertToPageUtils {
    public static <T> Page<T> listConvertToPage(List<T> list, Pageable pageable) {

        int start = ( int )pageable.getOffset();
        int end = (start + pageable.getPageSize()) > list.size() ? list.size() : (start + pageable.getPageSize());
        if(start > end){
            return new PageImpl<T>(new ArrayList<>(), pageable, list.size());
        }
        return new PageImpl<T>(list.subList(start, end), pageable, list.size());
    }
}

使用示例

Page<Event> events = ListConvertToPageUtils.listConvertToPage(list, pageable);

2、使用countQuery造成书写麻烦，代码显得过于臃肿

@Query(value = "SELECT  * FROM log where createtime BETWEEN :startTime AND :stopTime" ,
      countQuery = "SELECT  COUNT(1)  FROM  log where createtime BETWEEN :startTime AND :stopTime",
      nativeQuery = true)
Page<AlertLog> getLogByPage(Pageable pageable, Timestamp startTime, Timestamp stopTime);

【正确书写方法】

不需要使用countQuery即可实现分页，需要注意的是SQL语句的书写规范，主表必须使用别名

常见错误一

主表不加别名，造成select count(where)报错

@Query(value = "SELECT  username,userid,ip,path,name,type,createtime " +
            "FROM  system_log where createtime BETWEEN :startTime AND :stopTime ORDER BY createtime DESC" ,
            nativeQuery = true)
    Page<SystemLogVO> getSystemLog(Pageable pageable, Timestamp startTime, Timestamp stopTime);

select count(where) FROM  system_log where createtime BETWEEN ? AND ?

加上别名后正常查出结果

select count(T) FROM  system_log T where createtime BETWEEN ? AND ?

常见错误二

SQL语句不规范，造成select count(WHERE)报错

@Query(value = "SELECT DISTINCT T1.typename AS algoname,T2.type AS isopen,T3.*,T3.camurl AS cam_url FROM(" +
        "select * from analyse_apply " +
        "WHERE typeid in (SELECT id from algo_model_sub_type WHERE  id =:id)) T1\n" +
        "LEFT JOIN analyse_camera T2\n" +
        "ON T1.id = T2.analyseid\n" +
        "LEFT JOIN (SELECT  * FROM camera WHERE districtid in :idlist AND name Like :name) T3\n" +
        "ON T2.cameraid = T3.id\n" +
        "WHERE T3.id is not null",
        nativeQuery = true)
Page<CameraVO> findAllCameraById(Pageable pageable,Integer id, List<Integer> idlist, String name);

有人会觉得是不是没加别名造成的，加上之后重新执行仍然报错

--查询SQL
SELECT DISTINCT T1.typename AS algoname,T2.type AS isopen,T3.*,T3.camurl AS cam_url FROM(select * from analyse_apply T11 WHERE typeid in (SELECT id from algo_model_sub_type T12 WHERE  id =?)) T1
LEFT JOIN analyse_camera T2
ON T1.id = T2.analyseid
LEFT JOIN (SELECT  * FROM camera T13 WHERE districtid in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND name Like ?) T3
ON T2.cameraid = T3.id
WHERE T3.id is not null limit ?
--生成的count SQL
select count(DISTINCT T11) from analyse_apply T11 WHERE typeid in (SELECT id from algo_model_sub_type T12 WHERE  id =?)) T1
LEFT JOIN analyse_camera T2
ON T1.id = T2.analyseid
LEFT JOIN (SELECT  * FROM camera T13 WHERE districtid in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND name Like ?) T3
ON T2.cameraid = T3.id
WHERE T3.id is not null

仔细对比会发现countSQL与查询SQL并不太一样，造成此问题的原因是SQL语句主表与关联表书写不规范

正确示例如下

@Query(value = "SELECT DISTINCT T1.algoname,T2.*,T2.camurl AS cam_url " +
        "FROM camera_administer T1 \n" +
        "LEFT JOIN (SELECT  * FROM camera WHERE districtid in (:idlist) AND name Like :name) T2\n" +
        "ON T1.cameraid = T2.id " +
        "WHERE T2.id is not null " +
        "AND T1.algoid in (SELECT id from algo_model_sub_type WHERE  id =:id) ",
        nativeQuery = true)
Page<CameraVO> findAllCameraById(Pageable pageable,Integer id, List<Integer> idlist, String name);

我们知道在进行LEFT JOIN的时候，结果数据的数量就等于主表查询结果的数量