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做适配支持等，
