日积月累-MyBatis动态解析SQL（mybatis动态数据表）

在工作中，我们经常会遇到需要动态生成SQL场景（譬如：由SQL语句生成API）。一般有如下三种做法

1. 通过代码拼接。
2. 使用模版语言。例如：freeMarker，beetl等模版语言。
3. 使用MyBatis。

常用的做法有第二和第三两种。下面参考MyBatis生成动态SQL方式写了简易动态SQL解析器，为后面遇到动态SQL解析的场景提供一种方法或者思路。

package com.fz.gateway.datasharing.sql;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.scripting.xmltags.XMLScriptBuilder;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

/**
 * 动态sql解析器
 * 使用mybatis 来解析sql语句。
 * 注意：在写sql模版时，语句中如果包含>、<等符号，需要使用>、<；替换。
 * 
 * 参考：
 * https://pdai.tech/md/framework/orm-mybatis/mybatis-y-dynamic-sql.html
 * https://wuwenze.com/archives/1054/
 * @author Future.Zhang
 *
 */
public class DynamicSqlAnalyzer {

	private BoundSql boundSql;
	private Map<String, Object> params;
	
	public DynamicSqlAnalyzer(String sqlTemplate, Map<String, Object> params) {
		
		if(sqlTemplate == null || "".equals(sqlTemplate)) {
			throw new RuntimeException("sql模版不能为空！");
		}
		
		this.params = params;
		
		sqlTemplate = "<script>" + sqlTemplate + "</script>";
		
		XPathParser xpathParser = new XPathParser(sqlTemplate);
		XNode xnode = xpathParser.evalNode("script");
		
		XMLScriptBuilder builder = new XMLScriptBuilder(new Configuration(), xnode);
		SqlSource sqlSource = builder.parseScriptNode();
		
		boundSql = sqlSource.getBoundSql(params);
		
	}
	
	/**
	 * 获取需要执行的SQL语句。
	 * 如果脚本中sql参数使用的是#{}的形式，那么渲染出来的sql语句中将会使用问号占位符替换
	 * @return
	 */
	public String getSql() {
		return boundSql.getSql();
	}
	
	/**
	 * 获取执行SQL语句需要的注入参数。
	 * 返回的参数清单与sql中的问号占位符一致
	 * @return
	 */
	public List<ParameterAndValueMapping> getSqlParameterMappings(){
		List<ParameterAndValueMapping> result = new ArrayList<ParameterAndValueMapping>();
		
		List<ParameterMapping> mappings = boundSql.getParameterMappings();
		if(mappings == null || mappings.size() == 0) {
			return result;
		}
		
		for(ParameterMapping mapping:mappings) {
			ParameterAndValueMapping pv = new ParameterAndValueMapping();
			pv.setExpression(mapping.getExpression());
			pv.setJavaType(mapping.getJavaType());
			pv.setJdbcType(mapping.getJdbcType());
			pv.setJdbcTypeName(mapping.getJdbcTypeName());
			pv.setMode(mapping.getMode());
			pv.setNumericScale(mapping.getNumericScale());
			pv.setProperty(mapping.getProperty());
			pv.setTypeHandler(mapping.getTypeHandler());
			if(params != null && params.size() > 0) {
				pv.setValue(params.get(pv.getProperty()));
			}
			
			result.add(pv);
		}
		
		return result;
	}
	
	class ParameterAndValueMapping {

		private String property;
		private ParameterMode mode;
		private Class<?> javaType = Object.class;
		private JdbcType jdbcType;
		private Integer numericScale;
		private TypeHandler<?> typeHandler;
		private String jdbcTypeName;
		private String expression;
		private Object value;
		
		public String getProperty() {
			return property;
		}
		public void setProperty(String property) {
			this.property = property;
		}
		public ParameterMode getMode() {
			return mode;
		}
		public void setMode(ParameterMode mode) {
			this.mode = mode;
		}
		public Class<?> getJavaType() {
			return javaType;
		}
		public void setJavaType(Class<?> javaType) {
			this.javaType = javaType;
		}
		public JdbcType getJdbcType() {
			return jdbcType;
		}
		public void setJdbcType(JdbcType jdbcType) {
			this.jdbcType = jdbcType;
		}
		public Integer getNumericScale() {
			return numericScale;
		}
		public void setNumericScale(Integer numericScale) {
			this.numericScale = numericScale;
		}
		public TypeHandler<?> getTypeHandler() {
			return typeHandler;
		}
		public void setTypeHandler(TypeHandler<?> typeHandler) {
			this.typeHandler = typeHandler;
		}
		public String getJdbcTypeName() {
			return jdbcTypeName;
		}
		public void setJdbcTypeName(String jdbcTypeName) {
			this.jdbcTypeName = jdbcTypeName;
		}
		public String getExpression() {
			return expression;
		}
		public void setExpression(String expression) {
			this.expression = expression;
		}
		public Object getValue() {
			return value;
		}
		public void setValue(Object value) {
			this.value = value;
		}
		@Override
		public String toString() {
			final StringBuilder sb = new StringBuilder("ParameterMapping{");
		    sb.append("property='").append(property).append('\'');
		    sb.append(", mode=").append(mode);
		    sb.append(", javaType=").append(javaType);
		    sb.append(", jdbcType=").append(jdbcType);
		    sb.append(", numericScale=").append(numericScale);
		    sb.append(", jdbcTypeName='").append(jdbcTypeName).append('\'');
		    sb.append(", expression='").append(expression).append('\'');
		    sb.append(", value=").append(value);
		    sb.append('}');
		    return sb.toString();
		}
		
		

	}
	
	public static void main(String[] args) {
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", 1);
		params.put("name", "");
		
		String sql = "select * from test1 where 1=1 and id=#{id}"
				     +"<if test=\"name != null and name !=''\">"
		             +" and name = #{name}"
		             +"</if>";
		DynamicSqlAnalyzer anlyzer = new DynamicSqlAnalyzer(sql, params);
		
		System.out.println(anlyzer.getSql());
		//输出的SQL结果：select * from test1 where 1=1 and id=?
		System.out.println(anlyzer.getSqlParameterMappings());
		//输出问号占位符位置与对应的值，其中property表示参数名称，value表示参数的值
		//ParameterMapping{property='id', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, jdbcTypeName='null', expression='null', value=1}
	}
	
}