Dynamic Select SQL statements with MyBatis

问题:

I would like to have dynamic sql statements using mybatis in an Oracle 12g database where I can utilize a hash map to add conditionals something like the following:

<select id="getUsers" resultType="hashmap" parameterType="hashmap"> 
  select * 
  from users 
  <where> 
  <iterate var="i=0" increment> 
    ${columni} like #{valuei} 
  </iterate> 
  </where> 
</select> 

Is there a way for me to accomplish something like that?

回答1:

From documentation:

String Substitution

By default, using the #{} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatement parameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject a string unmodified into the SQL Statement. For example, for ORDER BY, you might use something like this:

ORDER BY ${columnName}

Here MyBatis won't modify or escape the string.

This allows you to e.g. pass column names as parameters to query etc.

Remember to always sanitize data that you are directly pasting to SQL.

---

If you need to generate multiple conditions for WHERE clause, use <where> tag with <foreach> inside. Note that <foreach> has advanced attributes that allow to specify separator, opening/ending string etc. Combined with ${} notation I've mentioned before this allows construction of dynamic WHERE clause. For an example, see this answer.