Dynamic Select SQL statements with MyBatis

Question

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?

Can't figure out - what's your problem? Have you read mybatis documentation? Your scenario looks quite standard and simple. — Konstantin V. Salikhov, Oct 21 '13 at 17:17
In the documentation, http://mybatis.github.io/mybatis-3/dynamic-sql.html, you have to specify the columns by using if statements and the example with the foreach is only used to specify values in a IN condition. I imagine there has to be another way for me to accomplish what I want using foreach. Again what I want is to build a select statement where I can use column and value names dynamically. — gabrielarules, Oct 21 '13 at 18:27
The question answer and its usage can be found here: [mybatis-generic-stmt-example](http://stackoverflow.com/a/43356567/2762716) — Alexander Davliatov, Apr 11 '17 at 21:59

score 2 · Answer 1 · edited May 23 '17 at 10:30

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.

but how do I add multiple where clauses? how can iterate the map to create the where clauses? I don't want to just put a String. — gabrielarules, Oct 21 '13 at 21:57
why won't you just use `` inside `` tag? see my updated answer. — Michał Rybak, Oct 21 '13 at 22:37
glad I could help :) Please remember to accept an answer that was helpful to you. — Michał Rybak, Oct 22 '13 at 01:52

Dynamic Select SQL statements with MyBatis

1 Answers1

Linked