这几天被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()
）
-
如果不加这个，后面的
select found_rows()
返回的数量为limit后的数量，而不是总数。
-
注意使用空格隔开，没有逗号
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>{
private String name;
private Integer activeCount;
@Override
public int compareTo(SnStateChangedVO o) {
// 升序（由小到大）
return this.getName().compareTo(o.getName());
PS额外小知识：
查询“条件”尽量不要放在Having里面，要放在where中，目的是减少聚合数据量。
我一般都会使用
NativeSearch
QueryBuilder查询es，但是这种查询返回的结果只有10000条，加上track_total_hits = true即可
java代码如下:
NativeQueryBuilder
query = new
NativeSearch
QueryBuilder()
query.setTrackTotalHits(true)
之前博客介绍郭试用JPA如何实现分页功能 但是 每次都要写sql语句去计算总条数countQuery 今天学习JPA文档的时候知道了Slice用法 于是进行了优化
之前文章连接
public interface XDMReviewTimeDao extends JpaRepository<XDMReviewTimePoJo, Long> {
@Quer...
HQL 大小写不敏感，当有相同的实体类名时，使用包名.实体类
Query query=session.create
Query("from Cat c");
List catlist=
query.list();
返回单个对象
Query q=session.create
Query("select count(c) from Cat c");
Number num=(Num
SpringDataJpa 动态复杂查询NativeQuery
emm挺长时间没更新了，最近在忙项目，外加搬家,后续会继续更新
遇到一个复杂的sql查询，在不通过外键关联的情况下，只能选择使用原生sql的方式查询，但是遇到一个极其XX的问题
首先去了Spring官方和一些博客调查了一下，感觉Jpa还是偏冷门讨论的不多，一搜索好多还是15年的信息…
当前SpringBoot版本信息
<ver...
原创：全局异常捕获BindException、MethodArgumentNotValidException和ConstraintViolationException @Validated@Valid
OKWeCool:
SSL中，公钥、私钥、证书(pem、crt、cer、key、csr)的后缀名都是些啥？
weixin_42052209:
IntelliJ IDEA更新Maven远程仓库索引index（pom文件终于有快速的自动提示了）
脸滚键盘1201:
