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

Related searches

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.

However, I still want my query to execute in the same framework as the rest of the application (which is not using 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?

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'

Can I use MyBatis to generate Dynamic SQL without executing it , MappedStatement and org.apache.ibatis.mapping.BoundSql which are key players into building the dynamic SQL. Here is a basic usage� MyBatis Dynamic SQL Usage Notes. This page is a short introduction to using the classes generated for the MyBatis3DynamicSQL runtime. These classes are dependent on the MyBatis Dynamic SQL library. Please refer to that site for full information about how the library works. For each introspected table, the generator will generate three objects:

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

// 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

MyBatis Dynamic SQL – How it Works, It can generate dynamic SQL with special tags in XML, or through the use of For example, MyBatis can execute an SQL string formatted like this: These classes can be used in conjunction with a MyBatis mapper like this: So you must be careful that these types of mappers are not exposed to any� The most common use case is to generate statements, and a matching set of parameters, that can be directly used by MyBatis. The library will also generate statements and parameter objects that are compatible with Spring JDBC templates. The library works by implementing an SQL-like DSL that creates an object containing a full SQL statement and

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.

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:

[...]
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();
[...]

MyBatis Dynamic SQL Usage Notes, This page is a short introduction to using the classes generated for the MyBatis3DynamicSQL runtime. These classes are dependent on the MyBatis Dynamic SQL library. For each introspected table, the generator will generate three objects: class is generated; There are no separate "with BLOBs" and " without BLOBs"� MyBatis Dynamic SQL MyBatis Dynamic SQL is an SQL templating library that makes it easier to execute dynamic SQL with MyBatis. It generates SQL that is formatted in such a way that it can be directly executed by MyBatis. The library also supports generating SQL that is formatted for use by Spring JDBC Templates.

mybatis version is 3.4.5

Util Class

To convert mapper to sql, need mapper interface class,method name,paramters,and 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 .

@Autowired
private SqlSession sqlSession;

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

mybatis – MyBatis 3, Dynamic SQL can be downright painful to deal with. with a powerful Dynamic SQL language that can be used within any If the where element does not behave exactly as you like, you can Another common necessity for dynamic SQL is the need to iterate over a collection, often to build an IN condition. No package in MyBatis is "off limits". If something works for you, then certainly feel free to use it. You could write an annotated method like this: @SelectProvider(type=GenericQueryProvider.class, method="genericQuery") List<Map> genericQuery(Object parms) Then your select provider could write and execute any query and return any result set.

MYBATIS - Dynamic SQL, It enables programmers to build queries based on the scenario dynamically. MyBatis uses a powerful Dynamic SQL language that can be used within any example will search only by name if it is provided, and if the name is not given, then which will be used in mybatisRead.java for executing Dynamic SQL SELECT� It's very likely that you can't, or you'd have to use native SQL for it at least. Remember that PostgreSQL doesn't allow PL/PgSQL inline, you need a DO block or an explicit function. – Craig Ringer Nov 23 '14 at 7:38

Executing dynamic SQL using sp_executesql. sp_executesql is an extended stored procedure that can be used to execute dynamic SQL statements in SQL Server. we need to pass the SQL statement and definition of the parameters used in the SQL statement and finally set the values to the parameters used in the query.

It can't be used to create dynamic procedures (any CREATE PROCEDURE would have a static definition based on the :SETVAR values in effect originally), but it can be used for some very powerful dynamic scripts.

Comments
  • for me, it shows ? instead of actual value. e.g. where login = ?. any work around on this ? thanks (I am not using mapper class)