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>{privateObject objectValue;private Map<String,Object> cache = new HashMap<>();publicObjectBingVariables(Object objectValue){
Objects.requireNonNull(objectValue,"传入得查询参数不能为null！");this.objectValue =objectValue;
init(objectValue);
}private voidinit(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 newRuntimeException(ex);
}
}
@OverridepublicObject put(String key, Object value) {returnsuper.put(key.toUpperCase(), value);
}
@Overridepublic Object get(Object key) {return super.get(String.valueOf(key).toUpperCase());
}
}
public class FunctionBindVariables extends HashMap<String,Object>{privateString 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 newFunctionBindVariables(sql,function);
}
@Overridepublic Object get(Object key) {return "sql".equals(key) ? this.sql : function.apply((String)key);
}
}
public class SingletonBindVariables extends HashMap<String,Object>{publicSingletonBindVariables(String sql, Object param){
put("sql",sql);
put("param",param);
}public static Map<String,Object> from(String sql, Object param){return newSingletonBindVariables(sql,param);
}
@Overridepublic 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 classGrid {private static final Logger log = LoggerFactory.getLogger(Grid.class);privateJdbcType[] jdbcTypes;private int MaxCol = 0;private int MaxRow = 0;private int MaxNumber = 0;private List<String> data = newArrayList();public Grid(intmaxCol) {this.MaxCol =maxCol;
}protected voidaddText(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(introw, String ignoreCaseRowName) {return getText(row,ignoreCaseRowName,false);
}/**
* 根据行数和列名匹配对应得数据
* @param row 列数
* @param ignoreCaseRowName 忽略大小写得 列名
* @param IgnoreUnmatchedColumn 忽略掉未匹配得列，当根据列名未找到数据时生效，true时如果列名不存在会返回null值，false时则抛出异常*/ public String getText(introw, 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+"]的列");returngetText(row,colIndex);
}public String getText(int row, intcol) {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, intcol, String text) {int Number = (row - 1) * this.MaxCol + col - 1;if (Number <= this.MaxNumber) {this.data.set(Number, text);
}else{
log.error("指定的位置在结果集中没有数据");
}
}public intgetMaxCol() {return this.MaxCol;
}public intgetMaxRow() {return this.MaxRow;
}publicString[] getColNames(){
String[] colNames= newString[MaxCol];for(int i=0;i<colNames.length;i++){
colNames[i]= this.data.get(i);
}returncolNames;
}public String getColName(intindex) {if (index > 0 && index <= this.MaxCol) {return (String)this.data.get(index - 1);
}else{
log.error("指定的位置在结果集中没有数据");return null;
}
}public boolean setColName(intindex, String columnName) {if (index > 0 && index <= this.MaxCol) {this.data.set(index - 1, columnName);return true;
}else{return false;
}
}public String[] getRowData(introw) {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);
}returnresult;
}else{return new String[0];
}
}public Map<String,String> getRowMap(introw){
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));
}returndata;
}public String[] getColData(intcol) {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);
}returnresult;
}else{return new String[0];
}
}public voidsetJdbcTypes(JdbcType[] jdbcTypes) {this.jdbcTypes =jdbcTypes;
}public JdbcType getJdbcType(intcol) {return this.jdbcTypes[col - 1];
}publicString 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("]}");returnbuilder.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 classGridResultSetHandler implements ResultSetHandler {privateTypeHandlerRegistry registry;publicGridResultSetHandler(){
registry= newTypeHandlerRegistry();
registry.register(String.class,JdbcType.TIMESTAMP, newDateTypeHandler());
registry.register(String.class,JdbcType.DATE, newDateTypeHandler());
registry.register(String.class,JdbcType.NUMERIC, newDoubleTypeHandler());
registry.register(String.class,JdbcType.DOUBLE, newDoubleTypeHandler());
}public List<Grid>handleResultSets(Statement statement) throws SQLException {
ResultSet resultSet=statement.getResultSet();try{
ResultSetMetaData metaData=resultSet.getMetaData();int columnCount =metaData.getColumnCount();
Grid grid= newGrid(columnCount);
JdbcType[] jdbcTypes= newJdbcType[columnCount];inti;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) {continuelabel61;
}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;returnvar8;
}
}finally{
resultSet.close();
}
}public Cursor<Grid>handleCursorResultSets(Statement statement) throws SQLException {throw new UnsupportedOperationException("Unsupported");
}public voidhandleOutputParameters(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做适配支持等，