运行期获取MyBatis执行的SQL及参数

public class MyBatisSqlUtils 
{
	/**
	 * 运行期获取MyBatis执行的SQL及参数
	 * @param id             Mapper xml 文件里的select Id
	 * @param parameterMap   参数
	 * @param sqlSessionFactory 
	 * @return
	 */
	public static MyBatisSql getMyBatisSql(String id, Map<String,Object> parameterMap,SqlSessionFactory sqlSessionFactory) {  
	    MyBatisSql ibatisSql = new MyBatisSql();  
	    MappedStatement ms = sqlSessionFactory.getConfiguration().getMappedStatement(id); 
	    BoundSql boundSql = ms.getBoundSql(parameterMap);  
	    ibatisSql.setSql(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 = parameterMap.get(names[0]);
	        		propertyName = names[1];
	        	}
	        	else if(propertyName.indexOf(".") != -1 && names.length == 3)
	        	{
	        		parameterObject = parameterMap.get(names[0]); // map
	        		if(parameterObject instanceof Map)
	        		{
	        			parameterObject = ((Map)parameterObject).get(names[1]);
	        		}
	        		propertyName = names[2];
	        	}
	        	else
	        	{
		        	parameterObject = parameterMap.get(propertyName);
	        	}
	            metaObject = parameterMap == null ? null : MetaObject.forObject(parameterObject);  
	            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());  
	              if (value != null) 
	              {  
	                value = MetaObject.forObject(value).getValue(propertyName.substring(prop.getName().length()));  
	              }  
	            } 
	            else 
	            {  
	              value = metaObject == null ? null : metaObject.getValue(propertyName);  
	            }  
	            parameterArray[i] = value;  
	          }  
	        }  
	        ibatisSql.setParameters(parameterArray);  
	    }  
	    return ibatisSql;  
	}

public class MyBatisSql 
{

	/**
	 * 运行期 sql
	 */
	private String sql;
	
	/**
	 * 参数 数组
	 */
    private Object[] parameters;
  
    public void setSql(String sql) {  
        this.sql = sql;  
    }  
  
    public String getSql() {  
        return sql;  
    }  
  
    public void setParameters(Object[] parameters) {  
        this.parameters = parameters;  
    }  
  
    public Object[] getParameters() {  
        return parameters;  
    }  
    
	@Override
	public String toString() {
		if(parameters == null || sql == null)
		{
			return "";
		}
		List<Object> parametersArray = Arrays.asList(parameters);
		List<Object> list = new ArrayList<Object>(parametersArray);
		while(sql.indexOf("?") != -1 && list.size() > 0 && parameters.length > 0)
		{
			sql = sql.replaceFirst("\\?", list.get(0).toString());
			list.remove(0);
		}
		return sql.replaceAll("(\r?\n(\\s*\r?\n)+)", "\r\n");
	}

以上贴的是实现代码，下面贴一段代码演示一下如何使用:

一、Mapper 文件

public interface AlarmMapper {

	public List<Alarm> queryAlarmData(@Param(value = "filter") Filter filter,
			@Param(value = "alarmStatus") AlarmStatus alarmStatus,
			@Param(value = "startDate") Date startDate,
			@Param(value = "endDate") Date endDate,
			@Param(value = "regionId") String regionId,
			@Param(value = "paramter") Map<String, Object> paramter,
			@Param(value = "startRow") int startRow,
			@Param(value = "endRow") int endRow) throws SQLException;
}

二、Mapper 的xml文件此处就不写了,select 的ID 为 queryAlarmData

三、在service层调用 queryAlarmData方法

public List<Alarm> queryAlarmData(Filter filter, AlarmStatus alarmStatus,
			Date start, Date end, String regionId, Map<String, Object> paramter,int startRow, int endRow) {
		try {
			List<Alarm> alarms = mapper.queryAlarmData(filter,
					alarmStatus, start, end, regionId,paramter, startRow, endRow);
			Map<String,Object> parameterMap = new HashMap<String,Object>(); //存储参数
			// key 要与Mapper中的参数名一致
			parameterMap.put("filter", filter);
			parameterMap.put("alarmStatus", alarmStatus);
			parameterMap.put("startDate", start);
			parameterMap.put("endDate", end);
			parameterMap.put("regionId", regionId);
			parameterMap.put("paramter", paramter);
			parameterMap.put("startRow", startRow);
			parameterMap.put("endRow", endRow);
			logger.info(MyBatisSqlUtils.getMyBatisSql("queryAlarmData", parameterMap, sqlSessionFactory).toString());
			return alarms;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

四、其实还可以用log4j来实现，将在下一篇博客贴出具体的实现代码(利用Spring的Aop以及自定义注解,实现系统日志功能)

五、欢迎各位指出本文的不妥之处,欢迎拍砖

评论

5 楼 yqy563930552 2014-09-17  

	<select id="listByID" resultType="sql.entity.User" parameterType="java.util.List">
		SELECT
		<include refid="all_column" />
		FROM BAOFOO_ADMIN.admin_login_user
		WHERE user_id in
		<foreach collection="list" index="index" item="tag" open="(" separator="," close=")">
			#{tag}
		</foreach>
	</select>

当我的参数是个List对象的时候，我该怎么去sql和参数呢，，貌似博主的方法不管用了

4 楼 xuke6677 2013-09-23  

参数个数，类型不定，是从页面传过来的。我带的是map参数

3 楼 xuke6677 2013-09-23  

没有判断参数类型，拼接的sql无法执行。如下
select * from users where  username=张三  
请修正，求思路

2 楼 cfqlove 2012-06-05  

wufabeishang 写道

请问log4j怎么实现的啊？也能拦截到SQL执行的参数吗？

log4j.debug=true
log4j.rootLogger=INFO, stdout, alarmFile

# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p %d %C: %m%n

log4j.logger.org.junit=DEBUG
log4j.logger.junit=DEBUG

# ehcache logger config
log4j.logger.net.sf.ehcache=DEBUG

# ibatis logger config
log4j.logger.org.apache.mybatis=DEBUG
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.java.sql=DEBUG


# alarm logger config
log4j.logger.com.boco.monitor.*=INFO, alarmFile
log4j.appender.alarmFile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.alarmFile.File=${catalina.home}/alarm_logs/alarm_log.log
log4j.appender.alarmFile.layout=org.apache.log4j.PatternLayout
log4j.appender.alarmFile.layout.ConversionPattern=%5p %d %C: %m%n

# flex client logger config
log4j.logger.com.boco.monitor.flex.action.ClientLogger=DEBUG, clientLog
log4j.appender.clientLog=org.apache.log4j.DailyRollingFileAppender
log4j.appender.clientLog.File=${catalina.home}/client_logs/client_log.log
log4j.appender.clientLog.layout=org.apache.log4j.PatternLayout
log4j.appender.clientLog.layout.ConversionPattern=%5p %d %C: %m%n

这是我项目的log4j配置，你可以参考下。

1 楼 wufabeishang 2012-06-04  

请问log4j怎么实现的啊？也能拦截到SQL执行的参数吗？