mybatis获取执行的sql(以mysql为例)
第一种方法
准备工作
-
前提使用的ssm项目,一张demo表,字段id(int)主键自增,name(varchar)
-
准备好两个工具类
MyBatisSqlUtils
package com.fcmap.ssm.config;
import java.util.List;
import java.util.Map;
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.SystemMetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.SqlSessionFactory;
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, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY);
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,SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY).getValue(propertyName.substring(prop.getName().length()));
}
}
else
{
value = metaObject == null ? null : metaObject.getValue(propertyName);
}
parameterArray[i] = value;
}
}
ibatisSql.setParameters(parameterArray);
}
return ibatisSql;
}
}
MyBatisSql
package com.fcmap.ssm.config;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
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");
}
}
示例1:不带参数
前提测试类模板
@ContextConfiguration("classpath:/spring-mybatis.xml")
@RunWith(SpringJUnit4ClassRunner.class)
//这里可以声明一个事务管理 每个单元测试都进行事务回滚 无论成功与否
@TransactionConfiguration(defaultRollback = true)
@Transactional
public class DaoTest {
@Autowired
private DemoDao demoDao;
}
说明:可以选择事务回滚,操作增删改需要回滚,否则会误操作数据,造成不必要的麻烦
如果,不需要回滚,在测试方法上贾@Rollback(false)注解
- dao层的方法
@Select("select * from demo")
public List<Demo> findAll1();
- 测试方法
@Test
public void testsqlString1() {
List<Demo> demoList = new ArrayList<Demo> ();
String sql = sqlSessionFactory.getConfiguration().
getMappedStatement("com.fcmap.ssm.dao.DemoDao.findAll1")
.getBoundSql(null).getSql();
System.out.println(sql);
}
- 控制台输出
select * from demo
示例2:带参数,但是参数是?
- dao层的方法
public Boolean insertDemo(Demo demo);
- mapper.xml
<insert id="insertDemo"
parameterType="com.fcmap.ssm.domain.Demo">
insert into DEMO (id,name) values (#{id},#{name});
</insert>
- 测试方法
@Test
public void testsqlString3() {
Map<String,Object> map = new HashMap<String,Object>();
Demo demo = new Demo(1,"测试");
// map.put("demo", demo);
map.put("id",1);
map.put("name","测试");
String sql = sqlSessionFactory.getConfiguration().
getMappedStatement("com.fcmap.ssm.dao.DemoDao.insertDemo")
.getBoundSql(map).getSql();
System.out.println(sql);
}
- 控制台输出
insert into DEMO (id,name) values (?,?);
示例3:参数动态的显示在sql中
- dao层方法
public Boolean insertDemo(Demo demo);
- mapper.xml
<insert id="insertDemo"
parameterType="com.fcmap.ssm.domain.Demo">
insert into DEMO (id,name) values (#{id},#{name});
</insert>
- 测试方法
@Test
public void testsqlString4() {
Map<String,Object> map = new HashMap<String,Object>();
Demo demo = new Demo(1,"测试");
// map.put("demo", demo);
map.put("id",1);
map.put("name","测试");
MyBatisSql myBatisSql = MyBatisSqlUtils.getMyBatisSql("com.fcmap.ssm.dao.DemoDao.insertDemo", map, sqlSessionFactory);
System.out.println(myBatisSql);
}
- 控制台输出
insert into DEMO (id,name) values (1,测试);
第二种方法
准备工作
工具类
package com.fcmap.ssm.config;
import org.apache.ibatis.annotations.Param;
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.SystemMetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandlerRegistry;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SqlHelper {
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
/**
* 通过接口获取sql
*
* @param mapper
* @param methodName
* @param args
* @return
*/
public static String getMapperSql(Object mapper, String methodName, Object... args) {
MetaObject metaObject = SystemMetaObject.forObject(mapper);
SqlSession session = (SqlSession) metaObject.getValue("h.sqlSession");
Class mapperInterface = (Class) metaObject.getValue("h.mapperInterface");
String fullMethodName = mapperInterface.getCanonicalName() + "." + methodName;
if (args == null || args.length == 0) {
return getNamespaceSql(session, fullMethodName, null);
} else {
return getMapperSql(session, mapperInterface, methodName, args);
}
}
/**
* 通过Mapper方法名获取sql
*
* @param session
* @param fullMapperMethodName
* @param args
* @return
*/
public static String getMapperSql(SqlSession session, String fullMapperMethodName, Object... args) {
if (args == null || args.length == 0) {
return getNamespaceSql(session, fullMapperMethodName, null);
}
String methodName = fullMapperMethodName.substring(fullMapperMethodName.lastIndexOf('.') + 1);
Class mapperInterface = null;
try {
mapperInterface = Class.forName(fullMapperMethodName.substring(0, fullMapperMethodName.lastIndexOf('.')));
return getMapperSql(session, mapperInterface, methodName, args);
} catch (ClassNotFoundException e) {
throw new IllegalArgumentException("参数" + fullMapperMethodName + "无效！");
}
}
/**
* 通过Mapper接口和方法名
*
* @param session
* @param mapperInterface
* @param methodName
* @param args
* @return
*/
public static String getMapperSql(SqlSession session, Class mapperInterface, String methodName, Object... args) {
String fullMapperMethodName = mapperInterface.getCanonicalName() + "." + methodName;
if (args == null || args.length == 0) {
return getNamespaceSql(session, fullMapperMethodName, null);
}
Method method = getDeclaredMethods(mapperInterface, methodName);
Map params = new HashMap();
final Class<?>[] argTypes = method.getParameterTypes();
for (int i = 0; i < argTypes.length; i++) {
if (!RowBounds.class.isAssignableFrom(argTypes[i]) && !ResultHandler.class.isAssignableFrom(argTypes[i])) {
String paramName = "param" + String.valueOf(params.size() + 1);
paramName = getParamNameFromAnnotation(method, i, paramName);
params.put(paramName, i >= args.length ? null : args[i]);
}
}
if (args != null && args.length == 1) {
Object _params = wrapCollection(args[0]);
if (_params instanceof Map) {
params.putAll((Map) _params);
}
}
return getNamespaceSql(session, fullMapperMethodName, params);
}
/**
* 通过命名空间方式获取sql
*
* @param session
* @param namespace
* @return
*/
public static String getNamespaceSql(SqlSession session, String namespace) {
return getNamespaceSql(session, namespace, null);
}
/**
* 通过命名空间方式获取sql
*
* @param session
* @param namespace
* @param params
* @return
*/
public static String getNamespaceSql(SqlSession session, String namespace, Object params) {
params = wrapCollection(params);
Configuration configuration = session.getConfiguration();
MappedStatement mappedStatement = configuration.getMappedStatement(namespace);
TypeHandlerRegistry typeHandlerRegistry = mappedStatement.getConfiguration().getTypeHandlerRegistry();
BoundSql boundSql = mappedStatement.getBoundSql(params);
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
String sql = boundSql.getSql();
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 (params == null) {
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(params.getClass())) {
value = params;
} else {
MetaObject metaObject = configuration.newMetaObject(params);
value = metaObject.getValue(propertyName);
}
JdbcType jdbcType = parameterMapping.getJdbcType();
if (value == null && jdbcType == null) jdbcType = configuration.getJdbcTypeForNull();
sql = replaceParameter(sql, value, jdbcType, parameterMapping.getJavaType());
}
}
}
return sql;
}
/**
* 根据类型替换参数
* 仅作为数字和字符串两种类型进行处理，需要特殊处理的可以继续完善这里
*
* @param sql
* @param value
* @param jdbcType
* @param javaType
* @return
*/
private static String replaceParameter(String sql, Object value, JdbcType jdbcType, Class javaType) {
String strValue = String.valueOf(value);
if (jdbcType != null) {
switch (jdbcType) {
//数字
case BIT:
case TINYINT:
case SMALLINT:
case INTEGER:
case BIGINT:
case FLOAT:
case REAL:
case DOUBLE:
case NUMERIC:
case DECIMAL:
break;
//日期
case DATE:
case TIME:
case TIMESTAMP:
//其他，包含字符串和其他特殊类型
default:
strValue = "'" + strValue + "'";
}
} else if (Number.class.isAssignableFrom(javaType)) {
//不加单引号
} else {
strValue = "'" + strValue + "'";
}
return sql.replaceFirst("\\?", strValue);
}
/**
* 获取指定的方法
*
* @param clazz
* @param methodName
* @return
*/
private static Method getDeclaredMethods(Class clazz, String methodName) {
Method[] methods = clazz.getDeclaredMethods();
for (Method method : methods) {
if (method.getName().equals(methodName)) {
return method;
}
}
throw new IllegalArgumentException("方法" + methodName + "不存在！");
}
/**
* 获取参数注解名
*
* @param method
* @param i
* @param paramName
* @return
*/
private static String getParamNameFromAnnotation(Method method, int i, String paramName) {
final Object[] paramAnnos = method.getParameterAnnotations()[i];
for (Object paramAnno : paramAnnos) {
if (paramAnno instanceof Param) {
paramName = ((Param) paramAnno).value();
}
}
return paramName;
}
/**
* 简单包装参数
*
* @param object
* @return
*/
private static Object wrapCollection(final Object object) {
if (object instanceof List) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("list", object);
return map;
} else if (object != null && object.getClass().isArray()) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("array", object);
return map;
}
return object;
}
}
示例1:这种方法可以带出参数显示
- dao层方法
public Boolean insertDemo(Demo demo);
- mapper.xml
<insert id="insertDemo"
parameterType="com.fcmap.ssm.domain.Demo">
insert into DEMO (id,name) values (#{id},#{name});
</insert>
- 测试方法
@Test
public void testsqlString5() {
Map<String,Object> map = new HashMap<String,Object>();
Demo demo = new Demo(1,"测试");
// map.put("demo", demo);
map.put("id",1);
map.put("name","测试");
SqlSession sqlSession = sqlSessionFactory.openSession();
DemoDao mapper = sqlSession.getMapper(DemoDao.class);
String mapperSql = SqlHelper.getMapperSql(
mapper,
"insertDemo",
map);
System.out.println(mapperSql);
}
- 控制台打印
insert into DEMO (id,name) values (1,'测试');
4164
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
