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 <[EMAIL PROTECTED]> 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="[EMAIL PROTECTED]" > > > > 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. >
