[Mybatis]仅使用Mybatis的SQL模板分析
前言
因为公司的项目历史设计问题层出不穷，没有时间去改变新项目，旧项目也无法改变。有很多非法操作需要生产，只能通过数据库脚本更改数据。
每次在开发过程中，都会抛出一个工作单，让生产每天更改数据和编写脚本。这怎么能容忍？
几个季度后，我受不了了。我一拍桌子，就决定要气.从头开始开发一个脚本执行工具来管理编写的脚本，这些脚本可以相互关联并跨数据库执行。解决临时脚本编写和脚本共享的问题。
在设计中，脚本执行工具的脚本一开始只是一个占位符，比如“{parameter name}”，然后就是replaceAll。想了想，为什么不能像mybatis一样解析脚本，或者直接使用mybatis的xml模板解析功能呢？
————————————————————————————————
正文
通过遍历代码，我们可以发现mybatis在XMLLanguageDriver类的createSqlSource中实现了SQL解析，并且支持使用脚本/脚本来执行字符串模板。
好吧，在这种情况下，试着按照这里的代码来。
如果直接修改XPathParser，太复杂了，还要管理内部逻辑。这里，就拿模拟法来说。
构建配置Configuration
首先，您需要mybatis的xml最低配置，它在这里写成字符串：
字符串EMPTY_XML='xml版本=\'1.0 '编码=\'UTF-8\' \r\n' //
！DOCTYPE配置\r\n' //
PUBLIC \ '-//my batis . org//DTD Config 3.0//EN \ ' \ r \ n '//
\ ' http://mybatis . org/dtd/my batis-3-config . dtd \ ' \ r \ n '//
配置\r\n' //
/configuration '；
然后根据mybatis的实现构建配置。
单击查看代码input stream input stream=new bytearray input stream(empty _ XML . getbytes(standard charset . utf _ 8))；
XMLConfigBuilder XMLConfigBuilder=new XMLConfigBuilder(inputStream，null，null)；
配置配置=xmlconfigbuilder . parse()；
通过配置，您可以创建XPathParser来解析SQL。
单击代码字符串script=' script \从table _ name中选择count (0 ),其中如果test=\' param！=null ' AND num=# { param }/if \ n/script '；
XPath parser parser=new XPath parser(脚本，false，new Properties()，new xmlmapperentitysolver())；
SqlSource source=createSqlSource(配置，parser . evalunode('/script ')，null)；
MapString，String params=new HashMap()；
param.put('param '，' 1 ')；
BoundSql BoundSql=source . getboundsql(params)；
string SQL=BoundSqL . GetSqL()；
结果
由上述代码解析的SQL是从表名中选择计数(0)，其中1=1，如果有参数号，则num=1。
是一个带有占位符的高度安全的预编译SQL。使用它时，需要构建prepareStatement，然后通过jdbc传入PrepareStatement并手动设置参数。
自动化prepareStatement参数设置
参考Mybatis的默认值。
arameterHandler类的setParameters
创建PrepareStatement可以通过如springJdbc进行构建：PreparedStatement ps = jdbcTemplate.getDataSource().getConnection().prepareStatement(boundSql.getSql());
然后调用DefaultParameterHandler的setParameters即可
获取完整SQL
参照mybatis-plus的PerformanceInterceptor类，该类可通过Statement获取SQL
需要的代码如下：
点击查看代码
/**
* COPY FROM {@link PerformanceInterceptor}
*/
private static final String DruidPooledPreparedStatement = "com.alibaba.druid.pool.DruidPooledPreparedStatement";
private static final String T4CPreparedStatement = "oracle.jdbc.driver.T4CPreparedStatement";
private static final String OraclePreparedStatementWrapper = "oracle.jdbc.driver.OraclePreparedStatementWrapper";
private Method oracleGetOriginalSqlMethod;
private Method druidGetSQLMethod;
/**
* 获取原始SQL， COPY FROM {@link PerformanceInterceptor}
*/
private String getOriginSql(PreparedStatement statement) {
String originalSql = null;
String stmtClassName = statement.getClass().getName();
if (DruidPooledPreparedStatement.equals(stmtClassName)) {
try {
if (druidGetSQLMethod == null) {
Class clazz = Class.forName(DruidPooledPreparedStatement);
druidGetSQLMethod = clazz.getMethod("getSql");
}
Object stmtSql = druidGetSQLMethod.invoke(statement);
if (stmtSql instanceof String) {
originalSql = (String) stmtSql;
}
} catch (Exception e) {
e.printStackTrace();
}
} else if (T4CPreparedStatement.equals(stmtClassName) || OraclePreparedStatementWrapper.equals(stmtClassName)) {
try {
if (oracleGetOriginalSqlMethod != null) {
Object stmtSql = oracleGetOriginalSqlMethod.invoke(statement);
if (stmtSql instanceof String) {
originalSql = (String) stmtSql;
}
} else {
Class clazz = Class.forName(stmtClassName);
oracleGetOriginalSqlMethod = getMethodRegular(clazz, "getOriginalSql");
if (oracleGetOriginalSqlMethod != null) {
// OraclePreparedStatementWrapper is not a public class, need set this.
oracleGetOriginalSqlMethod.setAccessible(true);
if (null != oracleGetOriginalSqlMethod) {
Object stmtSql = oracleGetOriginalSqlMethod.invoke(statement);
if (stmtSql instanceof String) {
originalSql = (String) stmtSql;
}
}
}
}
} catch (Exception e) {
// ignore
}
}
if (originalSql == null) {
originalSql = statement.toString();
}
return originalSql;
}
获取出来的SQL在mybatis中为com.mxxxx : SELECT....，大概是这样，需要截取：
点击查看代码
/**
* 获取sql语句开头部分, COPY FROM {@link PerformanceInterceptor}
*/
private int indexOfSqlStart(String sql) {
String upperCaseSql = sql.toUpperCase();
SetInteger set = new HashSet();
set.add(upperCaseSql.indexOf("SELECT "));
set.add(upperCaseSql.indexOf("UPDATE "));
set.add(upperCaseSql.indexOf("INSERT "));
set.add(upperCaseSql.indexOf("DELETE "));
set.remove(-1);
if (CollectionUtils.isEmpty(set)) {
return -1;
}
ListInteger list = new ArrayList(set);
list.sort(Comparator.naturalOrder());
return list.get(0);
}
这样就获取出来完整的SQL了，可喜可贺。但是！
Oracle数据库使用该方法无效，打印出来还是预编译SQL
完整SQL第二方案
在第一个方案执行失败的情况下（可遍历字符串看有没有），增加该方案：
参照：https://www.cnblogs.com/aipan/p/7237854.html
增加LoggableStatement，改造DefaultParameterHandler的setParameters（Copy出来作为新方法）：
点击查看代码
@SuppressWarnings({"unchecked", "rawtypes"})
private LoggableStatement buildPreparedStatement(JdbcTemplate jdbcTemplate, BoundSql boundSql) throws SQLException {
PreparedStatement ps = jdbcTemplate.getDataSource().getConnection().prepareStatement(boundSql.getSql());
// 改造点
LoggableStatement ls = new LoggableStatement(ps, boundSql.getSql());
ListParameterMapping parameterMappings = boundSql.getParameterMappings();
Object parameterObject = boundSql.getParameterObject();
if (parameterMappings != null) {
for (int i = 0; i parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
Object value;
String propertyName = parameterMapping.getProperty();
if (boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);
} else if (parameterObject == null) {
value = null;
} else {
MetaObject metaObject = configuration.newMetaObject(parameterObject);
value = metaObject.getValue(propertyName);
}
TypeHandler typeHandler = parameterMapping.getTypeHandler();
JdbcType jdbcType = parameterMapping.getJdbcType();
if (value == null jdbcType == null) {
jdbcType = configuration.getJdbcTypeForNull();
}
try {
typeHandler.setParameter(ls, i + 1, value, jdbcType);
} catch (TypeException | SQLException e) {
throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
}
}
}
}
return ls;
}
在获取完整SQL失败后，即可通过LoggableStatement来获取SQL：
if (!isCorrectGetSql(boundSql, originalSql)) {
originalSql = statement.getQueryString();
}
————————————————————————————
上面提到项目的该类完整代码：
点击查看代码
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.nio.charset.StandardCharsets;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import org.apache.ibatis.builder.xml.XMLConfigBuilder;
import org.apache.ibatis.builder.xml.XMLMapperEntityResolver;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.parsing.XPathParser;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.scripting.xmltags.XMLScriptBuilder;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;
import org.apache.ibatis.type.TypeHandler;
import org.springframework.jdbc.core.JdbcTemplate;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
/**
* 使用Mybatis的XML解析来进行SQL构建的执行器
*/
public class MybatisTemplateSqlExcutor {
/**
* 脚本模板
*/
private static final String SCRIPT_TEMPLATE = "script\n%s\n/script";
/**
* MP环境配置
*/
private Configuration configuration;
/**
* COPY FROM {@link PerformanceInterceptor}
*/
private static final String DruidPooledPreparedStatement = "com.alibaba.druid.pool.DruidPooledPreparedStatement";
private static final String T4CPreparedStatement = "oracle.jdbc.driver.T4CPreparedStatement";
private static final String OraclePreparedStatementWrapper = "oracle.jdbc.driver.OraclePreparedStatementWrapper";
private Method oracleGetOriginalSqlMethod;
private Method druidGetSQLMethod;
/**
* 构造器，初始化MP环境配置
*/
public MybatisTemplateSqlExcutor() {
InputStream inputStream = new ByteArrayInputStream(EMPTY_XML.getBytes(StandardCharsets.UTF_8));
XMLConfigBuilder xmlConfigBuilder = new XMLConfigBuilder(inputStream, null, null);
configuration = xmlConfigBuilder.parse();
}
public String parseSql(JdbcTemplate jdbcTemplate, String sqlTemplate, MapString, Object params) throws SQLException {
String script = String.format(SCRIPT_TEMPLATE, sqlTemplate);
XPathParser parser = new XPathParser(script, false, new Properties(), new XMLMapperEntityResolver());
SqlSource source = createSqlSource(configuration, parser.evalNode("/script"), Map.class);
BoundSql boundSql = source.getBoundSql(params);
LoggableStatement statement = buildPreparedStatement(jdbcTemplate, boundSql);
String originalSql = getOriginSql(statement.getPreparedStatement());
int index = indexOfSqlStart(originalSql);
if (index 0) {
originalSql = originalSql.substring(index);
}
if (!isCorrectGetSql(boundSql, originalSql)) {
originalSql = statement.getQueryString();
}
return originalSql;
}
/**
* 从MP复制过来的脚本解析方法
*/
private SqlSource createSqlSource(Configuration configuration, XNode script, Class parameterType) {
XMLScriptBuilder builder = new XMLScriptBuilder(configuration, script, parameterType);
return builder.parseScriptNode();
}
/**
* 根据BoundSql组装PreparedStatement，用于获取实际SQL
*/
@SuppressWarnings({"unchecked", "rawtypes"})
private LoggableStatement buildPreparedStatement(JdbcTemplate jdbcTemplate, BoundSql boundSql) throws SQLException {
PreparedStatement ps = jdbcTemplate.getDataSource().getConnection().prepareStatement(boundSql.getSql());
LoggableStatement ls = new LoggableStatement(ps, boundSql.getSql());
ListParameterMapping parameterMappings = boundSql.getParameterMappings();
Object parameterObject = boundSql.getParameterObject();
if (parameterMappings != null) {
for (int i = 0; i parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
Object value;
String propertyName = parameterMapping.getProperty();
if (boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);
} else if (parameterObject == null) {
value = null;
} else {
MetaObject metaObject = configuration.newMetaObject(parameterObject);
value = metaObject.getValue(propertyName);
}
TypeHandler typeHandler = parameterMapping.getTypeHandler();
JdbcType jdbcType = parameterMapping.getJdbcType();
if (value == null jdbcType == null) {
jdbcType = configuration.getJdbcTypeForNull();
}
try {
typeHandler.setParameter(ls, i + 1, value, jdbcType);
} catch (TypeException | SQLException e) {
throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
}
}
}
}
return ls;
}
/**
* 获取sql语句开头部分
*/
private int indexOfSqlStart(String sql) {
String upperCaseSql = sql.toUpperCase();
SetInteger set = new HashSet();
set.add(upperCaseSql.indexOf("SELECT "));
set.add(upperCaseSql.indexOf("UPDATE "));
set.add(upperCaseSql.indexOf("INSERT "));
set.add(upperCaseSql.indexOf("DELETE "));
set.remove(-1);
if (CollectionUtils.isEmpty(set)) {
return -1;
}
ListInteger list = new ArrayList(set);
list.sort(Comparator.naturalOrder());
return list.get(0);
}
/**
* 获取原始SQL， COPY FROM {@link PerformanceInterceptor}
*/
private String getOriginSql(PreparedStatement statement) {
String originalSql = null;
String stmtClassName = statement.getClass().getName();
if (DruidPooledPreparedStatement.equals(stmtClassName)) {
try {
if (druidGetSQLMethod == null) {
Class clazz = Class.forName(DruidPooledPreparedStatement);
druidGetSQLMethod = clazz.getMethod("getSql");
}
Object stmtSql = druidGetSQLMethod.invoke(statement);
if (stmtSql instanceof String) {
originalSql = (String) stmtSql;
}
} catch (Exception e) {
e.printStackTrace();
}
} else if (T4CPreparedStatement.equals(stmtClassName) || OraclePreparedStatementWrapper.equals(stmtClassName)) {
try {
if (oracleGetOriginalSqlMethod != null) {
Object stmtSql = oracleGetOriginalSqlMethod.invoke(statement);
if (stmtSql instanceof String) {
originalSql = (String) stmtSql;
}
} else {
Class clazz = Class.forName(stmtClassName);
oracleGetOriginalSqlMethod = getMethodRegular(clazz, "getOriginalSql");
if (oracleGetOriginalSqlMethod != null) {
// OraclePreparedStatementWrapper is not a public class, need set this.
oracleGetOriginalSqlMethod.setAccessible(true);
if (null != oracleGetOriginalSqlMethod) {
Object stmtSql = oracleGetOriginalSqlMethod.invoke(statement);
if (stmtSql instanceof String) {
originalSql = (String) stmtSql;
}
}
}
}
} catch (Exception e) {
// ignore
}
}
if (originalSql == null) {
originalSql = statement.toString();
}
return originalSql;
}
/**
* 获取此方法名的具体 Method
*
* @param clazz class 对象
* @param methodName 方法名
* @return 方法
*/
public Method getMethodRegular(Class clazz, String methodName) {
if (Object.class.equals(clazz)) {
return null;
}
for (Method method : clazz.getDeclaredMethods()) {
if (method.getName().equals(methodName)) {
return method;
}
}
return getMethodRegular(clazz.getSuperclass(), methodName);
}
/**
* 判断是否正确的获取了SQL
*/
private boolean isCorrectGetSql(BoundSql boundSql, String originSql) {
return countQuestionMark(boundSql.getSql()) countQuestionMark(originSql);
}
/**
* 统计占位符
*/
private int countQuestionMark(String sql) {
int result = 0;
for (char c : sql.toCharArray())
if (c == '')
result++;
return result;
}
/**
* 空MP配置模板，用于构建MP环境配置，（放这里是由于博客的编辑器识别问题，会导致高亮错误）
*/
private static final String EMPTY_XML = "xml version=\"1.0\" encoding=\"UTF-8\" \r\n"
+ "!DOCTYPE configuration\r\n"
+ " PUBLIC \"-//mybatis.org//DTD Config 3.0//EN\"\r\n"
+ " \"http://mybatis.org/dtd/mybatis-3-config.dtd\"\r\n"
+ "configuration\r\n"
+ "/configuration";
}
称之为Excutor是因为这其实是个子类，只提供parseSql
其他
若有更好的方法，请务必告诉我(??????)??
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/126072.html