java 使用MyBatis可以不执行生成动态SQL吗？

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

提问by Ray

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 似乎是生成动态 SQL 的理想选择。

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

但是，我仍然希望我的查询与应用程序的其余部分（不使用 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?

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

采纳答案by Bogdan

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 被设计为在构建后执行查询，但您可以利用它的配置和一点“内部知识”来获得您需要的信息。

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.MappedStatementand org.apache.ibatis.mapping.BoundSqlwhich are key players into building the dynamic SQL. Here is a basic usage example:

MyBatis 是一个非常好的框架，不幸的是它在文档方面缺乏，所以源代码是你的朋友。如果你周围挖你应该碰到这些类：org.apache.ibatis.mapping.MappedStatement与org.apache.ibatis.mapping.BoundSql这是关键球员进入构建动态SQL。这是一个基本的使用示例：

MySQL table userwith this data in it:

user包含此数据的MySQL 表：

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

Userclass:

User班级：

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

UserServiceinterface:

UserService界面：

package pack.test;
public interface UserService {
    // using a different sort of parameter to show some dynamic SQL
    public User getUser(int loginNumber);
}

UserService.xmlmapper file:

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:

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>

AppTesterto show the result:

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();
            }
        }
    }
}

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'

回答by chad

Everyone knows how to use BoundSql.getSql() to get a paramaterized query string from MyBatis, like this:

大家都知道如何使用 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 = ?

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

*代码未经测试，可能是次要类型问题或类似问题

回答by zwx

mybatis version is 3.4.5

mybatis 版本是 3.4.5

Util Class

实用程序类

To convert mapper to sql, need mapper interface class,method name,paramters,and sqlSession.

将mapper转换为sql，需要mapper接口类、方法名、参数和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;
            }
}

call example

调用示例

sqlSession is injected by Spring .

sqlSession 由 Spring 注入。

@Autowired
private SqlSession sqlSession;

    String sql = SqlUtil
        .showSql(sqlSession, PromotionCodeMapper.class, "selectByPromotionCodeForUpdate",
            new Object[]{"111"});
    log.warn(sql);

回答by Stefan Haberl

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.

只是添加到 Bogdan 的正确答案中：getBoundSql()如果您的接口具有更复杂的签名，则需要将 JavaBean 传递给带有 getter 的接口参数。

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:

我省略了 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();
[...]