本文介绍如何使用自定义类实现类似Mybatis的分页插件，通过添加过滤条件，处理复杂SQL的改写，并利用策略模式处理不同数据库的SQL调整。涉及Condition类、DbFilterHelper、DbFilterInterceptor和SqlUtil等关键组件。
原理：
原理类似mybatis分页插件 在使用时添加过滤条件即可 整个难点是改写原本sql 因为可能这个sql很复杂 我写的改写算法可能有bug
步骤
1 编写存放我们过滤条件的类
package com.yinhai.domain.common.plug;
import com.github.pagehelper.util.MetaObjectUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import java.util.*;
/**
* @author
* @discription;
* @time 2020/10/9 10:13
*/
@Slf4j
public class Condition {
private Map<String,Object> filterData=new HashMap<>();
private UnionTableModel unionTable=null;
private SqlResove sqlResove=new DefaultSqlResove();
public Object processParameterObject(MappedStatement ms, Object parameterObject, BoundSql boundSql, CacheKey pageKey) {
Map<String, Object> paramMap = null;
if (parameterObject == null) {
paramMap = new HashMap();
} else if (parameterObject instanceof Map) {
paramMap = new HashMap();
paramMap.putAll((Map)parameterObject);
} else {
paramMap = new HashMap();
boolean hasTypeHandler = ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass());
MetaObject metaObject = MetaObjectUtil.forObject(parameterObject);
if (!hasTypeHandler) {
String[] var9 = metaObject.getGetterNames();
int var10 = var9.length;
for(int var11 = 0; var11 < var10; ++var11) {
String name = var9[var11];
paramMap.put(name, metaObject.getValue(name));
}
}
if (boundSql.getParameterMappings() != null && boundSql.getParameterMappings().size() > 0) {
Iterator var13 = boundSql.getParameterMappings().iterator();
ParameterMapping parameterMapping;
String name;
do {
do {
do {
do {
if (!var13.hasNext()) {
return getParam(ms,boundSql,paramMap);
}
parameterMapping = (ParameterMapping)var13.next();
name = parameterMapping.getProperty();
} while(false);
} while(false);
} while(paramMap.get(name) != null);
} while(!hasTypeHandler && !parameterMapping.getJavaType().equals(parameterObject.getClass()));
paramMap.put(name, parameterObject);
}
}
return getParam(ms,boundSql,paramMap);
}
public Object getParam(MappedStatement ms,BoundSql boundSql,Map paramMap){
paramMap.putAll(filterData);
List<ParameterMapping> newParameterMappings = new ArrayList(boundSql.getParameterMappings());
if (boundSql.getParameterMappings() != null) {
filterData.forEach((k, v) -> {
String newK=k;
if(!k.contains("_")){
//驼峰转换
StringBuilder stringBuilder=new StringBuilder();
char[] chars = k.toCharArray();
for (int i = 0; i < chars.length; i++) {
char c=chars[i];
if(c>=65&&c<=89){
stringBuilder.append("_");
stringBuilder.append(c+32);
}else {
stringBuilder.append(c);
}
}
newK=stringBuilder.toString();
}
newParameterMappings.add((new ParameterMapping.Builder(ms.getConfiguration(), newK, v!=null?v.getClass():null)).build());
});
}
MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
metaObject.setValue("parameterMappings", newParameterMappings);
return paramMap;
}
public void addQueryFilter(String column,Object data){
filterData.put(column,data);
}
public void addUnionTable(String unionExpression,String unionTableName,String unionColumn){
log.error("对于自动联表操作 暂时未支持 请在xml语句中自行添加");
unionTable=new UnionTableModel();
unionTable.setColumn(unionColumn);
unionTable.setTableName(unionTableName);
unionTable.setUnionExpression(unionExpression);
}
public boolean isNeedUnionTable(){
return unionTable!=null;
}
public Map<String, Object> getFilterData(){
return filterData;
}
public UnionTableModel getUnionTable() {
return unionTable;
}
public SqlResove getSqlResove() {
return sqlResove;
}
public void setSqlResove(SqlResove sqlResove) {
this.sqlResove = sqlResove;
}
public String filterSql(BoundSql boundSql, Object parameter) {
return sqlResove.filterSql(this,boundSql,parameter);
}
public static class UnionTableModel{
private String unionExpression;
private String tableName;
private String column;
public String getUnionExpression() {
return unionExpression;
}
public void setUnionExpression(String unionExpression) {
this.unionExpression = unionExpression;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getColumn() {
return column;
}
public void setColumn(String column) {
this.column = column;
}
}
}
2 编写 DbFilterHelper 功能类似PageHelper 使用时以这个类为入口
public class DbFilterHelper {
private static ThreadLocal<Condition> CONDITON=new ThreadLocal<>();
public static void startFilter(Condition con){
CONDITON.set(con);
}
public static void clearCondition(){
CONDITON.remove();
}
public static Condition getCondition(){
return CONDITON.get();
}
}
3 编写mybatis 拦截器 拦截我们的sql查询语句 看看是否需要添加过滤条件
@Intercepts({@Signature(
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
), @Signature(
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}
)})
@Slf4j
public class DbFilterInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Condition condition=null;
if((condition= DbFilterHelper.getCondition())!=null){
try {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object parameter = args[1];
RowBounds rowBounds = (RowBounds) args[2];
ResultHandler resultHandler = (ResultHandler) args[3];
Executor executor = (Executor) invocation.getTarget();
CacheKey cacheKey;
BoundSql boundSql;
if (args.length == 4) {
boundSql = ms.getBoundSql(parameter);
cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
} else {
cacheKey = (CacheKey) args[4];
boundSql = (BoundSql) args[5];
}
List<Object> objects = DbFilterExecutorUtil.filterQuery(condition, executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
return objects;
}finally {
DbFilterHelper.clearCondition();
}
}else {
return invocation.proceed();
}
}
@Override
public Object plugin(Object o) {
return Plugin.wrap(o, this);
}
@Override
public void setProperties(Properties properties) {
log.info("sql 过滤器插件启动成功");
}
}
4 编写 DbFilterExecutorUtil 这是一个工具类 它可以帮我们新增一个BoundSql 我们就用这个工具类获得改写的sql 并生成新的BoundSql 传给mybatis执行器 获得执行结果
public class DbFilterExecutorUtil {
private static Field additionalParametersField;
public static MappedStatement getExistedMappedStatement(Configuration configuration, String msId) {
MappedStatement mappedStatement = null;
try {
mappedStatement = configuration.getMappedStatement(msId, false);
} catch (Throwable var4) {
}
return mappedStatement;
}
public static <E> List<E> filterQuery(Condition condition,Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql, CacheKey cacheKey) throws SQLException {
parameter=condition.processParameterObject(ms, parameter, boundSql, cacheKey);
String filterSqlStr=condition.filterSql(boundSql,parameter);
BoundSql filterSql = new BoundSql(ms.getConfiguration(), filterSqlStr, boundSql.getParameterMappings(), parameter);
Map<String, Object> additionalParameters = getAdditionalParameter(boundSql);
Iterator var12 = additionalParameters.keySet().iterator();
while(var12.hasNext()) {
String key = (String)var12.next();
filterSql.setAdditionalParameter(key, additionalParameters.get(key));
}
return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, filterSql);
}
public static Map<String, Object> getAdditionalParameter(BoundSql boundSql) {
try {
return (Map)additionalParametersField.get(boundSql);
} catch (IllegalAccessException var2) {
throw new PageException("获取 BoundSql 属性值 additionalParameters 失败: " + var2, var2);
}
}
static {
try {
additionalParametersField = BoundSql.class.getDeclaredField("additionalParameters");
additionalParametersField.setAccessible(true);
} catch (NoSuchFieldException var1) {
throw new PageException("获取 BoundSql 属性 additionalParameters 失败: " + var1, var1);
}
}
}
5 根据原始sql 和新增的过滤条件 改写sql 这里我们用下策略模式 因为可能 换个数据库就不一样了 我这里只实现了mysql的
接口类
public interface SqlResove {
String filterSql(Condition condition, BoundSql boundSql, Object parameter);
}
实现类
@Slf4j
public class DefaultSqlResove implements SqlResove {
@Override
public String filterSql(Condition condition, BoundSql boundSql, Object parameter) {
String sourceSql = boundSql.getSql();
StringBuilder stringBuilder=new StringBuilder(sourceSql);
if(condition.isNeedUnionTable()){
log.error("对于联表操作 暂时未支持 请在xml语句中自行添加");
}else {//不需要连表操作 直接加条件即可
String convert = SqlUtil.convert(sourceSql, condition.getFilterData());
return convert;
}
return sourceSql;
}
}
6 sql工具类 根据原始类 和条件map 改写原始sql 添加过滤器 比如原先为 where a=？ 改写后 为 where a=? and b=? (map里面为（b,value）)
public class SqlUtil {
public static String convert(String s, Map<String,Object> map){
String s1 = s.replaceAll("\\s{2,}", " ");
Set<String> strings = map.keySet();
StringBuilder stringBuilder=new StringBuilder(s1);
int order_by = containStr(stringBuilder, "order by");
int group_by = containStr(stringBuilder, "group by");
int limit = containStr(stringBuilder, "limit");
boolean o = order_by != -1;
boolean g = group_by != -1;
boolean l = limit != -1;
int start = g ? group_by : o ? order_by : l ? limit : -1;
boolean first = true;
boolean isAppend=start==-1;
//包含where
if(containStr(stringBuilder,"where")!=-1){
String addStr="";
int len=0;
for (String k : strings) {
addStr=format("and " + k + "=?");
if(isAppend){
stringBuilder.append(format(addStr));
}else {
stringBuilder.insert(start, addStr);
}
}
len=addStr.length();
}else {//不含where
String addStr="";
int len=0;
for (String k : strings) {
if (first) {
addStr=format("where " + k + "=?");
if(isAppend){
stringBuilder.append(format(addStr));
}else {
stringBuilder.insert(start-len, addStr);
}
} else {
addStr=format("and " + k + "=?");
if(isAppend){
stringBuilder.append(addStr);
}else {
stringBuilder.insert(start+len, addStr);
}
}
if (first) {
first = false;
}
len=addStr.length();
}
}
return stringBuilder.toString();
}
private static int containStr(StringBuilder s,String ch){
int i = s.indexOf(ch);
if(i==-1){
i=s.indexOf(ch.toUpperCase());
}
return i;
}
public static String format(String s){
return " "+s+" ";
}
}
7 添加进mybatis sqlSessionFactory (视情况而定 我这里是springboot环境)
Configuration
public class DbFilterAutoConfig {
@Autowired
private List<SqlSessionFactory> sqlSessionFactoryList;
@PostConstruct
public void addPageInterceptor() {
DbFilterInterceptor interceptor = new DbFilterInterceptor();
Iterator var3 = this.sqlSessionFactoryList.iterator();
while(var3.hasNext()) {
SqlSessionFactory sqlSessionFactory = (SqlSessionFactory)var3.next();
sqlSessionFactory.getConfiguration().addInterceptor(interceptor);
}
}
}
使用过程：
在我们的业务类添加 红色代码段：
@Override
public Optional<Page<XtTbEduPlanPo>> queryCoursePlanAllByCondition(CoursePlanDTO coursePlanDTO) {
Condition condition=new Condition();
condition.addQueryFilter("way","1");
DbFilterHelper.startFilter(condition);
Page<XtTbEduPlanPo> xtTbEduPlanPoPage = cousePlanReadService.queryCondition(courseConverterUtil.byCoursePlanDTO(coursePlanDTO).orElse(null));
return Optional.ofNullable(xtTbEduPlanPoPage);
}
扫一扫
2616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
