【Mybatis】获取sql
最近项目遇到springboot共享数据库连接池问题，解决方案是连接池单独做一个服务，其他服务通过Feign把sql传过去执行调用。
这样想要对项目改动最小的就是用mybatis获取sql
1、首先去掉mybatis启动的maven配置，只要mybatis原生的就可以了
<dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency>
pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com</groupId> <artifactId>mybatis</artifactId> <version>1.0</version> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency> <!-- 集成lombok 框架 --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> <scope>provided</scope> <version>1.18.12</version> </dependency> </dependencies> </project>
2、mapper.xml，这里测试是User.xml
<?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="UserMapper"> <select id="findUserById" parameterType="com.mybatis.entity.User" resultType="com.mybatis.entity.User"> SELECT * FROM USER WHERE id = #{id} </select> <select id="findUserByUsername" parameterType="com.mybatis.entity.User" resultType="com.mybatis.entity.User"> SELECT * FROM USER WHERE username = #{username} </select> <insert id="insertUser" parameterType="com.mybatis.entity.User"> insert into user (id, username, password) values (#{id}, #{username}, #{password}) </insert> <update id="updateUserUsernamePassword" parameterType="com.mybatis.entity.User"> update user set username=#{username}, password=#{password} where id=#{id} </update> </mapper>
3、mybatis.xml读取User.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <mappers> <mapper resource="mapper/User.xml" /> </mappers> </configuration>
4、User类
package com.mybatis.entity; import lombok.Data; @Data public class User { private int id; private String username; private String password; }
5、测试
package com.mybatis.test; import com.mybatis.entity.User; import org.apache.ibatis.io.Resources; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.mapping.ParameterMode; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.property.PropertyTokenizer; import org.apache.ibatis.scripting.xmltags.ForEachSqlNode; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.lang.reflect.Field; import java.util.List; import java.util.Map; public class Mytest { public static void main(String[] args) throws IOException { // 创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml")); User user = new User(); user.setId(1); user.setUsername("zhangsan"); user.setPassword("33333"); String sql = getMyBatisSql("UserMapper.insertUser", user, sqlSessionFactory); System.out.println(sql); } /** * 运行期获取MyBatis执行的SQL及参数 * * @param id Mapper xml 文件里的select Id * @param obj 参数 * @param sqlSessionFactory * @return */ public static String getMyBatisSql(String id, Object obj, SqlSessionFactory sqlSessionFactory) { MappedStatement ms = sqlSessionFactory.getConfiguration().getMappedStatement(id); BoundSql boundSql = ms.getBoundSql(obj); String sql = boundSql.getSql(); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); if (parameterMappings != null) { Object[] parameterArray = new Object[parameterMappings.size()]; ParameterMapping parameterMapping = null; Object value = null; Object parameterObject = null; MetaObject metaObject = null; PropertyTokenizer prop = null; String propertyName = null; String[] names = null; for (int i = 0; i < parameterMappings.size(); i++) { parameterMapping = parameterMappings.get(i); if (parameterMapping.getMode() != ParameterMode.OUT) { propertyName = parameterMapping.getProperty(); names = propertyName.split("\\."); if (propertyName.indexOf(".") != -1 && names.length == 2) { parameterObject = boundSql.getAdditionalParameter(names[0]); propertyName = names[1]; } else if (propertyName.indexOf(".") != -1 && names.length == 3) { parameterObject = boundSql.getAdditionalParameter(names[0]); // map if (parameterObject instanceof Map) { parameterObject = ((Map) parameterObject).get(names[1]); } propertyName = names[2]; } else { parameterObject = boundSql.getAdditionalParameter(propertyName); } if (null == parameterObject) { parameterObject = getFieldValueByFieldName(propertyName, obj); } prop = new PropertyTokenizer(propertyName); if (parameterObject == null) { value = null; } else if (ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass())) { value = parameterObject; } else if (boundSql.hasAdditionalParameter(propertyName)) { value = boundSql.getAdditionalParameter(propertyName); } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) { value = boundSql.getAdditionalParameter(prop.getName()); } else { value = metaObject == null ? null : metaObject.getValue(propertyName); } parameterArray[i] = value; } parameterObject = null; } int i = 0; while (sql.indexOf("?") != -1) { if (null == parameterArray[i]) { throw new RuntimeException("mybatis获取sql异常"); } if (parameterArray[i] instanceof String) { sql = sql.replaceFirst("\\?", "'" + parameterArray[i].toString() + "'"); } else { sql = sql.replaceFirst("\\?", parameterArray[i].toString()); } i++; } int index; StringBuilder sb = new StringBuilder(); for (String s : sql.split("\n")) { index = s.indexOf("--"); if (-1 == index) { index = s.length(); } sb.append(s.substring(0, index).replace("\t", " ").trim()).append(" "); } return sb.toString().replaceAll("(\r?\n(\\s*\r?\n)+)", " ").replaceAll("\n", " ").replaceAll("\r", " ").replaceAll(" ", " ").replaceAll(" ", " ").replaceAll(" ", " "); } return sql; } /** * 根据属性名获取属性值 * * @param fieldName * @param object * @return */ public static Object getFieldValueByFieldName(String fieldName, Object object) { try { Field field = object.getClass().getDeclaredField(fieldName); //设置对象的访问权限，保证对private的属性的访问 field.setAccessible(true); return field.get(object); } catch (Exception e) { new RuntimeException(e); return null; } } }
项目结构
