You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Nick Riebeek <Ni...@gov.ab.ca> on 2006/01/30 17:36:48 UTC
complex dynamic searches
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.
Re: complex dynamic searches
Posted by Emmanuele De Andreis <ma...@gmail.com>.
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 <Ni...@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.
>