SQL parameter substitution functionality differs substantially from string substitution functionality

[spencermw]

In both the user guide (http://mybatis.github.io/mybatis-3/sqlmap-xml.html#Parameters) and the GitHub FAQ (https://github.com/mybatis/mybatis-3/wiki/FAQ), the #{} syntax and the ${} syntax are treated as being identical, with the exception that ${} performs a direct string substitution and #{} inserts the value as a parameter to a prepared statement.

However, in practice, the #{} syntax limits the expressions that can be used in unexpected ways.

First, the #{} syntax cannot retrieve a value from a map when any syntax other than map.key is used. This is the deal-breaker that first brought this issue to my attention. Given:

try(SqlSession session = ...) {
    HashMap<String, String> map = new HashMap<>();
    map.put("name", "Bob Smith");
    session.getMapper(TestMapper.class).test(map);
}

/* TestMapper.java */
int test(@Param("map") HashMap<String, String> map);

<!-- TestMapper.xml -->
<select id="test" resultType="int">
    SELECT ID FROM PERSON WHERE NAME = #{map.name}
</select>

This will work; however, none of the below will work:

<!-- TestMapper.xml -->
<select id="test" resultType="int">
    SELECT ID FROM PERSON WHERE NAME = #{map['name']}
</select>

<!-- TestMapper.xml -->
<select id="test" resultType="int">
    <bind name="bound" value="'name'"/>
    SELECT ID FROM PERSON WHERE NAME = #{map[bound]}
</select>

<!-- TestMapper.xml -->
<select id="test" resultType="int">
    <!-- #{map.get(bound) with bound defined as above will also fail. -->
    SELECT ID FROM PERSON WHERE NAME = #{map.get('name')}
</select>

Null is used to set the parameter in each case, despite each expression evaluating correctly if used with ${} syntax instead.

Additionally, functions may not be used in parameter bindings. Given an object mapWrapper with a method name() passed as a parameter:

<select id="test" resultType="int">
    SELECT ID FROM PERSON WHERE NAME = #{mapWrapper.name()}
</select>

This results in an error. (Using ${} syntax, obviously, does not.)

Finally, attempting to use <bind> to overcome these limitations results in unexpected behavior. Consider the following:

<select id="test" resultType="int">
    <bind name="actualParameter" value="map['name']">
    SELECT ID FROM PERSON WHERE NAME = #{actualParameter}
</select>

This works as expected, which is great. However, let's say we have a list of columns (name, title, favorite_cookie_variety), and a map of columns to values (name => "Bob", title => "Senior Cookie Tester", favorite_cookie_variety => "Mint Chocolate"). We want to include these columns and values in our WHERE clause, subject to some arbitrary criteria which we don't want to work out beforehand.

<select id="test" resultType="int">
    SELECT ID FROM PERSON
    <where>
        <foreach collection="columns" item="column">
            <if test="column.someArbitraryCriteria">
                <bind name="actualParameter" value="#{map[column.name]}"/>
                AND ${column.name} = #{actualParameter}
            </if>
        </foreach>
    </where>
</select>

If you're familiar with the order in which MyBatis performs operations, you already see what's going to happen here. We re-bind "actualParameter" to a different value every iteration through the loop, but because parameter setting occurs after the script parsing has been completed, each parameter in the SQL query will be bound to the same value: the last value assigned to actualParameter.

While these examples may seem silly and contrived, I believe that the difference between ${} syntax and #{} syntax is unexpected and confusing. The available documentation does not describe or even hint at these limitations. Clarifying the documentation might be helpful, but I believe the usefulness of MyBatis will be enhanced by enabling users to leverage all the power available to them in ${} expressions in #{} expressions as well.

I would propose that the script driver be modified to parse #{} expressions as OGNL expressions the same as ${} expressions are. The result value could be stored in a new temporary variable, and that temporary variable be used in the parameter-setting stage.

[avackova]

Does it exist a workaround for last issue? How can I set actual value in following statement?

<foreach item="c" collection="filter.getFilter()" separator=" AND " open="(" close=")">
    <bind name="column" value="_parameter.mappingWhere(c.colCode)"/>
    <bind name="operator" value="_parameter.conditionOperator(c.condition)"/>
    <bind name="value" value="_parameter.conditionValue(c.condition, c.value)"/>
        ${column} ${operator} #{value}
</foreach>          

I've tried static methods, not static methods,..., but nothing works :-(

[xiangyucao]

Help! I have the exactly the same problem! How to use bind in foreach. Thanks.

[mnesarco]

See #575