I have some complex queries to build with a number of optional filters, for which MyBatis seems like an ideal candidate for generating dynamic SQL.
However, I still want my query to execute in the same framework as the rest of the application (which is not using MyBatis).
So what I was hoping to do was use MyBatis strictly for generating the SQL, but from there using the rest of my app to actually execute it. Is this possible? If so, how?
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'
List<ParameterMapping> boundParams = boundSql.getParameterMappings(); for (int i = 0; i < boundParams.size(); i++) { preparedStatement.setObject(i + 1, valuesMap.get(boundParams.get(i).getProperty())); } –
Inerrant 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
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;
}
}
sqlSession is injected by Spring .
@Autowired
private SqlSession sqlSession;
String sql = SqlUtil
.showSql(sqlSession, PromotionCodeMapper.class, "selectByPromotionCodeForUpdate",
new Object[]{"111"});
log.warn(sql);
public static void main(String[] args) throws Exception {
String script = "<script>select * from table where 1 = 1<if test='id != null'>and id = ${id} </if></script>";
System.out.println(buildSql(script));
}
private static String buildSql(String script) {
LanguageDriver languageDriver = new XMLLanguageDriver();
Configuration configuration = new Configuration();
SqlSource sqlSource = languageDriver.createSqlSource(configuration, script, Object.class);
Map<String, String> parameters = new HashMap<>();
parameters.put("id", "1");
BoundSql boundSql = sqlSource.getBoundSql(parameters);
return boundSql.getSql();
}
use ${id} instead of #{id}
result is: select * from table where 1 = 1 and id = 1
select * from table where 1 = 1 and id = '1' –
Gimpel Just to add to Bogdan's correct answer: You need to pass a JavaBean to getBoundSql() with getter's for your interface parameters, if you're interface has a more complex signature.
Let's assume you want to query the user based on the login number and/or the user name. Your interface might look like this:
package pack.test;
public interface UserService {
// using a different sort of parameter to show some dynamic SQL
public User getUser(@Param("number") int loginNumber, @Param("name") String name);
}
I'm leaving out the Mapper code since it's irrelevant for this discussion, but your code in AppTester should become:
[...]
final String name = "Andy";
User user = userService.getUser(i, name);
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(new Object() {
// provide getters matching the @Param's in the interface declaration
public Object getNumber() {
return i;
}
public Object getName() {
return name;
}
});
System.out.println("SQL used: " + boundSql.getSql());
System.out.println();
[...]
In my case, I need to generate dynamic sql , then submit it to aliyun MaxCompute which is a sevice for big data and can not work with mybatis.
Here is my worked junit5 test case without binding a datasource (only 2 files).
Notice: after get a org.apache.ibatis.mapping.BoundSql, I had to write a custom method toSqlWithParam to geneate full sql.
MyBatisGenSql.mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="xxxNamespace" >
<sql id="Base_Column_List" >
id
</sql>
<select id="select01">
select <include refid="Base_Column_List" />
from ${table}
<where>
<if test="id != null">
AND id = #{id}
</if>
<if test="name != null">
AND ( name = #{name} or nick = #{name} )
</if>
</where>
order by id desc
</select>
</mapper>
MyBatisGenSqlTest.java
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.session.Configuration;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @see org.apache.ibatis.builder.xml.XMLConfigBuilder
*/
@Slf4j
public class MyBatisGenSqlTest {
@SneakyThrows
@Test
public void test() {
String resource = "MyBatisGenSql.mapper.xml";
InputStream inputStream = MyBatisGenSqlTest.class.getResourceAsStream(resource);
Configuration configuration = new Configuration();
XMLMapperBuilder mapperParser = new XMLMapperBuilder(inputStream, configuration, resource, configuration.getSqlFragments());
mapperParser.parse();
MappedStatement ms = configuration.getMappedStatement("xxxNamespace.select01");
Map<String, Object> parameters = new HashMap<>();
parameters.put("table", "table001");
parameters.put("id", 1L);
parameters.put("name", "zhang3");
BoundSql boundSql = ms.getBoundSql(parameters);
String sql = toSqlWithParam(boundSql);
sql = collapseWhiteSpace(sql);
String expectedSql = "select id from table001 WHERE id = 1 AND ( name = 'zhang3' or nick = 'zhang3' ) order by id desc";
Assertions.assertEquals(expectedSql, sql);
}
protected String toSqlWithParam(BoundSql boundSql) {
String sql = boundSql.getSql();
List<ParameterMapping> boundParams = boundSql.getParameterMappings();
Object parameterObj = boundSql.getParameterObject();
Assertions.assertTrue(parameterObj instanceof Map, "parameterObj only support Map right now.");
Map parameterMap = (Map) parameterObj;
// FIXME '?' in sql comment
String[] arr = sql.split("\\?");
Assertions.assertEquals(arr.length - 1, boundParams.size(), "has `?` in sql comment?");
StringBuilder buf = new StringBuilder();
for (int i = 0; i < arr.length; i++) {
buf.append(arr[i]);
if (i < boundParams.size()) {
Object param = parameterMap.get(boundParams.get(i).getProperty());
String paramStr = toParameterString(param);
buf.append(paramStr);
}
}
return buf.toString();
}
protected String toParameterString(Object param) {
if (param == null) {
return "null";
}
if (param instanceof Number || param instanceof Boolean) {
return param.toString();
}
if (param instanceof CharSequence) {
return "'" + param + "'";
}
if (param instanceof java.sql.Date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String str = sdf.format((java.sql.Date) param);
return "'" + str + "'";
}
if (param instanceof java.sql.Time) {
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss");
String str = sdf.format((java.sql.Time) param);
return "'" + str + "'";
}
if (param instanceof java.util.Date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String str = sdf.format((java.util.Date) param);
return "'" + str + "'";
}
throw new IllegalArgumentException("TODO: parameter whit type `" + param.getClass().getName() + "` is not implemented yet.");
}
/**
* @see <a href="http://www.java2s.com/example/java-utility-method/string-whitespace-collapse-index-0.html">Java Utililty Methods String Whitespace Collapse</a>
*/
public String collapseWhiteSpace(String content) {
return content.replaceAll("\\s+", " ");
}
}
© 2022 - 2024 — McMap. All rights reserved.
?instead of actual value. e.g.where login = ?. any work around on this ? thanks (I am not using mapper class) – Dinse