[FIXED] MyBatis query in a for loop

Issue

I'm using Java, Spring boot and MyBatis. Consider that I have a list of hashMap objects like this :

[
   { "filterFunction": "GreaterEquals": "filterValue": 1000, "targetField": "balance"},
   { "filterFunction": "like": "filterValue":"Mike", "targetField": "name"},
]

How can I convert it to a query ? in myBatis xml file I want to have a loop and for each list item add a condition (WHERE clause).

so my WHERE query should be dynamic and in a loop. How can I achieve this?

Solution

There seems to be a mix-up with comma and colon, but it would look something like this if I understand correctly.

<select id="select" resultType="domain.User">
  select * from users
  <where>
    <foreach collection="list" item="x" separator="and">
      ${x.targetField}
      <choose>
        <when test="x.filterFunction == 'GreaterEquals'">
          >=
        </when>
        <when test="x.filterFunction == 'LessEquals'">
          &lt;=
        </when>
        <when test="x.filterFunction == 'like'">
          like
        </when>
      </choose>
      #{x.filterValue}
    </foreach>
  </where>
</select>

A few notes:

1. As it's XML, < must be escaped as < or written in CDATA i.e. <![CDATA[<]]>.
2. You must ensure that the value of targetField is a valid column name because ${} has to be used for column names in the statement. See the FAQ for the details.

Off-topic:

• You may also be able to use SQL provider. There are some examples in the doc.

Answered By - ave
Answer Checked By - Clifford M. (JavaFixing Volunteer)