Mybatis 自定义自动分页
1.JDBCToMyBatisUtils.java
package com.util.query;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
/**
* 帮助类
* @author callens
*/
@Component
public class JDBCToMyBatisUtils {
private static final Pattern humpPattern = Pattern.compile("[A-Z]");// 匹配字符
/**
* 组装查询sql
* @param queryInfo
* @param sql
* @return
*/
public String querySql(MapperQueryInfo queryInfo, String sql) {
StringBuffer newSql = new StringBuffer(sql);
assembleSql(queryInfo, newSql);
String finalSql = "";
if (queryInfo != null && queryInfo.isNeedDoPage()) {
int startRows = queryInfo.getStart();
int limit = queryInfo.getLimit();
finalSql = createSqlQuery(sql.toString(), startRows, limit);
}
return finalSql;
}
/**
* 组装sql
* @param queryInfo
* @param sql
*/
private void assembleSql(MapperQueryInfo queryInfo, StringBuffer sql) {
// 组装参数脚本，并加载参数集合
sql.append(createFilterSql(queryInfo, false));
// 组装排序脚本
String sortSql = createSortSql(queryInfo);
if (StringUtils.isNotBlank(sortSql)) {
sql.append(" ORDER BY ").append(sortSql);
}
}
/**
* 接收从前端传入的排序sql
* @param queryInfo
* @return
*/
private String createSortSql(final MapperQueryInfo queryInfo) {
if (queryInfo == null) {
return "";
}
List<SortInfo> sortInfos = queryInfo.getSortInfos();
StringBuffer sqlSB = new StringBuffer(1024);
if (sortInfos == null || sortInfos.size() == 0) {
return "";
}
int i = 0;
for (SortInfo sortInfo : sortInfos) {
if (StringUtils.isNotBlank(sortInfo.getColumnName())) {
sqlSB.append(i++ > 0 ? "," : "").append(sortInfo.getColumnName());
sqlSB.append(sortInfo.isDesc() ? " DESC" : " ASC");
}
}
if (sqlSB.length() == 0) {
return "";
}
return sqlSB.toString();
}
/**
* 创建SQL分页查询语句
* @param sql
* @param start 起始行
* @param limit 查询的限制数量
* @return 查询sql
*/
private String createSqlQuery(String sql, int start, int limit) {
StringBuffer sqlSB = new StringBuffer(2048);
if (start > 0) {
sqlSB.append(" SELECT * FROM (");
sqlSB.append(" SELECT ROWNUM AS ROW_, T.* FROM (");
sqlSB.append(" SELECT ");
sqlSB.append(sql.trim().substring(7));
sqlSB.append(") T WHERE ROWNUM <= ");
sqlSB.append(start + limit);
sqlSB.append(" ) ");
sqlSB.append(" WHERE ROW_ > ");
sqlSB.append(start);
sqlSB.append(" \n");
} else if (limit > 0) {
sqlSB.append(" SELECT T.* FROM (");
sqlSB.append(" SELECT ");
sqlSB.append(sql.trim().substring(7));
sqlSB.append(") T WHERE ROWNUM <= ");
sqlSB.append(limit);
sqlSB.append(" \n");
}
return sqlSB.toString();
}
/**
* 方法说明：根据列表查询信息中过滤信息创建过滤脚本
* @param queryInfo 列表查询信息
* @param isFirstFilter 是否作为第一个过滤条件，决定在参数语句中为WHERE前缀，还是AND前缀
* @return 过滤条件sql
*/
private String createFilterSql(final MapperQueryInfo queryInfo, boolean isFirstFilter) {
if (queryInfo == null) {
return "";
}
List<FilterInfo> filterInfos = queryInfo.getFilterInfos();
if (filterInfos == null || filterInfos.size() == 0) {
return "";
}
StringBuffer sqlSB = new StringBuffer(1024);
for (FilterInfo filterInfo : filterInfos) {// 当前传入的字段为VO对象字段,需要转换为下划线的数据库字段
sqlSB.append(isFirstFilter ? " WHERE " : " AND ").append(humpToLine(filterInfo.getFieldName()));
Set<Object> values = filterInfo.getValues();
if (values.size() > 0) {
sqlSB.append(" IN (");
int i = 0;
for (Object value : filterInfo.getValues()) {
sqlSB.append(i > 0 ? ",'" : "'");
sqlSB.append(value);
sqlSB.append("'");
}
sqlSB.append(") ");
}
}
return sqlSB.toString();
}
/**
* 驼峰转下划线
* @param value
* @return
*/
private String humpToLine(String value) {
Matcher matcher = humpPattern.matcher(value);
StringBuffer sb = new StringBuffer();
while (matcher.find()) {
matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
}
matcher.appendTail(sb);
return sb.toString();
}
/**
* 组装查询总数的sql语句
* @param queryInfo
* @param sql
* @return
*/
public String querySqlByCount(MapperQueryInfo queryInfo,String sql) {
StringBuffer sb = new StringBuffer(2048);
sb.append(" SELECT COUNT(1) AS CNT FROM ( ");
sb.append(sql);
assembleSql(queryInfo, sb);
sb.append(" ) ");
String finalSql=sb.toString();
return finalSql;
}
}
2.在Mybatis的全局配置文件中增加plugin
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<plugins>
<plugin interceptor="com.interceptors.mybatis.InterceptorStatementHandlerByPage"></plugin>
<plugin interceptor="com.interceptors.mybatis.InterceptorResultSetHandlerByPage"></plugin>
</plugins>
</configuration>
3.实现Statement拦截器
package com.interceptors.mybatis;
import java.sql.Connection;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import com.util.query.JDBCToMyBatisUtils;
/**
* 拦截方法为拦截statement之前进行操作
* @Intercepts 说明是一个拦截器
* @Signature 拦截器的签名
* type 拦截的类型 四大对象之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler)
* method 拦截的方法
* args 参数,高版本需要加个Integer.class参数,不然会报错
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class,Integer.class})})
public class InterceptorStatementHandlerByPage implements Interceptor{
private JDBCToMyBatisUtils utils=Context.getSpringContext().getBean(JDBCToMyBatisUtils.class);
@Override
public Object intercept(Invocation invocation) throws Throwable {
//获取StatementHandler，默认是RoutingStatementHandler
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
//获取statementHandler包装类
MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);
//分离代理对象链
while (MetaObjectHandler.hasGetter("h")) {
Object obj = MetaObjectHandler.getValue("h");
MetaObjectHandler = SystemMetaObject.forObject(obj);
}
while (MetaObjectHandler.hasGetter("target")) {
Object obj = MetaObjectHandler.getValue("target");
MetaObjectHandler = SystemMetaObject.forObject(obj);
}
//获取查询接口映射的相关信息
MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");
String mapId = mappedStatement.getId();
//拦截以.ByPage结尾的请求，分页功能的统一实现
if (mapId.matches(".+ByPage$") || mapId.matches(".+Detail")) {
//获取进行数据库操作时管理参数的handler
ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler");
//获取请求时的参数
@SuppressWarnings("unchecked")
Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
//获取传入的参数
MapperQueryInfo mapperQueryInfo=(MapperQueryInfo)paraObject.get("mapperQueryInfo");//获取分页信息参数
String sql = (String) MetaObjectHandler.getValue("delegate.boundSql.sql");
mapperQueryInfo.setNativeSql(sql);
mapperQueryInfo.setFilterByPage(true);
String querysql=utils.querySql(mapperQueryInfo, sql);
// log.info("sql by page -->{}",querysql);//打印分页sql
//将构建完成的分页sql语句赋值个体'delegate.boundSql.sql'，偷天换日
MetaObjectHandler.setValue("delegate.boundSql.sql", querysql);
}
//调用原对象的方法，进入责任链的下一级
return invocation.proceed();
}
//获取代理对象
@Override
public Object plugin(Object o) {
//生成object对象的动态代理对象
return Plugin.wrap(o, this);
}
//设置代理对象的参数
@Override
public void setProperties(Properties properties) {
}
}
4.实现ResultSet拦截器
package com.interceptors.mybatis;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.resultset.DefaultResultSetHandler;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import com.util.query.JDBCToMyBatisUtils;
/**
* 拦截方法为拦截statement之前进行操作
* @Intercepts 说明是一个拦截器
* @Signature 拦截器的签名 type 拦截的类型 四大对象之一(Executor,ResultSetHandler,ParameterHandler,StatementHandler) method 拦截的方法 args 参数,高版本需要加个Integer.class参数,不然会报错
*/
@Intercepts({ @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = { Statement.class }) })
public class InterceptorResultSetHandlerByPage implements Interceptor {
private JDBCToMyBatisUtils utils=Context.getSpringContext().getBean(JDBCToMyBatisUtils.class);
@SuppressWarnings("unchecked")
@Override
public Object intercept(Invocation invocation) throws Throwable {
//获取默认的resultSetHandler对象
DefaultResultSetHandler resultSetHandler = (DefaultResultSetHandler) invocation.getTarget();
//获取其代理对象
MetaObject metaResultSetHandler = SystemMetaObject.forObject(resultSetHandler);
try {
//使用其代理对象获取传入的参数对象
ParameterHandler parameterHandler = (ParameterHandler) metaResultSetHandler.getValue("parameterHandler");
Map<String, Object> paraObject=null;
try {
paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
}catch(Exception e) {
}
if(paraObject!=null) {
//获取参数
MapperQueryInfo mapperQueryInfo=paraObject.getOrDefault("mapperQueryInfo",null)==null?null:(MapperQueryInfo)paraObject.get("mapperQueryInfo");
if(mapperQueryInfo!=null&&mapperQueryInfo.isFilterByPage()) {//判断参数结果不为空，并且为分页sql,则进行其他处理
List<?> list=(List<?>) invocation.proceed();//提前获取结果集
if(list.size()!=0) {//获取count信息
String sql=mapperQueryInfo.getNativeSql();//获取其原始sql
//获取查询总记录数的sql
sql=utils.querySqlByCount(mapperQueryInfo, sql);
//获取执行器的connection,获取结果集之后，暂时执行器不会被关掉
Connection connection = (Connection) metaResultSetHandler.getValue("executor.delegate.transaction.connection");
//获取得到count
int count=getTotalRecord(connection, sql, parameterHandler);
mapperQueryInfo.setCount(count);//保存总记录数
}
return list;
}
}
} catch (Exception e) {
throw new Exception("Overwrite SQL : Fail!");
}
return invocation.proceed();
}
/**
* 执行 count 操作
* @param connection 数据库连接
* @param sql sql
* @param parameterHandler 参数设置处理器
* @return
*/
private int getTotalRecord(Connection connection, String sql, ParameterHandler parameterHandler) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
parameterHandler.setParameters(preparedStatement);
resultSet = preparedStatement.executeQuery();
resultSet.next();
int count=resultSet.getInt(1);
return count;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(resultSet!=null) {
resultSet.close();
}
if(preparedStatement!=null) {
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
5.如何使用
在使用的时候，mapper接口需要以ByPage或者 Detail结尾，传入的参数为MapperQueryInfo 里面需要传入分页信息
package com.query;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
/**
* 查询信息类，用于表示查询总记录及分页信息. 系统中大部分的查询方法都需要通过此类来限定查询数据量，以防止潜在的内存问题。
*/
@ToString
public class MapperQueryInfo {
@Getter @Setter private String nativeSql;//原生的sql
@Getter @Setter private boolean filterByPage=false;//标识是否进行拦截
@Getter @Setter private int count=0;//当前查询sql的总记录数
/**
* 默认限制数量为100
*/
public static final int DEFAULT_LIMIT = 100;
/**
* 查询所要记录条数,负数表示未设置分页/段查询信息. 若该值小于0,表示未设置查询分页信息.
*/
@Getter @Setter private int limit = DEFAULT_LIMIT; // 默认第一页
/**
* 排序信息
*/
@Getter @Setter private List<SortInfo> sortInfos = new ArrayList<>();
/**
* 过滤信息.
*/
@Getter @Setter private List<FilterInfo> filterInfos = new ArrayList<>();
/**
* 是否需要分页
*/
public boolean isNeedPage() {
}
}
如下，可以自动分页查询，获取到分页之后的数据
/**
* 分页查询
* @param queryInfo
* @param vo
* @return
*/
public List<AuditFundsDirectivesByTGDTO> auditFundsDirectivesByTGByPage(@Param("mapperQueryInfo")MapperQueryInfo queryInfo,
@Param("vo")VO vo);
2289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
