我有一些复杂的查询要使用许多可选的过滤器构建，MyBatis似乎是生成动态SQL的理想候选者。
但是，我仍然希望我的查询在与应用程序的其余部分（不使用MyBatis）相同的框架中执行。
所以我希望做的是严格使用MyBatis生成SQL，但是从那里使用我的应用程序的其余部分来实际执行它。这可能吗？如果是这样，怎么样？
我有一些复杂的查询要使用许多可选的过滤器构建，MyBatis似乎是生成动态SQL的理想候选者。
但是，我仍然希望我的查询在与应用程序的其余部分（不使用MyBatis）相同的框架中执行。
所以我希望做的是严格使用MyBatis生成SQL，但是从那里使用我的应用程序的其余部分来实际执行它。这可能吗？如果是这样，怎么样？
虽然MyBatis设计用于在构建查询后执行查询，但您可以利用它的配置和一些“内部知识”来获得所需内容。
MyBatis是一个非常好的框架，遗憾的是它缺少文档方面，所以源代码是你的朋友。如果你四处挖掘，你应该碰到这些类： org.apache.ibatis.mapping.MappedStatement 和 org.apache.ibatis.mapping.BoundSql 这是构建动态SQL的关键角色。这是一个基本的用法示例：
MySQL表 user 有这些数据：
name login
----- -----
Andy a
Barry b
Cris c
User 类：
package pack.test;
public class User {
private String name;
private String login;
// getters and setters ommited
}
UserService 接口：
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 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 显示结果：
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();
}
}
}
}
结果如下：
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'
虽然MyBatis设计用于在构建查询后执行查询，但您可以利用它的配置和一些“内部知识”来获得所需内容。
MyBatis是一个非常好的框架，遗憾的是它缺少文档方面，所以源代码是你的朋友。如果你四处挖掘，你应该碰到这些类： org.apache.ibatis.mapping.MappedStatement 和 org.apache.ibatis.mapping.BoundSql 这是构建动态SQL的关键角色。这是一个基本的用法示例：
MySQL表 user 有这些数据：
name login
----- -----
Andy a
Barry b
Cris c
User 类：
package pack.test;
public class User {
private String name;
private String login;
// getters and setters ommited
}
UserService 接口：
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 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 显示结果：
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();
}
}
}
}
结果如下：
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'
每个人都知道如何使用BoundSql.getSql（）从MyBatis获取一个参数化的查询字符串，如下所示：
// 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 = ?
但是现在你需要方程的另一半，与问号对应的值列表：
// 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;"
现在，您可以将其序列化以发送到其他位置以进行运行，或者您可以将其打印到日志中，以便将它们拼接在一起并手动运行查询。
*未经测试的代码，可能是次要类型问题等
只是添加到Bogdan的正确答案：您需要将JavaBean传递给 getBoundSql() 如果您的接口具有更复杂的签名，那么使用getter作为您的接口参数。
假设您要根据登录号和/或用户名查询用户。您的界面可能如下所示：
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);
}
我省略了Mapper代码，因为它与此讨论无关，但您在AppTester中的代码应该变为：
[...]
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();
[...]