mybatis-plus 基于分页插件[PaginationInterceptor] 自定义[分页查询 总数查询]Sql位置
package xxx.xxx.xxx.xxx;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.core.parser.SqlInfo;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectFactory;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.toolkit.JdbcUtils;
import com.baomidou.mybatisplus.extension.toolkit.SqlParserUtils;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.springframework.util.Assert;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 分页拦截器
*
* @author hubin
* @since 2016-01-23
*/
@Setter
@Accessors(chain = true)
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class CustomPaginationInterceptor extends PaginationInterceptor implements Interceptor {
protected static final Log logger = LogFactory.getLog(CustomPaginationInterceptor.class);
/**
* COUNT SQL 解析
*/
private ISqlParser countSqlParser;
/**
* 溢出总页数，设置第一页
*/
private boolean overflow = false;
/**
* 单页限制 500 条，小于 0 如 -1 不受限制
*/
private long limit = 500L;
/**
* 方言类型
*/
private String dialectType;
/**
* 方言实现类<br>
* 注意！实现 接口的子类
*/
private String dialectClazz;
/**
* Physical Page Interceptor for all the queries with parameter {@link RowBounds}
*/
@SuppressWarnings("unchecked")
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
// SQL 解析
this.sqlParser(metaObject);
// 先判断是不是SELECT操作 (2019-04-10 00:37:31 跳过存储过程)
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("");
if (SqlCommandType.SELECT != mappedStatement.getSqlCommandType()
|| StatementType.CALLABLE == mappedStatement.getStatementType()) {
return invocation.proceed();
}
// 针对定义了rowBounds，做为mapper接口方法的参数
BoundSql boundSql = (BoundSql) metaObject.getValue("");
Object paramObj = boundSql.getParameterObject();
// 判断参数里是否有page对象
IPage<?> page = null;
if (paramObj instanceof IPage) {
page = (IPage<?>) paramObj;
} else if (paramObj instanceof Map) {
for (Object arg : ((Map<?, ?>) paramObj).values()) {
if (arg instanceof IPage) {
page = (IPage<?>) arg;
break;
}
}
}
/*
* 不需要分页的场合，如果 size 小于 0 返回结果集
*/
if (null == page || page.getSize() < 0) {
String sql = boundSql.getSql();
boolean isReplaceSql=false;
if(sql.contains(CustomPaginationPosition.startIdentify)){
sql=sql.replaceAll(CustomPaginationPosition.startIdentifyPattern, "");
isReplaceSql=true;
}
if(sql.contains(CustomPaginationPosition.endIdentify)){
sql=sql.replaceAll(CustomPaginationPosition.endIdentifyPattern, "");
isReplaceSql=true;
}
if (isReplaceSql) {
metaObject.setValue("", sql);
}
return invocation.proceed();
}
/*
* 处理单页条数限制
*/
if (limit > 0 && limit <= page.getSize()) {
page.setSize(limit);
}
Connection connection = (Connection) invocation.getArgs()[0];
DbType dbType = StringUtils.isNotEmpty(dialectType) ? DbType.getDbType(dialectType)
: JdbcUtils.getDbType(connection.getMetaData().getURL());
Configuration configuration = mappedStatement.getConfiguration();
CustomPaginationPosition customPaginationPosition = new CustomPaginationPosition(configuration,boundSql, page, dbType);
String pageSql = customPaginationPosition.getPageSql();
if (page.isSearchCount()) {
SqlInfo sqlInfo = SqlParserUtils.getOptimizeCountSql(page.optimizeCountSql(), countSqlParser, pageSql);
this.queryTotal(overflow, sqlInfo.getSql(), mappedStatement, customPaginationPosition.getPageBoundSql(), page, connection);
if (page.getTotal() <= 0) {
return null;
}
}
DialectModel model = customPaginationPosition.parseDialectModel();
List<ParameterMapping> mappings = new ArrayList<>(boundSql.getParameterMappings());
Map<String, Object> additionalParameters = (Map<String, Object>) metaObject.getValue("");
model.consumers(mappings, configuration, additionalParameters);
metaObject.setValue("", customPaginationPosition.getPaginationSql());
metaObject.setValue("", mappings);
return invocation.proceed();
}
private class CustomPaginationPosition {
private static final String startIdentifyPattern = "\\^\\^\\{CustomPaginationPositionStartIdentify\\}\\^\\^";
private static final String startIdentify = "^^{CustomPaginationPositionStartIdentify}^^";
private static final String endIdentifyPattern = "\\^\\^\\{CustomPaginationPositionEndIdentify\\}\\^\\^";
private static final String endIdentify = "^^{CustomPaginationPositionEndIdentify}^^";
/**
* 原始boundSql
*/
private BoundSql boundSql;
/**
* 构造PageBoundSql 需要
*/
private Configuration configuration;
/**
* 截取自定义分页开始位置 之前的sql
*/
private String startSql;
/**
* 之前sql中有多少个 '?' 参数
*/
private int startSqlParameterNum;
/**
* 自定义的 分页sql
*/
@Getter
private String pageSql;
/**
* 分页sql中参数个数
*/
private int pageSqlParameterNum;
/**
* 同理 同上
*/
private String endSql;
/**
* 同理 同上
*/
private int endSqlParameterNum;
/**
* 构造分页sql对象 借助原始boundSql
* 1.分页时
* 2.不分页时
*/
@Getter
private BoundSql pageBoundSql;
/**
* 分页参数
*/
private IPage<?> page;
/**
* sql方言类型
*/
private DbType dbType;
@Getter
private DialectModel model;
/**
* 是否为分页sql true 分页 false 不分页
*/
@Getter
private boolean isPaging;
public CustomPaginationPosition(Configuration configuration,BoundSql boundSql, IPage<?> page, DbType dbType) {
Assert.notNull(boundSql, "boundSql not null");
Assert.notNull(boundSql, "configuration not null");
this.boundSql = boundSql;
this.configuration = configuration;
String originalSql = boundSql.getSql();
Assert.isTrue(org.apache.commons.lang3.StringUtils.isNotBlank(originalSql), "sql not null");
int startIdentifyIndex = originalSql.indexOf(startIdentify);
int endIdentifyIndex = originalSql.indexOf(endIdentify);
Assert.isTrue((startIdentifyIndex == -1 && endIdentifyIndex == -1)
|| (startIdentifyIndex != -1 && endIdentifyIndex != -1), String.format("自定义分页标识必须成对出现[(%s) - (%s)]", startIdentify, endIdentify));
if (startIdentifyIndex != -1 && endIdentifyIndex != -1) {
Assert.isTrue(startIdentifyIndex < endIdentifyIndex, String.format("[%s]标签必须在[%s]标签前面", startIdentify, endIdentify));
this.startSql = originalSql.substring(0, startIdentifyIndex);
this.pageSql = originalSql.substring(startIdentifyIndex + startIdentify.length(), endIdentifyIndex);
this.endSql = originalSql.substring(endIdentifyIndex + endIdentify.length());
} else {
this.pageSql = originalSql;
}
if (org.apache.commons.lang3.StringUtils.isBlank(this.startSql)) {
this.startSql = "";
}
if (org.apache.commons.lang3.StringUtils.isBlank(this.endSql)) {
this.endSql = "";
}
//是否分页
this.isPaging=!(null == page || page.getSize() < 0);
if (this.isPaging) {
this.startSqlParameterNum = org.apache.commons.lang3.StringUtils.countMatches(this.startSql, "?");
this.pageSqlParameterNum = org.apache.commons.lang3.StringUtils.countMatches(this.pageSql, "?");
this.endSqlParameterNum = org.apache.commons.lang3.StringUtils.countMatches(this.endSql, "?");
this.pageBoundSql=new BoundSql(this.configuration,this.pageSql,this.boundSql.getParameterMappings(),this.boundSql.getParameterObject()){
BoundSql boundSql=CustomPaginationPosition.this.boundSql;
@Override
public String getSql() {
return boundSql.getSql();
}
@Override
public List<ParameterMapping> getParameterMappings() {
return boundSql.getParameterMappings().subList(CustomPaginationPosition.this.startSqlParameterNum
,CustomPaginationPosition.this.startSqlParameterNum+CustomPaginationPosition.this.pageSqlParameterNum);
}
@Override
public Object getParameterObject() {
return boundSql.getParameterObject();
}
@Override
public boolean hasAdditionalParameter(String name) {
return boundSql.hasAdditionalParameter(name);
}
@Override
public void setAdditionalParameter(String name, Object value) {
boundSql.setAdditionalParameter(name, value);
}
@Override
public Object getAdditionalParameter(String name) {
return boundSql.getAdditionalParameter(name);
}
};
}else{
this.pageBoundSql=new BoundSql(this.configuration,this.startSql+this.pageSql+this.endSql,this.boundSql.getParameterMappings(),this.boundSql.getParameterObject()){
BoundSql boundSql=CustomPaginationPosition.this.boundSql;
@Override
public List<ParameterMapping> getParameterMappings() {
return boundSql.getParameterMappings();
}
@Override
public Object getParameterObject() {
return boundSql.getParameterObject();
}
@Override
public boolean hasAdditionalParameter(String name) {
return boundSql.hasAdditionalParameter(name);
}
@Override
public void setAdditionalParameter(String name, Object value) {
boundSql.setAdditionalParameter(name, value);
}
@Override
public Object getAdditionalParameter(String name) {
return boundSql.getAdditionalParameter(name);
}
};
}
this.page = page;
this.dbType = dbType;
}
public String getPaginationSql() {
return this.startSql + model.getDialectSql() + this.endSql;
}
public DialectModel parseDialectModel(){
this.pageSql=concatOrderBy(this.pageSql, page);
this.model = DialectFactory.buildPaginationSql(page, this.pageSql, dbType, dialectClazz);
return this.model;
}
}
}