Appending OR in ibatis based on the condition

Question

I'm using iBATIS to create select statements.

I have 4 conditions, like aUsers, bUsers, cUsers and dUsers and let's call my table as b

Now I would like to implement the following SQL statement with iBATIS:

b.aUsers=aUsers OR b.bUsers=bUsers OR b.cUsers=cUsers OR b.dUsers=dUsers

if aUsers is null and rest are not then i want the query to be

b.bUsers=bUsers OR b.cUsers=cUsers OR b.dUsers=dUsers

//-----------------------------------------------

if cUsers is null and rest are not then i want the query to be

b.aUsers=aUsers OR b.bUsers=bUsers OR b.dUsers=dUsers

etc.

How can I create such a query in ibatis?

Answer 1

In cases like that you need to create Dynamic Queries.

You may use Unary Conditional Elements to check if the properties are null or not.

<isNull> Checks if a property is null.

<isNotNull> Checks if a property is not null

Furthermore, Your case could require a nested isNotNull || isNull anidadement. So you just would do something similar like next where you checks if aUsers is null or not, and then the other properties:

<select id="getUsers" parameterClass="USER" resultMap="your-result-map" >
  select * from b
    <dynamic prepend="WHERE">
      <isNull prepend="AND" property="aUsers">
          <isNotNull property="bUsers">
                  <!-- ... and so on -->
                  <!-- your condition -->
          </isNotNull>
      </isNull>
      <isNotNull prepend="AND" property="aUsers">
         <isNull  property="bUsers">
              <!-- ... and so on -->
              <!-- your condition -->
         </isNull>
      </isNotNull>
    </dynamic>
</select>