ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nick Riebeek" <Nick.Rieb...@gov.ab.ca>
Subject complex dynamic searches
Date Mon, 30 Jan 2006 16:36:48 GMT
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="sgreen@mail.com"

 

Another search might be:

 

(field1 like "23 Primrose%" or field2 = "Sackville") and field3 =
"Canada"

 

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

This communication is intended for the use of the recipient to which it is addressed, and
may contain confidential, personal and or privileged information. Please contact us immediately
if you are not the intended recipients of this communication, and do not copy, distribute,
or take action relying on it. Any communication received in error, or subsequent reply, should
be deleted or   destroyed.


Mime
View raw message