Can I use MyBatis to generate Dynamic SQL without executing it?

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'

http://stackoverflow.com/questions/13195144/can-i-use-mybatis-to-generate-dynamic-sql-without-executing-it

 https://my.oschina.net/lichhao/blog/114311