|
Hello, I’ve been looking into doing a fairly complex
search within iBatis and was wondering if it was possible or if I need to put
the search logic into either a stored procedure or my app code. What I need to accomplish is for the user to be able to
search or any field or combination of fields. For instance, say I have a
data entity with 10 fields, I need to allow the user to search for something
like (field4 = “sam” and field5”=”green”)
or field7=”[EMAIL PROTECTED]” Another search might be: (field1 like “23 Primrose%” or field2 = “Sackville”)
and field3 = “ I suspect, these types of dynamic search queries can not be
built within the iBatis .xml, as I need to keep track of whether the user
specified a specific search field so that I know when to use a where clause and
when to use an and. For instance something like the below will not work, as if
the first field is null, then the where clause will not be added to the sql. <select id="Search" resultMap="SearchResult" parameterMap="SearchParameters"> SELECT * FROM Address
<dynamic prepend="WHERE">
<isNotNull property="field1"> field1= # field1#
</isNotNull>
<isNotNull prepend="AND" property="field2"> field2= # field2#
</isNotNull>
</dynamic>
…
… </select> Likewise the below won’t work as although the WHERE
will always be present, the and statements will always be inserted, therefore
resulting in a query reading SELECT… WHERE AND ….. <select id="Search" resultMap=" SearchResult " parameterMap=" SearchParameters "> SELECT * FROM Address WHERE
<dynamic>
<isNotNull prepend="AND" property=" field1"> field1= # field1#
</isNotNull>
</dynamic>
<dynamic>
<isNotNull prepend="AND" property=" field2"> field2= # field2#
</isNotNull>
</dynamic>
…
… </select> Thanks for any comments / suggestions that people may have. Nick |
- complex dynamic searches Nick Riebeek
- Re: complex dynamic searches Emmanuele De Andreis
