MyBatis 3 - get SQL string from mapper

Question

I'd like to use MyBatis3 only to produce SQL string (using the xml mapping) but the SQL i obtain is not valid.

Example, I obtain the sql string:

SELECT * FROM USER WHERE NAME = john

In this sql isn't present the ' char sorrounding the string value john

in mybatis.xml:

...
    <mappers>
        <mapper resource="sql1.xml"/>
    </mappers>
...

sql1.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

   <mapper namespace="sql1">
       <select id="select1" parameterType="map" resultType="String" >
           SELECT * FROM USERS
           WHERE 
           name LIKE ${name} AND num = ${number}
       </select>
   </mapper>

in MyBatisSql.java:

SqlSessionFactory sessionFactory = ConnectionFactory.getSqlSessionFactory();
Configuration configuration = sessionFactory.getConfiguration();

Map pars = new HashMap<String, Object>();
pars.put("name", "john");    
pars.put("number", 1345);

MappedStatement ms = configuration.getMappedStatement("sql1.select1);   
BoundSql boundSql = ms.getBoundSql(params);
String sql = boundSql.getSql();
System.out.println(sql);

the result is

SELECT * FROM USERS
WHERE 
name LIKE john AND num = 12345

in this SQL, the string john, isn't enclosed by the ' char so it's not a valid SQL (my purpose is only to produce valid SQL string using myBatis). I'd like to have:

SELECT * FROM USERS
WHERE 
name LIKE 'john' AND num = 12345

thanks

Solution

You should use #{name} instead of ${name}.

The sample below will generate a valid SQL

<mapper namespace="sql1">
    <select id="select1" parameterType="map" resultType="String" >
        SELECT * FROM USERS
        WHERE 
        name LIKE #{name} AND num = #{number}
    </select>
</mapper>

MyBatis directly copies and pastes the string parameter if you use $ character. On the other hand it uses parameter binding if you use # character.

You should then execute your sql using selectMap, selectList or selectOne,

List<String> resultSet = sessionFactory.openSession().selectList("sql1.select1", pars);

This call will automatically bind the parameters to the statement and execute it.

WARNING:

<select id="select1" parameterType="map" resultType="String" >
    SELECT * FROM USERS
        WHERE 
        name LIKE #{name} AND num = #{number}
</select>

might fail to execute since MyBatis cannot map multiple columns (SELECT *) to a single string (resultType="String") two possible corrections to the query is shown below:

<!--Solution One-->
<select id="select1" parameterType="map" resultType="String" >
    SELECT name FROM USERS
        WHERE 
        name LIKE #{name} AND num = #{number}
</select>

<!--Solution Two-->
<select id="select1" parameterType="map" resultType="java.util.LinkedHashMap" >
    SELECT * FROM USERS
        WHERE 
        name LIKE #{name} AND num = #{number}
</select>

For solution two you should execute mybatis query using the java code below:

List<Map<?, ?>> resultSet = sessionFactory.openSession().selectList("sql1.select1", pars);

Details of Why getBoundSql Returns a Query with ?:

Parameter binding is done at driver level so you will not get an sql string like this

SELECT * FROM USERS
WHERE 
name LIKE 'john' AND num = 12345

Instead you'll get sql query template which is ready for parameter binding,

SELECT * FROM USERS
    WHERE 
    name LIKE ? AND num = ?

Adding parameters into sql string allows sql injection. Safe way is to use parameter binding method provided with SQL Driver and MyBatis always uses parameter binding.

Suppose that you manually created your sql command as string, and suppose that I'm a malicius user trying to access your data. I can write

john' or ''='

So this will generate the sql command below:

SELECT * FROM USERS
WHERE 
name LIKE 'john' or ''='' AND num = 12345

Additional Benefits of Parameter Binding

The second benefit of parameter binding is that it allows prepared statements. Suppose that you need to execute same sql 1000 times with different parameters.

If you generate sql strings with parameters bound,

SELECT * FROM USERS WHERE name LIKE 'john' AND num = 12345;
SELECT * FROM USERS WHERE name LIKE 'foo' AND num = 67890;

database server will need to parse each sql command one by one and then execute them.

With parameterized sql queries,

SELECT * FROM USERS WHERE name LIKE ? AND num = ?

SQL driver caches the query so parsing is done only once and then it binds different parameters to the same SQL command.

Update: Using BoundSql Outside of MyBatis

You can still use the parameterized sql (boundSql) with an another library or Java's java.sql.Connection. Below is an example:

Connection myConnection;
PreparedStatement preparedStatement = myConnection.prepareStatement(boundSql);
preparedStatement.setString(1, "john"); //First parameter starts with 1 not 0!
preparedStatement.setInt(2, 12345);
ResultSet results = preparedStatement.executeQuery();

Comments

[Lof]

thanks for your reply, I just tried replacing ${name} with #{name} but the result is: SELECT * FROM USERS WHERE name LIKE ? AND num = ?

[vahapt]

@Lof I've extended my answer to cover your comment.

[Lof]

Hello, thanks again. My purpose was to use MyBatis only for sql String generation because the advantage to write sql in xml files. Maybe it's not possible to bind pars in sql without execute sql in mybatis, so I can't use it only to have sql string.

[vahapt]

@Lof Assuming you would be using java.sql.Connection I've updated my answer. In this case you can call getBoundSql without parameters i.e. ms.getBoundSql(null)

[Lof]

Thanks for your complete reply, very helpful

[DerMike]

This is a rather thorough post but this does not seem to answer the question, does it? Sorry. The question states produce [valid] SQL string but @vahapt elaborates on parameter binding in SQL.