前言
ps:最近在參與3100保衛戰,戰況很激烈,剛剛打完仗,來更新一下之前寫了一半的博客。
該篇針對日常寫查詢的時候,那些動態條件sql 做個簡單的封裝,自動生成(拋磚引玉,搞個小玩具,不喜勿噴)。
正文
來看看我們平時寫那些查詢,基本上都要寫的一些動態sql:
?
?
?
一個字段寫一個if ,有沒有人覺得煩的。
每張表的查詢,很多都有這種需求,根據什么查詢,根據什么查詢,不為空就觸發條件。
天天寫天天寫,copy 改,copy改, 有沒有人覺得煩的。
?
可能有看官看到這就會說, 用插件自動生成就好了。
也有看官會說,用mybatis-plus就好了。
確實有道理,但是我就是想整個小玩具。你管我。
開整
本篇實現的封裝小玩具思路:
①制定的規則(比如標記自定義注解 @JcSqlQuery 或是 函數命名帶上JcDynamics)。
② 觸發的查詢符合規則的, 都自動去根據傳參對象,不為空就自動組裝 sql查詢條件。
③ 利用mybatis @Select 注解,把默認表查詢sql寫好,順便進到自定義的mybatis攔截器里面。
④組裝完sql,就執行,完事。
先寫mapper函數 :
?
/*** @Author JCccc* @Description* @Date 2023/12/14 16:56*/
@Mapper
public interface DistrictMapper {@Select("select code,name,parent_code,full_name FROM s_district_info")List<District> queryListJcDynamics(District district);@Select("select code,name,parent_code,full_name FROM s_district_info")District queryOneJcDynamics(District district);}
?
然后是ParamClassInfo.java 這個用于收集需要參與動態sql組裝的類:
?
import lombok.Data;/*** @Author JCccc* @Description* @Date 2021/12/14 16:56*/
@Data
public class ParamClassInfo {private String classType;private Object keyValue;private String keyName;}
然后是一個自定義的mybatis攔截器(這里面寫了一些小函數實現自主組裝,下面有圖解) :
MybatisInterceptor.java
import com.example.dotest.entity.ParamClassInfo;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ReflectionUtils;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;import static java.util.regex.Pattern.*;/*** @Author JCccc* @Description* @Date 2021/12/14 16:56*/
@Component
@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class MybatisInterceptor implements Interceptor {private final static String JC_DYNAMICS = "JcDynamics";@Overridepublic Object intercept(Invocation invocation) throws Throwable {//獲取執行參數Object[] objects = invocation.getArgs();MappedStatement ms = (MappedStatement) objects[0];Object objectParam = objects[1];List<ParamClassInfo> paramClassInfos = convertParamList(objectParam);String queryConditionSqlScene = getQueryConditionSqlScene(paramClassInfos);//解析執行sql的map方法,開始自定義規則匹配邏輯String mapperMethodAllName = ms.getId();int lastIndex = mapperMethodAllName.lastIndexOf(".");String mapperClassStr = mapperMethodAllName.substring(0, lastIndex);String mapperClassMethodStr = mapperMethodAllName.substring((lastIndex + 1));Class<?> mapperClass = Class.forName(mapperClassStr);Method[] methods = mapperClass.getMethods();for (Method method : methods) {if (method.getName().equals(mapperClassMethodStr) && mapperClassMethodStr.contains(JC_DYNAMICS)) {BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]);String originalSql = boundSql.getSql().toLowerCase(Locale.CHINA).replace("[\\t\\n\\r]", " ");//進行自動的 條件拼接String newSql = originalSql + queryConditionSqlScene;BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), newSql,boundSql.getParameterMappings(), boundSql.getParameterObject());MappedStatement newMs = newMappedStatement(ms, new MyBoundSqlSqlSource(newBoundSql));for (ParameterMapping mapping : boundSql.getParameterMappings()) {String prop = mapping.getProperty();if (boundSql.hasAdditionalParameter(prop)) {newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));}}Object[] queryArgs = invocation.getArgs();queryArgs[0] = newMs;System.out.println("打印新SQL語句" + newSql);}}//繼續執行邏輯return invocation.proceed();}private String getQueryConditionSqlScene(List<ParamClassInfo> paramClassInfos) {StringBuilder conditionParamBuilder = new StringBuilder();if (CollectionUtils.isEmpty(paramClassInfos)) {return "";}conditionParamBuilder.append(" WHERE ");int size = paramClassInfos.size();for (int index = 0; index < size; index++) {ParamClassInfo paramClassInfo = paramClassInfos.get(index);String keyName = paramClassInfo.getKeyName();//默認駝峰拆成下劃線 ,比如 userName -》 user_name , name -> name//如果是需要取別名,其實可以加上自定義注解這些,但是本篇例子是輕封裝,思路給到,你們i自己玩String underlineKeyName = camelToUnderline(keyName);conditionParamBuilder.append(underlineKeyName);Object keyValue = paramClassInfo.getKeyValue();String classType = paramClassInfo.getClassType();//其他類型怎么處理 ,可以按照類型區分 ,比如檢測到一組開始時間,Date 拼接 between and等
// if (classType.equals("String")){
// conditionParamBuilder .append("=").append("\'").append(keyValue).append("\'");
// }conditionParamBuilder.append("=").append("\'").append(keyValue).append("\'");if (index != size - 1) {conditionParamBuilder.append(" AND ");}}return conditionParamBuilder.toString();}private static List<ParamClassInfo> convertParamList(Object obj) {List<ParamClassInfo> paramClassList = new ArrayList<>();for (PropertyDescriptor pd : BeanUtils.getPropertyDescriptors(obj.getClass())) {if (!"class".equals(pd.getName())) {if (ReflectionUtils.invokeMethod(pd.getReadMethod(), obj) != null) {ParamClassInfo paramClassInfo = new ParamClassInfo();paramClassInfo.setKeyName(pd.getName());paramClassInfo.setKeyValue(ReflectionUtils.invokeMethod(pd.getReadMethod(), obj));paramClassInfo.setClassType(pd.getPropertyType().getSimpleName());paramClassList.add(paramClassInfo);}}}return paramClassList;}public static String camelToUnderline(String line){if(line==null||"".equals(line)){return "";}line=String.valueOf(line.charAt(0)).toUpperCase().concat(line.substring(1));StringBuffer sb=new StringBuffer();Pattern pattern= compile("[A-Z]([a-z\\d]+)?");Matcher matcher=pattern.matcher(line);while(matcher.find()){String word=matcher.group();sb.append(word.toUpperCase());sb.append(matcher.end()==line.length()?"":"_");}return sb.toString();}@Overridepublic Object plugin(Object o) {//獲取代理權if (o instanceof Executor) {//如果是Executor(執行增刪改查操作),則攔截下來return Plugin.wrap(o, this);} else {return o;}}/*** 定義一個內部輔助類,作用是包裝 SQL*/class MyBoundSqlSqlSource implements SqlSource {private BoundSql boundSql;public MyBoundSqlSqlSource(BoundSql boundSql) {this.boundSql = boundSql;}@Overridepublic BoundSql getBoundSql(Object parameterObject) {return boundSql;}}private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {MappedStatement.Builder builder = newMappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());builder.resource(ms.getResource());builder.fetchSize(ms.getFetchSize());builder.statementType(ms.getStatementType());builder.keyGenerator(ms.getKeyGenerator());if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {builder.keyProperty(ms.getKeyProperties()[0]);}builder.timeout(ms.getTimeout());builder.parameterMap(ms.getParameterMap());builder.resultMaps(ms.getResultMaps());builder.resultSetType(ms.getResultSetType());builder.cache(ms.getCache());builder.flushCacheRequired(ms.isFlushCacheRequired());builder.useCache(ms.isUseCache());return builder.build();}@Overridepublic void setProperties(Properties properties) {//讀取mybatis配置文件中屬性}
代碼簡析:
駝峰轉換下劃線,用于轉出數據庫表的字段 :
通過反射把 sql入參的對象 不為空的屬性名和對應的值,拿出來:
?
組件動態查詢的sql 語句 :
?
寫個簡單測試用例:
@AutowiredDistrictMapper districtMapper;@Testpublic void test() {District query = new District();query.setCode("110000");query.setName("北京市");District district = districtMapper.queryOneJcDynamics(query);System.out.println(district.toString());District listQuery = new District();listQuery.setParentCode("110100");List<District> districts = districtMapper.queryListJcDynamics(listQuery);System.out.println(districts.toString());}
?看下效果,可以看到都自動識別把不為空的字段屬性和值拼接成查詢條件了:
?
?
?
好了,該篇就到這。 拋磚引玉,領悟分步封裝思路最重要,都去搞些小玩具娛樂娛樂吧。