本篇文章参考:
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));
}
}
}
}