CODEWITHSUNDEEP
ibatismybatis
vahapt
vahapt

Reputation: 1705

MyBatis - Nested Conditions in Where Clause

I'm dynamically generating the where clause inside MyBatis v3 mapper xml. However placing parentheses is really cumbersome. Is there an easier way to handle the problem without using if statements?

<where>
  <if test="filter != null">
    <choose>
      <when test="filter.lref != null">
        file.lref = #{filter.lref}
      </when>
      <otherwise>
        <!-- I don't want to use this -->
        <if test="filter.forLike != null || filter.forInt != null">
          ( 
        </if>
        <if test="filter.forLike != null" >
          subject LIKE #{filter.forLike}    
          OR requester_identifier LIKE #{filter.forLike}
          OR requester_name LIKE #{filter.forLike}
        </if>
        <if test="filter.forInt != null">
          OR file_id = #{filter.forInt}
        </if>

        <!-- I don't want to use this -->
        <if test="filter.forLike != null || filter.forInt != null">
          ) 
        </if>
      </otherwise>
    </choose>
  </if>
  <if test="listMode > 0">
    <choose>
       <when test="listMode == 1">
         AND file_status_link.dosya_ref is not NULL
       </when>
       <otherwise>
         AND file_status_link.dosya_ref is NULL
       </otherwise>
    </choose>
   </if>            
</where>

Sample dynamically generated SQL output is as follows

WHERE ( subject LIKE ? OR requester_identifier LIKE ? OR requester_name LIKE ? ) 
AND file_status_link.dosya_ref is NULL

Upvotes: 6

Views: 22814

Answers (2)

user3623143
user3623143

Reputation: 1

Pseudo-SQL (XML-SQL):

where
    1 = 1
    <A>and file.lref = #{filter.lref}</A>
    <D>and (
        <E>
        subject like #{filter.forLike}    
        or requester_identifier like #{filter.forLike}
        or requester_name like #{filter.forLike}
        </E>
        <F>or file_id = #{filter.forInt}</F>
    )</D>
    </B>
    <C>and file_status_link.dosya_ref is <G>not</G> null</C>

Where:

A: <B> && filter.lref != null
B: filter != null
D: <B> && (<E> || <F>)
E: filter.forLike != null
F: filter.forInt != null
C: listMode > 0
G: listMode == 1

Upvotes: 0

partlov
partlov

Reputation: 14277

You can try to encapsulate that part inside <trim> tag. It would be something like this:

<trim prefix="(" prefixOverrides="OR" suffix=")">
  <if test="filter.forLike != null" >
    subject LIKE #{filter.forLike}    
    OR requester_identifier LIKE #{filter.forLike}
    OR requester_name LIKE #{filter.forLike}
  </if>
  <if test="filter.forInt != null">
    OR file_id = #{filter.forInt}
  </if>
</trim>

Upvotes: 6

Related Questions