0

I would like to use a dynamic sql statement that executes only when the variable is not null AND greater than zero. Like this:

<isNotNull prepend="AND" property="ProprietaryId">
    <isGreaterThan prepend="AND" property="ProprietaryId" compareValue="0">
        G.PROPRIETARY_ID = #ProprietaryId#
    </isGreaterThan>
</isNotNull>

but without prepending two 'AND's.

I have read the documentation but have found no good example.

anthonybell
  • 5,790
  • 7
  • 42
  • 60

4 Answers4

6

To work around to this issue I almost never use the "prepend" feature, but instead write an sql like this:

WHERE 1=1
<isNotNull property="ProprietaryId">
    <isGreaterThan property="ProprietaryId" compareValue="0">
    AND G.PROPRIETARY_ID = #ProprietaryId#
    </isGreaterThan>
</isNotNull>
Andrea Colleoni
  • 5,919
  • 3
  • 30
  • 49
3

I just came across this question while looking for the same answer. While effective, this solution kind of bugged me so I studied the iBATIS docs some more and noticed this example:

  <dynamic prepend="where">
    <isGreaterThan prepend="and" property="id" compareValue="0">
      ACC_ID = #id#
    </isGreaterThan>
    <isNotNull prepend="and" property="lastName">
      ACC_LAST_NAME = #lastName#
    </isNotNull>
  </dynamic>

You'd think that might cause a superfluous "and" to be included within the WHERE clause if only one of the conditions is true, but apparently iBATIS is smart enough to prevent this when using the dynamic tag. It works for me (using iBATIS 2.3.0 in this case).

Jeff
  • 93
  • 1
  • 7
1

Its me from the future. Parent elements override the prepend of their first child, so your code will work fine since the isGreaterThan prepend will be overwritten by the parent isNotNull prepend.

From the docs:

The prepend attribute is a part of the code that is free to be overridden by the a parent element's prepend if necessary. In the above example the "where" prepend will override the first true conditional prepend. This is necessary to ensure that the SQL statement is built properly. For example, in the case of the first true condition, there is no need for the AND, and in fact it would break the statement.

anthonybell
  • 5,790
  • 7
  • 42
  • 60
0
<isNotNull property="ProprietaryId">
    <isGreaterThan prepend="AND" property="ProprietaryId" compareValue="0">
        G.PROPRIETARY_ID = #ProprietaryId#
    </isGreaterThan>
</isNotNull>

just delete the first prepend will work

雷云竹
  • 1