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

时间:2024-11-29 12:37:31

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://*.com/questions/13195144/can-i-use-mybatis-to-generate-dynamic-sql-without-executing-it

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