iBatis allows building dynamic query conditionally based on properties sent to the named SQL. Suppose We have a Person implementation where the search page allows search by id or by email id (assuming both are unique). We could write two queries
something like this
<select id="getPersonById" resultClass="Person" > select * from PERSON where ID = #value# </select> <select id="getPersonByManagerId" resultClass="Person" > select * from PERSON where EMAIL_ID = #value# </select>OR we could make a dynamic Query like the following
<select id="getPerson" resultClass="Person" parameterClass="java.util.map"> select * from PERSON <dynamic prepend="where" > <isNotNull parameter="id"> ID = #id# </isNotNull> <isNotNull parameter="managerId"> EMAIL_ID = #emailId# </isNotNull> </dynamic> </select>However there is one pitfall. If the parameter does not contain either id or managerId, we are going to return all Person objects and a java queryForObject will throw a multiple objects found exception. How do we avoid this? One solution is to add a or condition with a ubiquitous false expression
something like this
<select id="getPerson" resultClass="Person" parameterClass="java.util.map"> select * from PERSON where 1=0 <dynamic prepend="or" > <isNotNull parameter="id"> ID = #id# </isNotNull> <isNotNull parameter="managerId" prepend="and" > EMAIL_ID = #emailId# </isNotNull> </dynamic> </select>if both parameters are null, the statement will never return any result-sets so we are good. And notice that if BOTH filters are given, it would AND and still return only one result.