Mybatis 實現動態組裝查詢條件,仿SQL模式
目的:
以前比較習慣使用Hibernate,後來覺得mybatis不能按我想要的自動組裝為SQL查詢條件,所以提供該工具類;
效果圖:
如圖所示,根據條件自動組裝查詢條件,下面來說一下實現方法:
1. ServiceImpl書寫註意項
Page<SysLogin> resultPage = null; try { PageHelper.startPage(pager.getCurrentPage(), pager.getPageSize()); // 判斷是否有分頁 if (ObjectHelper.isNotEmpty(pager.getDirection()) && ObjectHelper.isNotEmpty(pager.getProperties())) { specification.addOrderBy(pager.getProperties(), pager.getDirection()); } // 判斷是否存在邏輯刪除篩選 String sqlStr = specification.sql(); if (sqlStr.indexOf("deleted") == -1) { specification.eq("deleted", "0"); } resultPage = this.sysLoginMapper.page(specification.sql()); } catch (Exception e) { result = Result.newFailure("數據錯誤", "在獲取分頁列表時發生異常。"); log.error(SimpleLogFormater.formatException(result.getMessage(), e)); return result; }
2. Mapper.java 書寫
查詢條件非Map對象,直接就是SQL語句瞭;
/** * 分頁查詢數據 * * @return */ Page<T> page(String sqlStr);
3. 關於XML的配置,會拼裝SQL語句
附:SQL拼裝工具類
/** * @Description: (用一句話描述該文件做什麼) * @author heliang * @date 2018-7-6 下午6:43:42 * @version V2.1 */ package com.onem2.base.common; import com.onem2.base.helper.ObjectHelper; /** * @ClassName: Specification * @Description: (這裡用一句話描述這個類的作用) * @author heliang * @date 2018-7-6 下午6:43:42 * @version V2.1 * Update Logs: * Name: * Date: * Description: 初始化 */ public class Specification { private StringBuilder where = new StringBuilder(); private String groupBy; private String having; private String orderBy; public StringBuilder getWhere() { return where; } public void setWhere(StringBuilder where) { this.where = where; } public String getGroupBy() { return groupBy; } public void setGroupBy(String groupBy) { this.groupBy = groupBy; } public String getHaving() { return having; } public void setHaving(String having) { this.having = having; } public String getOrderBy() { return orderBy; } public void setOrderBy(String orderBy) { this.orderBy = orderBy; } public Specification addOrderBy(String sort, String order) { if (!isEmpty(sort) && !isEmpty(order)) { this.orderBy = ObjectHelper.underscoreName(sort) + " " + order; } return this; } public Specification orLike(String value, String columns) { if (!isEmpty(value)) { StringBuffer strBuf = new StringBuffer(""); for (String column : columns.split(",")) { strBuf.append(ObjectHelper.underscoreName(column) + " like '%" + value + "%' or "); } String orLikeStr = strBuf.substring(0, strBuf.lastIndexOf("or")); where.append(" and (" + orLikeStr + ")"); } return this; } public Specification eq(String column, String value) { if (!isEmpty(value)) { where.append(" and " + ObjectHelper.underscoreName(column) + " = '" + sqlParam(value) + "'"); } return this; } public Specification ne(String column, String value) { if (!isEmpty(value)) { where.append(" and " + ObjectHelper.underscoreName(column) + " != '" + sqlParam(value) + "'"); } return this; } public Specification like(String column, String value) { if (!isEmpty(value)) { where.append(" and " + ObjectHelper.underscoreName(column) + " like '%" + sqlParam(value) + "%'"); } return this; } public Specification notLike(String column, String value) { if (!isEmpty(value)) { where.append(" and " + ObjectHelper.underscoreName(column) + " not like '%" + sqlParam(value) + "%'"); } return this; } public Specification in(String column, String... values) { if (!isEmpty(values)) { where.append(" and " + ObjectHelper.underscoreName(column) + " in (" + inValuesString(values) + ")"); } return this; } public Specification notIn(String column, String... values) { if (!isEmpty(values)) { where.append(" and " + ObjectHelper.underscoreName(column) + " not in (" + inValuesString(values) + ")"); } return this; } public Specification gt(String column, String value) { if (!isEmpty(value)) { where.append(" and " + ObjectHelper.underscoreName(column) + " > '" + sqlParam(value) + "'"); } return this; } public Specification gte(String column, String value) { if (!isEmpty(value)) { where.append(" and " + ObjectHelper.underscoreName(column) + " >= '" + sqlParam(value) + "'"); } return this; } public Specification lt(String column, String value) { if (!isEmpty(value)) { where.append(" and " + ObjectHelper.underscoreName(column) + " < '" + sqlParam(value) + "'"); } return this; } public Specification lte(String column, String value) { if (!isEmpty(value)) { where.append(" and " + ObjectHelper.underscoreName(column) + " <= '" + sqlParam(value) + "'"); } return this; } public Specification between(String column, String from, String to) { if (isEmpty(from) && isEmpty(to)) { return this; } if (isEmpty(to)) { where.append(" and " + ObjectHelper.underscoreName(column) + " >= '" + sqlParam(from) + "'"); } else if (isEmpty(from)) { where.append(" and " + ObjectHelper.underscoreName(column) + " <= '" + sqlParam(to) + "'"); } else { where.append(" and " + ObjectHelper.underscoreName(column) + " between '" + sqlParam(from) + "' and '" + sqlParam(to) + "'"); } return this; } public String sql() { StringBuilder sql = new StringBuilder(""); final int a = 4; final int b = 5; if (where.length() > a) { sql.append(" " + where.substring(b)); } if (!isEmpty(groupBy)) { sql.append(" group by " + groupBy); } if (!isEmpty(having)) { sql.append(" having " + having); } if (!isEmpty(orderBy)) { sql.append(" order by " + orderBy); } return sql.toString(); } public String toString() { return sql(); } private static boolean isEmpty(String value) { return value == null || "".equals(value) || value.trim().length() == 0; } private static boolean isEmpty(String[] values) { if (values == null || values.length == 0) { return true; } for (String value : values) { if (!isEmpty(value)) { return false; } } return true; } private static String inValuesString(String[] values) { StringBuilder string = new StringBuilder(); for (String value : values) { if (isEmpty(value)) { continue; } string.append('\''); string.append(value); string.append('\''); string.append(','); } if (string.length() > 0) { string.deleteCharAt(string.length() - 1); } return string.toString(); } private static String sqlParam(String sqlParam) { return sqlParam.replaceAll("([';]+|(--)+)", ""); } }
附:ObjectHelper 工具源碼:
package com.onem2.base.helper; import java.util.ArrayList; import java.util.Collection; import java.util.List; /** * Object幫助類 功能:此類提供處理 <Object>對象一系列方法 * * @author 賀亮 * */ public class ObjectHelper { /** * 將id數組轉換為id集合 * * @param ids * @return */ public static List<Long> initIds(String[] ids) { List<Long> list = new ArrayList<Long>(); list.add(-1L); for (int i = 0; i < ids.length; i++) { list.add(Long.valueOf(ids[i])); } return list; } /** * 組裝條件 * * @param str * @return */ public static List<String> strToList(String str) { if (isEmpty(str)) { return null; } String[] strs = str.split(","); List<String> list = new ArrayList<String>(); for (int i = 0; i < strs.length; i++) { list.add(strs[i]); } return list; } /** * 判斷這個Object是否為Null或長度為0 * * @param obj * @return */ public static boolean isEmpty(Object obj) { if (obj == null) { return true; } if (obj instanceof Collection) { return ((Collection<?>) obj).isEmpty(); } if (obj instanceof String) { return ((String) obj).equalsIgnoreCase("null") | ((String) obj).trim().toString().equals(""); } if (obj instanceof StringBuffer) { return ((StringBuffer) obj).length() == 0; } if (obj.getClass().isArray()) { try { Object[] a = (Object[]) obj; boolean b = true; for (Object o : a) { b = b & isEmpty(o); if (!b) { break; } } return b; } catch (ClassCastException e) { } } return false; } /** * 判斷這個Object是否不為Null或長度不為0 * * @param obj * @return */ public static boolean isNotEmpty(Object obj) { return !isEmpty(obj); } /** * 返回首字母大寫單詞 * * @param str * @return */ public static String lcyFirstLetterToUpper(String str) { return str.replaceFirst(str.substring(0, 1), str.substring(0, 1) .toUpperCase()); } /** * 轉換為下劃線 * * @param camelCaseName * @return */ public static String underscoreName(String camelCaseName) { StringBuilder result = new StringBuilder(); if (camelCaseName != null && camelCaseName.length() > 0) { result.append(camelCaseName.substring(0, 1).toLowerCase()); for (int i = 1; i < camelCaseName.length(); i++) { char ch = camelCaseName.charAt(i); if (Character.isUpperCase(ch)) { result.append("_"); result.append(Character.toLowerCase(ch)); } else { result.append(ch); } } } return result.toString(); } /** * 轉換為駝峰 * * @param underscoreName * @return */ public static String camelCaseName(String underscoreName) { StringBuilder result = new StringBuilder(); if (underscoreName != null && underscoreName.length() > 0) { boolean flag = false; for (int i = 0; i < underscoreName.length(); i++) { char ch = underscoreName.charAt(i); if ("_".charAt(0) == ch) { flag = true; } else { if (flag) { result.append(Character.toUpperCase(ch)); flag = false; } else { result.append(ch); } } } } return result.toString(); } public static void main(String[] args) { System.out.println(underscoreName("loginName")); } }
這樣就可以做到動態生成查詢條件,復雜的查詢條件也不會去改動XML配置瞭。
mybatis原理:參數解析與SQL動態組裝過程
mybatis執行sql之前, 需要經過參數解析、sql動態組裝等過程,本文主要聊聊mybatis的:
(1)參數解析原理及其過程
(2)sql動態組裝原理及其過程
一、數據準備
1.實體類,省略瞭set、get方法
public class User { private String id; private String username; private String password; private Integer isValid; }
2.mapper接口UserMapper,可以看作是一個根據用戶名和密碼的登錄接口
User getUserByUsernameAndPassword(@Param("name") String username, @Param("pwd") String password);
3.mapper映射
<select id="getUserByUsernameAndPassword" resultType="com.qxf.pojo.User"> select id,username,password,is_valid as isValid from t_user <where> <if test="name != null and name != ''"> username = #{name} </if> <if test="pwd != null and pwd != ''"> and password = #{pwd} </if> </where> </select>
4.測試,mybatis-config.xml配置文件按一般配置即可,這裡就不貼代碼瞭
//讀取配置信息 InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); //根據配置信息,創建SqlSession工廠 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream); //SqlSession工廠創建SqlSession SqlSession sqlSession = factory.openSession(); //獲取接口的代理對象 UserMapper mapper = sqlSession.getMapper(UserMapper.class); //執行相應的接口方法 User user = mapper.getUserByUsernameAndPassword("張三2", null); System.out.println(user);
下面將以這句代碼為入口:
(註意,這裡隻是為瞭測試,給密碼參數傳遞瞭null,正常情況不會這樣傳遞參數的,不然結果返回一個List集合就會報錯的)
//執行相應的接口方法 User user = mapper.getUserByUsernameAndPassword("張三2", null);
二、參數解析原理及其過程
首先要明白一點,返回的是mapper接口的代理對象,所以會來到MapperProxy的invoke方法
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { try { // Object對象的方法,則直接執行 if (Object.class.equals(method.getDeclaringClass())) { return method.invoke(this, args); } if (method.isDefault()) { return this.invokeDefaultMethod(proxy, method, args); } } catch (Throwable var5) { throw ExceptionUtil.unwrapThrowable(var5); } // 獲取mapperMethod,這裡面就會進行參數解析 MapperMethod mapperMethod = this.cachedMapperMethod(method); // 執行方法 return mapperMethod.execute(this.sqlSession, args); }
重點關註這句:
// 獲取mapperMethod,這裡面就會進行參數解析 MapperMethod mapperMethod = this.cachedMapperMethod(method);
參數的解析可以分成兩部:
(1)形參的解析
(2)實參的封裝
(1)形成的解析
一路跟進去,最終會來到 ParamNameResolver,暫且叫做參數名稱解析器吧,首先會在構造器組裝參數的位置和名稱的對應關系,如果我們使用瞭@Param註解,則會使用我們定義的名稱,否則會使用arg0、arg1….依次替代,詳細代碼如下:
public ParamNameResolver(Configuration config, Method method) { // 獲取參數列表中,每一個參數的類型 Class<?>[] paramTypes = method.getParameterTypes(); // 獲取參數註解,因為每個參數可能有多個註解,所以是二維數組 Annotation[][] paramAnnotations = method.getParameterAnnotations(); // 存放結果的map SortedMap<Integer, String> map = new TreeMap(); // 參數個數 int paramCount = paramAnnotations.length; for(int paramIndex = 0; paramIndex < paramCount; ++paramIndex) { if (!isSpecialParameter(paramTypes[paramIndex])) { // 參數名稱 String name = null; // 參數的註解數組 Annotation[] var9 = paramAnnotations[paramIndex]; // 參數註解的個數 int var10 = var9.length; // 遍歷每個註解,找到Param註解,拿到value作為參數名稱 for(int var11 = 0; var11 < var10; ++var11) { Annotation annotation = var9[var11]; if (annotation instanceof Param) { this.hasParamAnnotation = true; name = ((Param)annotation).value(); break; } } if (name == null) { if (config.isUseActualParamName()) { name = this.getActualParamName(method, paramIndex); } if (name == null) { name = String.valueOf(map.size()); } } // 參數序號作為key,從0開始,參數名稱作為值 map.put(paramIndex, name); } } // 沒有做什麼,再一次封裝而已 this.names = Collections.unmodifiableSortedMap(map); }
結果是這樣的:符合我們的預期的
(2)實參的封裝
然後會來到getNamedParams方法對參數進一步的封裝:
public Object getNamedParams(Object[] args) { // 參數個數,這個names就是上面解析後的map,key是從0開始的參數序號,value是參數名稱 int paramCount = this.names.size(); // 這裡的args便是實參列表 // 實參不為空,形參個數不為0 if (args != null && paramCount != 0) { if (!this.hasParamAnnotation && paramCount == 1) { // 沒有使用@Param註解,並且隻有一個參數 return args[(Integer)this.names.firstKey()]; } else { // 將參數封裝成一個map Map<String, Object> param = new ParamMap(); int i = 0; // 對形參循環迭代 for(Iterator var5 = this.names.entrySet().iterator(); var5.hasNext(); ++i) { Entry<Integer, String> entry = (Entry)var5.next(); // names中的參數名稱為key,值為實參值 param.put((String)entry.getValue(), args[(Integer)entry.getKey()]); // 並添加key為param1、param2之類的通用參數 String genericParamName = "param" + String.valueOf(i + 1); if (!this.names.containsValue(genericParamName)) { param.put(genericParamName, args[(Integer)entry.getKey()]); } } return param; } } else { return null; } }
通過源碼可以發現,
(1)如果隻有一個參數,並且沒有使用@Param註解,就直接返回第一個參數
(2)有多個參數,則封裝成一個map,key為參數參數名稱,使用瞭@Param註解,名稱就是註解中的值,否則key為arg0、arg1這種類型,同時,一定含有key為param1、param2的參數,值就是傳入的值
封裝後的結果如下:
這樣就完成瞭參數的解析過程,總結一下:
(1)解析形參,判斷是否使用瞭@Param註解
(2)封裝實參,如果隻有一個,並且沒有使用@Param註解,就直接返回第一個參數值,否則封裝成map
三、動態組裝sql原理及其過程
來到CachingExecutor的如下方法,作為入口:
public <E> List<E> query(MappedStatement ms, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException { // 獲取組裝完成的sql BoundSql boundSql = ms.getBoundSql(parameterObject); // 創建緩存key CacheKey key = this.createCacheKey(ms, parameterObject, rowBounds, boundSql); // 執行查詢 return this.query(ms, parameterObject, rowBounds, resultHandler, key, boundSql); }
重點看這句:
// 獲取組裝完成的sql BoundSql boundSql = ms.getBoundSql(parameterObject);
一路跟進去,來到DynamicSqlSource的getBoundSql方法:
public BoundSql getBoundSql(Object parameterObject) { // 將參數封裝成動態上下文,DynamicContext中sqlBuilder就是最後組裝的sql DynamicContext context = new DynamicContext(this.configuration, parameterObject); // 根據條件,動態組裝sql this.rootSqlNode.apply(context); SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(this.configuration); Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass(); // 將#{參數}替換為? SqlSource sqlSource = sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings()); BoundSql boundSql = sqlSource.getBoundSql(parameterObject); Map var10000 = context.getBindings(); Objects.requireNonNull(boundSql); var10000.forEach(boundSql::setAdditionalParameter); return boundSql; }
我們先看下這句:
// 根據條件,動態組裝sql this.rootSqlNode.apply(context);
對於我們的sql:
<select id="getUserByUsernameAndPassword" resultType="com.qxf.pojo.User"> select id,username,password,is_valid as isValid from t_user <where> <if test="name != null and name != ''"> username = #{name} </if> <if test="pwd != null and pwd != ''"> and password = #{pwd} </if> </where> </select>
每個標簽都有對應的SqlNode來處理,比如if標簽,就由IfSqlNode來處理,where標簽,則會通過TrimSqlNode來處理,SqlNode的具體實現類如下:
這裡以IfSqlNode處理if標簽為例:
這是就是兩步:
(1)判斷表達式的值是否為真,這裡最終使用的是Ognl來判斷
(2)如果表達式的為真,就將標簽內容追加到sql中去
處理結果如下:
因為密碼的參數傳入為null,所以不會拼接密碼查詢條件,隻拼接瞭用戶名查詢條件
然後是將#{參數}替換為?進行占位:
// 將#{參數}替換為? SqlSource sqlSource = sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings());
這個就比較簡單瞭,可以自行看源碼,最終是這樣的:
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。
推薦閱讀:
- SpringBoot @Cacheable自定義KeyGenerator方式
- Java代碼裡如何拼接SQL語句到mybatis的xml
- JAVA8發送帶有Body的HTTP GET請求
- Java實用工具之StringJoiner詳解
- Android動態表格的實現代碼(內容、樣式可擴縮)