Can I use MyBatis to generate Dynamic SQL without executing it?
Everyone knows how to use BoundSql.getSql() to get a paramaterized query string from MyBatis, like this:
// get parameterized query
MappedStatement ms = configuration.getMappedStatement("MyMappedStatementId");
BoundSql boundSql = ms.getBoundSql(parameters);
System.out.println("SQL" + boundSql.getSql());
// SELECT species FROM animal WHERE name IN (?, ?) or id = ?
But now you need the other half of the equation, the list of values that correspond to the question marks:
// get parameters
List<ParameterMapping> boundParams = boundSql.getParameterMappings();
String paramString = "";
for(ParameterMapping param : boundParams) {
paramString += boundSql.getAdditionalParameter(param.getProperty()) + ";";
}
System.out.println("params:" + paramString);
// "Spot;Fluffy;42;"
Now you can serialize it to send elsewhere to be run, or you can print it to a log so you can stitch them together and run the query manually.
*code not tested, might be minor type issues or the like
mybatis version is 3.4.5
Util Class
To convert mapper to sql, need mapper interface class,method name,paramters,and sqlSession.
package util;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.regex.Matcher;
import org.apache.ibatis.binding.MapperMethod.MethodSignature;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.util.CollectionUtils;
/**
* @author zwxbest - 19-4-25
*/
public class SqlUtil {
public static String showSql(SqlSession sqlSession, Class mapperInterface, String methodName,
Object[] params) {
Configuration configuration = sqlSession.getConfiguration();
MappedStatement ms = configuration.getMappedStatement(
mapperInterface.getName() + "." + methodName);
Method sqlMethod = null;
//find method equals methodName
for (Method method : mapperInterface.getDeclaredMethods()) {
if (method.getName().equals(methodName)) {
sqlMethod = method;
break;
}
}
if (sqlMethod == null) {
throw new RuntimeException("mapper method is not found");
}
MethodSignature method = new MethodSignature(configuration, mapperInterface, sqlMethod);
Object paramObject = method.convertArgsToSqlCommandParam(params);
BoundSql boundSql = ms.getBoundSql(paramObject);
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql
.getParameterMappings();
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (!CollectionUtils.isEmpty(parameterMappings) && parameterObject != null) {
TypeHandlerRegistry typeHandlerRegistry = configuration
.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?",
Matcher.quoteReplacement(getParameterValue(parameterObject)));
} else {
MetaObject metaObject = configuration.newMetaObject(
parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
sql = sql
.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql
.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
} else {
sql = sql.replaceFirst("\\?", "missing");
}
}
}
}
return sql;
}
/**
* if param's type is `String`,add single quotation<br>
*
* if param's type is `datetime`,convert to string and quote <br>
*/
private static String getParameterValue(Object obj) {
String value = null;
if (obj instanceof String) {
value = "'" + obj.toString() + "'";
} else if (obj instanceof Date) {
DateFormat formatter = DateFormat
.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
value = "'" + formatter.format(new Date()) + "'";
} else if (obj instanceof LocalDateTime) {
value = "\'" + ((LocalDateTime) obj)
.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) + "\'";
} else {
if (obj != null) {
value = obj.toString();
} else {
value = "";
}
}
return value;
}
}
call example
sqlSession is injected by Spring .
@Autowired
private SqlSession sqlSession;
String sql = SqlUtil
.showSql(sqlSession, PromotionCodeMapper.class, "selectByPromotionCodeForUpdate",
new Object[]{"111"});
log.warn(sql);
Although MyBatis was designed to execute the query after it builds it, you can make use of it's configuration and a little bit of "inside knowledge" to get to what you need.
MyBatis is a very nice framework, unfortunately it lacks on the documentations side so the source code is you friend. If you dig around you should bump into these classes: org.apache.ibatis.mapping.MappedStatement and org.apache.ibatis.mapping.BoundSql which are key players into building the dynamic SQL. Here is a basic usage example:
MySQL table user with this data in it:
name login
----- -----
Andy a
Barry b
Cris c
User class:
package pack.test;
public class User {
private String name;
private String login;
// getters and setters ommited
}
UserService interface:
package pack.test;
public interface UserService {
// using a different sort of parameter to show some dynamic SQL
public User getUser(int loginNumber);
}
UserService.xml mapper file:
<mapper namespace="pack.test.UserService">
<select id="getUser" resultType="pack.test.User" parameterType="int">
<!-- dynamic change of parameter from int index to login string -->
select * from user where login = <choose>
<when test="_parameter == 1">'a'</when>
<when test="_parameter == 2">'b'</when>
<otherwise>'c'</otherwise>
</choose>
</select>
</mapper>
sqlmap-config.file:
<configuration>
<settings>
<setting name="lazyLoadingEnabled" value="false" />
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost/test"/>
<property name="username" value="..."/>
<property name="password" value="..."/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="pack/test/UserService.xml"/>
</mappers>
</configuration>
AppTester to show the result:
package pack.test;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class AppTester {
private static String CONFIGURATION_FILE = "sqlmap-config.xml";
public static void main(String[] args) throws Exception {
Reader reader = null;
SqlSession session = null;
try {
reader = Resources.getResourceAsReader(CONFIGURATION_FILE);
session = new SqlSessionFactoryBuilder().build(reader).openSession();
UserService userService = session.getMapper(UserService.class);
// three users retreived from index
for (int i = 1; i <= 3; i++) {
User user = userService.getUser(i);
System.out.println("Retreived user: " + user.getName() + " " + user.getLogin());
// must mimic the internal statement key for the mapper and method you are calling
MappedStatement ms = session.getConfiguration().getMappedStatement(UserService.class.getName() + ".getUser");
BoundSql boundSql = ms.getBoundSql(i); // parameter for the SQL statement
System.out.println("SQL used: " + boundSql.getSql());
System.out.println();
}
} finally {
if (reader != null) {
reader.close();
}
if (session != null) {
session.close();
}
}
}
}
And the result:
Retreived user: Andy a
SQL used: select * from user where login = 'a'
Retreived user: Barry b
SQL used: select * from user where login = 'b'
Retreived user: Cris c
SQL used: select * from user where login = 'c'