Mybatis騷操作-通用查詢工具類

老專案大多都有對JDBC進行了封裝，可以直接執行SQL的工具類，在做專案升級改造的時候（這裡僅指整合mybatis），要麼全部調整成dao-xml的形式（會有改動程式碼多的問題，而且看程式碼時需要xml和java來回切換），要麼維持原邏輯不改動（跟mybatis基本無關，同樣難以用到mybatis的配置）

這裡實現個可以讓工具使用到mybatis的xml和dao騷氣操作，可以保持工具類原有用法

這裡僅展示查詢部分邏輯，增刪改類似的寫法，寫法中sql和作為字串寫在java程式碼中，不習慣可以不往下看了

1、根據mybatis寫法寫dao類和xml類，同時需要一個查詢返回的資料集類即可

如果需要轉為具體dto類，寫轉換邏輯即可

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.*.utility.SQLMapper">

    <select id="execSQL" resultType="com.*.utility.Grid" parameterType="java.util.Map">
        ${sql}
    </select>

    <update id="execUpdateSQL" >
        ${sql}
    </update>

</mapper>

@Mapper
public interface SQLMapper {

    /**
     * 核心方法是這個，直接用肯定不方便，因為要把sql和需要的引數都放到 map裡面
     * 在呼叫的寫法上應該把sql和sql執行需要的引數作為兩個入參傳入
     */
    @Deprecated
    Grid execSQL(Map<String,Object> params);

    /**
     * 無需引數的查詢
     */
    default Grid execSQL(String sql){
        return execSQL(Collections.singletonMap("sql",sql));
    }

    /**
     * 對sql中僅需一個引數的查詢，
     * @param bindVariable 需要的引數，僅可為String、int、double、date等基本的型別
     */
    default Grid execSQLBindVariable(String sql, Object bindVariable){
        return execSQL(new SingletonBindVariables(sql,bindVariable));
    }

    /**
     * 將引數放入Map中進行查詢，如果入參是dto型別的傳入，推薦使用ObjectBingVariables類進行包裝下
     */
    default Grid execSQLBindVariables(String sql, Map<String,Object> bindVariables){
        bindVariables.put("sql",sql);
        return execSQL(bindVariables);
    }

    /**
     * 引數支援Lambda寫法
     */
    default Grid execSQL(String sql, Function<String,Object> param){
        return execSQLBindVariables(sql, FunctionBindVariables.from(sql,param));
    }
}

　　

public class ObjectBingVariables extends HashMap<String,Object> {

    private Object objectValue;

    private Map<String,Object> cache = new HashMap<>();

    public ObjectBingVariables(Object objectValue){
        Objects.requireNonNull(objectValue,"傳入得查詢引數不能為null！");
        this.objectValue = objectValue;
        init(objectValue);
    }

    private void init(Object dto){
        try {
            Method[] methods = dto.getClass().getMethods();
            for (Method method : methods) {
                if (method.getName().startsWith("get") && method.getParameterCount() == 0) {
                    Object value = method.invoke(dto);
                    String key = method.getName().substring(3);
                    this.put(key.toUpperCase(),value);
                }
            }
        }catch (Exception ex){
            throw new RuntimeException(ex);
        }
    }

    @Override
    public Object put(String key, Object value) {
        return super.put(key.toUpperCase(), value);
    }

    @Override
    public Object get(Object key) {
        return super.get(String.valueOf(key).toUpperCase());
    }
}

public class FunctionBindVariables extends HashMap<String,Object> {

    private String sql;
    private Function<String,Object> function;

    public FunctionBindVariables(String sql, Function<String,Object> function){
        this.sql = sql;
        this.function = function;
    }

    public static Map<String,Object> from(String sql, Function<String,Object> function){
        return new FunctionBindVariables(sql,function);
    }

    @Override
    public Object get(Object key) {
        return "sql".equals(key) ? this.sql : function.apply((String)key);
    }
}

public class SingletonBindVariables extends HashMap<String,Object> {


    public SingletonBindVariables(String sql, Object param){
        put("sql",sql);
        put("param",param);
    }

    public static Map<String,Object> from(String sql, Object param){
        return new SingletonBindVariables(sql,param);
    }


    @Override
    public Object get(Object key) {
        return "sql".equals(key) ? super.get("sql") : super.get("param");
    }

}

到這裡，查詢就僅需要一個通用的查詢結果集Grid物件

import org.apache.ibatis.type.JdbcType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;
import java.util.function.Function;

public class Grid  {
    private static final Logger log = LoggerFactory.getLogger(Grid.class);
    private JdbcType[] jdbcTypes;
    private int MaxCol = 0;
    private int MaxRow = 0;
    private int MaxNumber = 0;
    private List<String> data = new ArrayList();

    public Grid(int maxCol) {
        this.MaxCol = maxCol;
    }

    protected void addText(String text) {
        this.data.add(text);
        int size = this.data.size();
        if (size > this.MaxCol) {
            this.MaxNumber = size - this.MaxCol;
            if (this.MaxNumber % this.MaxCol == 0) {
                this.MaxRow = this.MaxNumber / this.MaxCol;
            } else {
                this.MaxRow = this.MaxNumber / this.MaxCol + 1;
            }

        }
    }

    public <T> T getText(int row, int col, Function<String, T> function) {
        return function.apply(this.getText(row, col));
    }

    public String getText(int row, String ignoreCaseRowName) {
        return getText(row,ignoreCaseRowName,false);
    }

    /**
     * 根據行數和列名匹配對應得資料
     * @param row 列數
     * @param ignoreCaseRowName 忽略大小寫得 列名
     * @param IgnoreUnmatchedColumn 忽略掉未匹配得列，當根據列名未找到資料時生效，true時如果列名不存在會返回null值，false時則丟擲異常
     */
    public String getText(int row, String ignoreCaseRowName, boolean IgnoreUnmatchedColumn) {
        int colIndex = -1;
        for(int i=0;i<this.MaxCol;i++){
            if(this.data.get(i).equalsIgnoreCase(ignoreCaseRowName)){
                colIndex = i+1;
                break;
            }
        }

        if(colIndex== -1 && IgnoreUnmatchedColumn)
            return null;

        if(colIndex == -1)
            throw new RuntimeException("未找到符合["+ignoreCaseRowName+"]的列");

        return getText(row,colIndex);
    }

    public String getText(int row, int col) {
        int Number = (row - 1) * this.MaxCol + col - 1;
        if (Number <= this.MaxNumber) {
            return (String)this.data.get(Number + this.MaxCol);
        } else {
            log.error("指定的位置在結果集中沒有資料");
            return null;
        }
    }

    public void replaceText(int row, int col, String text) {
        int Number = (row - 1) * this.MaxCol + col - 1;
        if (Number <= this.MaxNumber) {
            this.data.set(Number, text);
        } else {
            log.error("指定的位置在結果集中沒有資料");
        }
    }

    public int getMaxCol() {
        return this.MaxCol;
    }

    public int getMaxRow() {
        return this.MaxRow;
    }

    public String[] getColNames(){
        String[] colNames = new String[MaxCol];
        for(int i=0;i<colNames.length;i++){
            colNames[i] = this.data.get(i);
        }
        return colNames;
    }

    public String getColName(int index) {
        if (index > 0 && index <= this.MaxCol) {
            return (String)this.data.get(index - 1);
        } else {
            log.error("指定的位置在結果集中沒有資料");
            return null;
        }
    }

    public boolean setColName(int index, String columnName) {
        if (index > 0 && index <= this.MaxCol) {
            this.data.set(index - 1, columnName);
            return true;
        } else {
            return false;
        }
    }

    public String[] getRowData(int row) {
        if (row > 0 && row <= this.MaxRow) {
            String[] result = new String[this.MaxCol];

            for(int i = 0; i < this.MaxCol; ++i) {
                int index = this.MaxCol * row + i;
                result[i] = (String)this.data.get(index);
            }

            return result;
        } else {
            return new String[0];
        }
    }

    public Map<String,String> getRowMap(int row){
        Map<String,String> data = new IgnoreCaseHashMap<>();
        String[] colNames = getColNames();
        for(int i=0;i<colNames.length;i++){
            data.put(colNames[i],getText(row,i+1));
        }
        return data;
    }

    public String[] getColData(int col) {
        if (col > 0 && col <= this.MaxCol) {
            String[] result = new String[this.MaxRow];

            for(int i = 0; i < this.MaxRow; ++i) {
                int index = this.MaxRow * (i + 1) + col;
                result[i] = (String)this.data.get(index);
            }

            return result;
        } else {
            return new String[0];
        }
    }

    public void setJdbcTypes(JdbcType[] jdbcTypes) {
        this.jdbcTypes = jdbcTypes;
    }

    public JdbcType getJdbcType(int col) {
        return this.jdbcTypes[col - 1];
    }

    public String toString() {
        StringBuilder builder = new StringBuilder("Grid{[");

        for(int i = 0; i < this.data.size(); ++i) {
            if (i != 0 && i % this.MaxCol == 0) {
                builder.append("],[");
            } else if (i != 0) {
                builder.append(",");
            }

            builder.append((String)this.data.get(i));
        }

        builder.append("]}");
        return builder.toString();
    }



}

透過mybatis外掛讓查詢結果轉為該物件

import com.sinosoft.mybatis.typehandler.DateTypeHandler;
import com.sinosoft.mybatis.typehandler.DoubleTypeHandler;
import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.Collections;
import java.util.List;

@Component
@Intercepts({
        @Signature(type= ResultSetHandler.class, method="handleResultSets", args={Statement.class}),
        @Signature(type= ResultSetHandler.class, method="handleCursorResultSets", args={Statement.class}),
        @Signature(type= ResultSetHandler.class, method="handleOutputParameters", args={CallableStatement.class})
})
public class GridResultSetHandler implements ResultSetHandler {
    
    private TypeHandlerRegistry registry;
    
    public GridResultSetHandler(){
        registry = new TypeHandlerRegistry();
        registry.register(String.class,JdbcType.TIMESTAMP, new DateTypeHandler());
        registry.register(String.class,JdbcType.DATE, new DateTypeHandler());
        registry.register(String.class,JdbcType.NUMERIC, new DoubleTypeHandler());
        registry.register(String.class,JdbcType.DOUBLE, new DoubleTypeHandler());
    }
    

    public List<Grid> handleResultSets(Statement statement) throws SQLException {
        ResultSet resultSet = statement.getResultSet();

        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            Grid grid = new Grid(columnCount);
            JdbcType[] jdbcTypes = new JdbcType[columnCount];

            int i;
            for(i = 1; i <= columnCount; ++i) {
                grid.addText(metaData.getColumnName(i));
                jdbcTypes[i - 1] = JdbcType.forCode(metaData.getColumnType(i));
            }

            grid.setJdbcTypes(jdbcTypes);

            label61:
            while(true) {
                if (resultSet.next()) {
                    i = 1;

                    while(true) {
                        if (i > columnCount) {
                            continue label61;
                        }

                        int columnType = metaData.getColumnType(i);
                        TypeHandler<String> typeHandler = this.registry.getTypeHandler(String.class, JdbcType.forCode(columnType));
                        grid.addText((String)typeHandler.getResult(resultSet, i));
                        ++i;
                    }
                }

                List<Grid> matrices = Collections.singletonList(grid);
                List var8 = matrices;
                return var8;
            }
        } finally {
            resultSet.close();
        }
    }

    public Cursor<Grid> handleCursorResultSets(Statement statement) throws SQLException {
        throw new UnsupportedOperationException("Unsupported");
    }

    public void handleOutputParameters(CallableStatement callableStatement) throws SQLException {
        throw new UnsupportedOperationException("Unsupported");
    }
}    

呼叫示例程式碼如下：

@Autowired
private SQLMapper cSQLMapper;

public void test(){


    cSQLMapper.execSQLBindVariable("select * from Code where codeType=#{codeType}","sex");

    Map<String,Object> bind = new HashMap<>();
    bind.put("codeType","sex");
    bind.put("code","1");
    cSQLMapper.execSQLBindVariables("select * from Code where codeType=#{codeType} and code=#{code}",bind);

    LDCodePo tLDCodePo = new LDCodePo();
    tLDCodePo.setCodeType("sex");
    tLDCodePo.setCode("1");
    cSQLMapper.execSQLBindVariables("select * from Code where codeType=#{codetype} and code=#{code}",
            new ObjectBingVariables(tLDCodePo));
}

簡單的查詢可以使用這個，複雜的雖然也是可以透過sql字串拼接去實現，但對於需要使用foreach標籤等的，更好的還是使用dao-xml的形式

對於查詢結果集需要轉為具體物件的，可以對Grid做適配支援等，