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,测试);