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。

推薦閱讀: