ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emmanuele De Andreis <manu...@gmail.com>
Subject Re: complex dynamic searches
Date Tue, 31 Jan 2006 14:43:37 GMT
Hi,
why don't you keep it simple?
You can pass the whole where condition as a single parameter.
In this case you have to use the $parameter$ syntax but you have no limits.
As long as you create a condition in ansi sql this should not be a big
problem even with multiple providers.
Manu

On 1/30/06, Nick Riebeek <Nick.Riebeek@gov.ab.ca> wrote:
>
>
> 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.
>
>
>
>
>
> NickThis 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