【原创】spring data jpa在mysql分页中的实例（一次访问同时获取数据和总数）

~ 前言

起因：

这几天被spring jpa的分页折磨，鄙人就爱用sql，以前都是@Query("原生sql语句", native=true)解决问题。但是涉及到大数据量的多条件查询拼接sql就GG了，最开始使用...where 1=1 and IF(:snYear='', 1=1, sn_year=:snYear)...这种方式拼接，但是数据量一大上来（700多万条），发现居然不走sn_year索引（/心碎）。上网看了看索引失效情况，大概原因是参与函数计算的索引字段会导致该字段的索引失效。

另寻它法：

JPA自带的各种类（JPA有好几种方式来拼接sql）的拼接sql真的是恶心至极！代码量不仅远远超过sql原代码，而且很不清晰。
像我这种sql原驻民（偏爱手写sql，一目了然，好控制），还得学习他的语法，真是看了就烦！！

经不断的研究发现，JPA其中的EntityManger类的方式还是很清晰的！完美满足我的想法。

一、原生sql语句

mysql中语句如下

select SQL_CALC_FOUND_ROWS 
 sn,max(count) as active_count
from sn_state_changed
where sn_year = 'zz' 
group by sn 
limit 0,10;
    
select FOUND_ROWS() as total;

解释：

SQL_CALC_FOUND_ROWS

供后面的查询总数sql语句使用（select found_rows()）

1. 如果不加这个，后面的select found_rows()返回的数量为limit后的数量，而不是总数。
2. 注意使用空格隔开，没有逗号

FOUND_ROWS()

查询总数sql语句

二、正式查询

这两个语句必须在同一个查询的session中，否则查询的出来的数量是错误的。

JPA中使用@PersistenceContext EntityManage em来处理。
 

解释：
@PersistenceContext就是用来为每个线程创建一个EntityManager的，每个线程会生成新的session，所以保证了一个方法中多次调用执行em也是在同一个session中

所以Navicat查询（一个session中可以执行多句sql，返回多个结果N）：

 查询结果（注意结果有两个）： 

三、Spring Data JPA中的实例应用

...

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.*;


@Slf4j
@Service
public class SnService {

    @PersistenceContext
    private EntityManager em;

//    @Modifying
//    @Transactional
    public PageResultVO<SnStateChangedVO> getSnActiveCount(String snChannels, String snVersion, String snYear, Integer fromCount, Integer toCount, Integer page, Integer size) {
         List<String> snChannelList = Arrays.asList(snChannels.split(","));

         // 原来的方式过慢，所以放弃（原因：sql中的where条件中的IF(...)导致不走索引，导致查询过慢
         // List<SnStateChangedVO> leaderboards = snDTORepository.getSnActiveCountForLeaderboard(snChannelList, snVersion, snYear, fromCount, toCount);

        Map<String, Object> params = new HashMap();
        StringBuilder sqlWhere = new StringBuilder();
        if(StringUtils.isNotBlank(snChannels)){
            //if(snChannelList.size()!=0){ // 始终有值size()=1
            sqlWhere.append("and sn_channel in (:snChannels) ");
            params.put("snChannels", snChannelList);
        }
        if(StringUtils.isNotBlank(snVersion)){
            sqlWhere.append("and sn_version = :snVersion ");
            params.put("snVersion", snVersion);
        }
        if(StringUtils.isNotBlank(snYear)){
            sqlWhere.append("and sn_year = :snYear ");
            params.put("snYear", snYear);
        }
        if(fromCount==0 && toCount==0){
            // sqlWhere.append("and count!=0"); // !=号在mysql5.6中不走索引，8.0才开始走索引
            sqlWhere.append("and count>0 ");
        }else {
            if (fromCount != 0) {
                sqlWhere.append("and count >= :fromCount ");
                params.put("fromCount", fromCount);
            }
            if (toCount != 0) {
                sqlWhere.append("and count < :toCount ");
                params.put("toCount", toCount);
            }
        }

        /**
         * 条件尽量不放在Having里面，要放在where中，减少聚合数据量
         */
        //StringBuilder sqlHaving = new StringBuilder();
        //if(fromCount!=0){
        //    sqlHaving.append("and active_count >= :fromCount ");
        //    params.put("fromCount", fromCount);
        //}
        //if(toCount!=0){
        //    sqlHaving.append("and active_count < :toCount ");
        //    params.put("toCount", toCount);
        //}

        StringBuilder sql = new StringBuilder();
        sql.append("select " +
                "SQL_CALC_FOUND_ROWS " + // 供查询总数使用（如果不加这个，后面的select found_rows()返回的数量为limit后的数量，而不是总数。注意使用空格隔开没有逗号）
                "sn as name, max(count) as active_count from sn_state_changed " +
                "where 1=1 ");
        sql.append("and sn not like 'FF%' and sn not like 'ZZ%' "); // 排除无效数据
        sql.append(sqlWhere);
        sql.append( "group by sn ");
        // sql.append( "having active_count!=0 and sn not like 'FF%' and sn not like 'ZZ%' ");
        // sql.append(sqlHaving);
        sql.append("order by active_count desc");
        Query nativeQuery = em.createNativeQuery(sql.toString(), SnStateChangedVO.class);
        for (String key : params.keySet()) {
            nativeQuery.setParameter(key, params.get(key));
        }
        nativeQuery.setFirstResult((page-1)*size);
        nativeQuery.setMaxResults(size);
        List<SnStateChangedVO> resultList = nativeQuery.getResultList();

        StringBuilder sqlTotalCount = new StringBuilder();
        sqlTotalCount.append("select FOUND_ROWS() as total;"); // 查询总数(注意务必放在同一个session中执行（这里的@PersistenceContext entityManage每次载入就是一个session，所以当前这一个方法中是同一个session）)
        Query nativeQuery2 = em.createNativeQuery(sqlTotalCount.toString());
        Long total = ((BigInteger)nativeQuery2.getSingleResult()).longValue();

        return PageResultVO.<SnStateChangedVO>builder().list(resultList).total(total).build();
    }
}

 其中包含两个实体类PageResultVO和SnStateChangedVO：

import lombok.Builder;
import lombok.Data;

import java.util.List;

@Builder
@Data
public class PageResultVO<T> {
    private List<T> list;
    private Long total;
}

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
@Data
public class SnStateChangedVO implements Comparable<SnStateChangedVO>{

  @Id
  private String name;

  private Integer activeCount;

  @Override
  public int compareTo(SnStateChangedVO o) {
    // 升序（由小到大）
    return this.getName().compareTo(o.getName());
  }
}

PS额外小知识：

查询“条件”尽量不要放在Having里面，要放在where中，目的是减少聚合数据量。