How to apply a method to a parameter in MyBatis

Andrew White picture Andrew White · Feb 8, 2012 · Viewed 11k times · Source

After reading about Mapper XMLs I can't help to wonder how one might go about appling some common transforms to a parameter. For example...

<select id="selectPerson" parameterType="String" resultType="hashmap">
  <!-- #{name} should always be upper case and have a trailing % -->
  SELECT * FROM PERSON WHERE FIRST_NAME like #{name} 
</select> 

After reading this and this I can make some observations.

  1. Using SQL functions such as upper or concat or '||' or '+' to do transforms kills performance in DB2
  2. I could always wrap the the mapper or expose the details in the service layer but that seems messy

What I want is to be able to do something like...

<select id="selectPerson" parameterType="String" resultType="hashmap">
  <!-- #{name} should always be upper case and have a trailing % -->
  SELECT * FROM PERSON WHERE FIRST_NAME like #{name.upperCase() + '%'} 
</select> 

Is something like this possible or what is the second best solution?

Update: it appears that MyBatis uses OGNL for some expression evaluation. For example, if and ${} expressions use OGNL but #{} does NOT appear to unless there is some way to trick it.

Answer

skrounge picture skrounge · Jun 7, 2013

Bind

MyBatis allows for creating values from method and properties in the context using <bind/>.

Bind creates a new variable in the scope of the current statement. The OGNL statement that binds the value can use the passed in _parameter object to compute a new bound value that can then used by MyBatis to construct the prepared statement.


Example

Your example using bind:

<select id="selectPerson" parameterType="String" resultType="hashmap">
  <!-- #{name} should always be upper case and have a trailing % -->
  <bind name="nameStartsWith" value="_parameter.getName().upperCase() + '%'"/>
  SELECT * FROM PERSON WHERE FIRST_NAME like #{nameStartsWith} 
</select>

Source