mybatis从mapper中获取执行的sql语句

本篇文章参考：

Mybatis获取sql语句_mappedstatement 获取sql_头铁的很的博客-CSDN博客

Mybatis从Mapper实例获取Sqlsession对象_OK_boom的博客-CSDN博客

获取sql：改为从TypeHandler获取转换后的参数值

获取sqlSession：支持mybatisPlus

实现效果：从mapper对象中，指定方法名称，指定参数对象，获取填充后要执行的sql语句。

（可能存在bug，如果mybatis填充参数不是执行ps.setString方法，获取sql可能会报错，或者结果不正常）

    @Test
    public void fun3() throws Exception{
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(new DruidDataSource());
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        SqlSessionFactory factory = bean.getObject();
        Assert.notNull(factory,"factory不能是null!");
        SqlSession sqlSession = factory.openSession();
        ConnectInfoMapper mapper = sqlSession.getMapper(ConnectInfoMapper.class);

        ConnectInfo bo = new ConnectInfo();
        bo.setConnectType("mysql");
        bo.setConnectMetaData(new DataSourceBase());
        bo.setDate(new Date());
        LambdaQueryWrapper<ConnectInfo> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.like(ConnectInfo::getConnectType,"mysql");
        LambdaUpdateWrapper<ConnectInfo> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
        lambdaUpdateWrapper.eq(ConnectInfo::getConnectType,"mysql");
		//mapper对象,方法名称，对应的方法参数...
        System.err.println(GetSqlUtils.getSqlByMybatis(mapper, MyUtils.getLambdaName(mapper::update),bo,lambdaUpdateWrapper));
    }

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.override.MybatisMapperProxy;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.aopalliance.intercept.MethodInvocation;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.binding.MapperProxy;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import org.springframework.util.Assert;
import org.springframework.util.ReflectionUtils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.DateFormat;
import java.time.LocalDateTime;
import java.util.*;

public class GetSqlUtils {
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class SqlSessionObject{
        SqlSession sqlSession;
        Class<?> cls;   //代理的接口
    }

    public static class ListSqlFunction implements SqlFunction<List<Map<String,Object>>>{
        private final List<Map<String,Object>> list = new ArrayList<>();

        @Override
        public void apply(Map<String,Object> entity) throws Exception {
            list.add(entity);
        }

        @Override
        public List<Map<String,Object>> getResult() throws Exception {
            return list;
        }
    }

    public interface SqlFunction<T>{
        default void init(){}
        void apply(Map<String,Object> entity) throws Exception;
        T getResult() throws Exception;
    }

    //缓存对象
    public static final Map<String, SqlSessionObject> GLOBAL_MAP= new HashMap<>();
    public static final ThreadLocal<Object> THREAD_LOCAL = new ThreadLocal<>();
    public static final ThreadLocal<String> THREAD_LOCAL_SQL = new ThreadLocal<>();
    public static final PreparedStatement PREPARE_PROXY =
            MyUtils.getInterfaceProxy(PreparedStatement.class,GetSqlUtils::prepareProxy);
    public static final Connection CONNECTION_PROXY =
            MyUtils.getInterfaceProxy(Connection.class,GetSqlUtils::connectProxy);
    private static  Method setParameter = null;

    static  {
        Method[] methods = ReflectionUtils.getAllDeclaredMethods(TypeHandler.class);
        for (Method method : methods) {
            if(method.getName().equals("setParameter")){
                setParameter = method;
                ReflectionUtils.makeAccessible(method);
                break;
            }
        }

    }

    private static Object prepareProxy(MethodInvocation var){
        try {
            if(!"setNull".equals(var.getMethod().getName()) && var.getArguments().length >= 2){
                THREAD_LOCAL.set(var.getArguments()[1]);
            }else {
                THREAD_LOCAL.set(null);
            }

            if (var.getMethod().getName().startsWith("set") && var.getArguments().length >= 2) {
                String sql = THREAD_LOCAL_SQL.get();
                if (sql != null) {
                    sql = sql.replaceFirst("\\?",
                            "'" + Objects.toString(THREAD_LOCAL.get(), "") + "'");
                    THREAD_LOCAL_SQL.set(sql);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private static Object connectProxy(MethodInvocation var){
        try {
            if("prepareStatement".equals(var.getMethod().getName())){
                THREAD_LOCAL_SQL.set((String) var.getArguments()[0]);
                return PREPARE_PROXY;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从Mapper实例获取Sqlsession对象
     * @param mapper
     * @return
     */
    synchronized private static SqlSessionObject getSqlSession(Object mapper) {
        SqlSessionObject sqlSessionObject = GLOBAL_MAP.get(mapper.getClass().getName());
        if(sqlSessionObject != null){
            return sqlSessionObject;
        }

        if (!Proxy.isProxyClass(mapper.getClass())){
            return null;
        }
        InvocationHandler invocationHandler=Proxy.getInvocationHandler(mapper);

        Object mapperProxy = null;
        if (invocationHandler instanceof MapperProxy){
            mapperProxy= (MapperProxy)invocationHandler;
        }
        if(invocationHandler instanceof MybatisMapperProxy){
            mapperProxy= (MybatisMapperProxy)invocationHandler;
        }
        if(mapperProxy == null){
            return null;
        }

        try {
            sqlSessionObject  = new SqlSessionObject();
            Field field = ReflectionUtils.findField(mapperProxy.getClass(), "sqlSession");
            Assert.notNull(field,"sqlSession对象不能为null!");
            ReflectionUtils.makeAccessible(field);
            SqlSession sqlSession = (SqlSession)field.get(mapperProxy);
            sqlSessionObject.setSqlSession(sqlSession);

            field = ReflectionUtils.findField(mapperProxy.getClass(), "mapperInterface");
            Assert.notNull(field,"mapperInterface对象不能为null!");
            ReflectionUtils.makeAccessible(field);
            Class<?> cls = (Class<?>)field.get(mapperProxy);
            sqlSessionObject.setCls(cls);
            GLOBAL_MAP.put(mapper.getClass().getName(),sqlSessionObject);
            return sqlSessionObject;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static String getSqlByMybatis(Object mapper,String methodName,Object... parameter) throws Exception{
        return getSqlString("mybatis",mapper,methodName,parameter);
    }

    public static String getSqlBySelf(Object mapper,String methodName,Object... parameter) throws Exception{
        return getSqlString("self",mapper,methodName,parameter);
    }

    /**
     * 获取格式化后的sql语句
     * @param mapper mapper对象
     * @param methodName 调用的方法
     * @param parameter 实际参数
     * @return
     */
    private static String getSqlString(String type,Object mapper,String methodName,Object... parameter) throws Exception{
        SqlSessionObject sqlSessionObject = getSqlSession(mapper);
        Assert.notNull(sqlSessionObject,"sqlSessionObject对象不能为null!");
        SqlSession sqlSession = sqlSessionObject.getSqlSession();
        Class<?> cls = sqlSessionObject.getCls();
        MappedStatement mappedStatement =
                sqlSession.getConfiguration().getMappedStatement(cls.getTypeName()+"."+methodName);
        Configuration configuration = mappedStatement.getConfiguration();
        Method[] methods = ReflectionUtils.getAllDeclaredMethods(cls);
        Method method =null;
        for(Method temp:methods){
            if(temp.getName().equals(methodName)){
                method = temp;
                break;
            }
        }
        //执行sql语句，未被代理的mapper对象
        //MapperMethod mapperMethod = new MapperMethod(cls,method,configuration);
        //mapperMethod.execute(sqlSession,parameter);

        Assert.notNull(method,"method对象不能为null!");
        MapperMethod.MethodSignature methodSignature = new MapperMethod.MethodSignature(configuration,cls,method);
        Object obj = methodSignature.convertArgsToSqlCommandParam(parameter);
        //获取除去xml标签的sql语句
        BoundSql boundSql = mappedStatement.getBoundSql(obj);

        try {
            String sql = null;
            switch (type){
                case "mybatis":
                    sql = getSqlStringByMybatis(boundSql, obj, mappedStatement);
                    break;
                case "self":
                    sql = getSqlStringBySelf(configuration, boundSql);
                    break;
                default:
                    break;
            }
            return sql;
        } catch (Exception e){
            e.printStackTrace();
        }finally {
            THREAD_LOCAL.remove();
            THREAD_LOCAL_SQL.remove();
        }
        return null;
    }

    private static String getSqlStringByMybatis(BoundSql boundSql,Object obj,
                                          MappedStatement mappedStatement) throws Exception{
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        PreparedStatement preparedStatement = CONNECTION_PROXY.prepareStatement(sql);
        DefaultParameterHandler defaultParameterHandle = new DefaultParameterHandler(mappedStatement,obj,boundSql);
        defaultParameterHandle.setParameters(preparedStatement);
        return THREAD_LOCAL_SQL.get();
    }

    private static String getSqlStringBySelf(Configuration configuration, BoundSql boundSql) throws Exception{
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (parameterObject == null || parameterMappings.size() == 0) {
            return sql;
        }

        MetaObject metaObject = configuration.newMetaObject(parameterObject);
        for (int i = 0; i < parameterMappings.size(); i++) {
            ParameterMapping parameterMapping = parameterMappings.get(i);
            String propertyName = parameterMapping.getProperty();
            JdbcType jdbcType = parameterMapping.getJdbcType();
            Object obj = null;
            if (metaObject.hasGetter(propertyName)) {
                obj = metaObject.getValue(propertyName);
            } else if (boundSql.hasAdditionalParameter(propertyName)) {
                obj = boundSql.getAdditionalParameter(propertyName);
            }
            TypeHandler<?> typeHandler = parameterMapping.getTypeHandler();
            if (obj == null && jdbcType == null) {
                jdbcType = configuration.getJdbcTypeForNull();
            }

            setParameter.invoke(typeHandler,PREPARE_PROXY,i,obj,jdbcType);
            obj = THREAD_LOCAL.get();
            THREAD_LOCAL.remove();

            sql = sql.replaceFirst("\\?", "'" + Objects.toString(obj, "") + "'");
        }
        return sql;
    }

    @Deprecated
    private static String getParameterValue(Object obj) {
        String value;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(obj) + "'";
        } else if (obj instanceof LocalDateTime) {
            LocalDateTime localDateTime = (LocalDateTime)obj;
            value = "'" + DateUtil.format(localDateTime,DatePattern.UTC_SIMPLE_MS_PATTERN) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "''";
            }
        }
        return value;
    }

    /**
     * 	游标类型：
     * 	ResultSet.TYPE_FORWARD_ONLY:只进游标
     * 	ResultSet.TYPE_SCROLL_INSENSITIVE:可滚动。但是不受其他用户对数据库更改的影响。
     * 	ResultSet.TYPE_SCROLL_SENSITIVE:可滚动。当其他用户更改数据库时这个记录也会改变。
     * 	能否更新记录：
     * 	ResultSet.CONCUR_READ_ONLY,只读
     * 	ResultSet.CONCUR_UPDATABLE,可更新
     *
     *  prepareStatement()方法中resultSetType的默认值为ResultSet.TYPE_FORWARD_ONLY，resultSetConcurrency的默认值为ResultSet.CONCUR_READ_ONLY
     * @param mapper
     * @param sql
     * @return
     */
    public static <T> T executeSelectSql(Object mapper,String sql,Integer fetchSize,SqlFunction<T> sqlFunction){
        SqlSessionObject sqlSessionObject = getSqlSession(mapper);
        Assert.notNull(sqlSessionObject,"sqlSessionObject对象不能为null!");
        SqlSession sqlSession = sqlSessionObject.getSqlSession();
        try(Connection coon = sqlSession.getConfiguration().getEnvironment().getDataSource().getConnection()){
            return executeSelectSql(coon,sql,fetchSize,sqlFunction);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public static <T> T executeSelectSql(Connection coon,String sql,
                                         Integer fetchSize,
                                         SqlFunction<T> sqlFunction){
        try(PreparedStatement preparedStatement = coon.prepareStatement(sql)){
            if(fetchSize != null){
                //开启游标查询，参数为每次查询的数量，jdbc没有定义这个参数值的规范，
                // 不同数据库可能不一样,
                // mysql必须设置为Integer.MIN_VALUE才可以开启游标，8.0驱动下验证失败
                // 8.0驱动下，mysql连接属性添加&useCursorFetch=true，设置fetchSize为一个正整数才可以生效(二者缺一不可)
                //enableStreamingResults()可以开启流式查询(每次查一个)，但这不是jdbc接口,需要转化类为mysql的实现类
                //检查游标是否生效:查看ResultSet对象的rowData属性，如果有rows属性，则说明使用游标失败，有fetchRows属性，则说明成功
                //使用游标成功后，每次next办法会检查接下来有没有数据，有数据的话会取出fetchSize值的数据量
                preparedStatement.setFetchSize(fetchSize);
                //设置游标方向，非必须
                //preparedStatement.setFetchDirection(ResultSet.FETCH_FORWARD);
            }
            //设置最大查到条数
            //preparedStatement.setMaxRows(10);
            //查询数据,每次从服务器检索指定数量的数据，调用next()遍历完后，会再次从服务器取数据。
            ResultSet resultSet = preparedStatement.executeQuery();
            return convertList(resultSet,sqlFunction);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public static <T> T convertList(ResultSet rs,SqlFunction<T> sqlFunction) throws Exception {
        sqlFunction.init();
        try (rs) {
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                sqlFunction.apply(rowData);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlFunction.getResult();
    }
}

import com.baomidou.mybatisplus.core.toolkit.ReflectionKit;
import com.baomidou.mybatisplus.core.toolkit.support.IdeaProxyLambdaMeta;
import com.baomidou.mybatisplus.core.toolkit.support.LambdaMeta;
import com.baomidou.mybatisplus.core.toolkit.support.ReflectLambdaMeta;
import com.baomidou.mybatisplus.core.toolkit.support.ShadowLambdaMeta;
import io.vavr.*;
import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;
import org.springframework.aop.framework.ProxyFactory;

import java.io.Serializable;
import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.util.function.Function;

public class MyUtils {
    public static <T,R> T getInterfaceProxy(Class<T> cls, Function<MethodInvocation,R> function){
        ProxyFactory factory = new ProxyFactory();
        factory.setInterfaces(cls);
        factory.addAdvice((MethodInterceptor) function::apply);
        return cls.cast(factory.getProxy());
    }

    public static <R> String getLambdaName(Function0<R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    public static <T, R> String getLambdaName(Function1<T, R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    public static <T1, T2, R> String getLambdaName(Function2<T1, T2, R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    public static <T1, T2, T3, R> String getLambdaName(Function3<T1, T2, T3, R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    public static <T1, T2, T3, T4, R> String getLambdaName(Function4<T1, T2, T3, T4, R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    public static <T1, T2, T3, T4, T5, R> String getLambdaName(Function5<T1, T2, T3, T4, T5, R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    public static <T1, T2, T3, T4, T5, T6, R> String getLambdaName(Function6<T1, T2, T3, T4, T5, T6, R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    public static <T1, T2, T3, T4, T5, T6, T7, R> String getLambdaName(Function7<T1, T2, T3, T4, T5, T6, T7, R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    public static <T1, T2, T3, T4, T5, T6, T7, T8, R> String getLambdaName(Function8<T1, T2, T3, T4, T5, T6, T7, T8, R> func) {
        LambdaMeta meta = getLambdaMeta(func);
        return meta.getImplMethodName();
    }

    private static LambdaMeta getLambdaMeta(Serializable func){
        if (func instanceof Proxy) {
            return new IdeaProxyLambdaMeta((Proxy)func);
        } else {
            try {
                Method method = func.getClass().getDeclaredMethod("writeReplace");
                return new ReflectLambdaMeta((SerializedLambda)((Method)ReflectionKit.setAccessible(method)).invoke(func));
            } catch (Throwable var2) {
                return new ShadowLambdaMeta(com.baomidou.mybatisplus.core.toolkit.support.SerializedLambda.extract(func));
            }
        }
    }

}