我可以使用MyBatis生成动态SQL而不执行它吗？ [英] Can I use MyBatis to generate Dynamic SQL without executing it?

问题描述

我有一些复杂的查询要使用多个可选过滤器构建，MyBatis似乎是生成动态SQL的理想选择。

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.

但是，我仍然想要我的查询在与应用程序的其余部分（不使用MyBatis）相同的框架中执行。

However, I still want my query to execute in the same framework as the rest of the application (which is not using MyBatis).

所以我希望做的是严格使用MyBatis生成SQL，但从那里使用我的应用程序的其余部分来实际执行它。这可能吗？如果是这样，怎么做？

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?

推荐答案

虽然MyBatis设计用于在构建之后执行查询，但您可以使用它的配置以及一些内部知识来获得你需要的东西。

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是一个非常好的框架，遗憾的是它在文档方面缺乏所以源代码是你的朋友。如果你四处寻找，你应该碰到这些类： org.apache.ibatis.mapping.MappedStatement 和 org.apache.ibatis.mapping.BoundSql 这是构建动态SQL的关键人物。以下是一个基本用法示例：

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表用户包含此数据：

MySQL table user with this data in it:

name    login
-----   -----
Andy    a
Barry   b
Cris    c

用户类：

package pack.test;
public class User {
    private String name;
    private String login;
    // getters and setters ommited
}

UserService interface：

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 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生成动态SQL而不执行它吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！