Tech Support

[roy astronomia <rv...@yahoo.com>]

Team IBatis,
 
i am a newbee in ibatis framework and i want to use ibatis framework on a .net project. i would like to know how can i have a dynamic call of select queries with varying number of where conditions. I am using this query for searching that can require varying number of where criterias such as CustomerID, LastName, FirstName and City. User can fill up all criterias but the can leave it blank to, so there will be a dynamic where clause. Currently this is my code. How can i  write a where clause that can have dynamic fields? Also there would be a problem with the insertion of AND/OR Clause

<select id="SelectCustFilterByName" resultMap="SelectResult" parameterClass="Customer">
 SELECT CustomerID, LastName FROM Customers
 <dynamic prepend="WHERE">
  <isParameterPresent>
   LastName LIKE #value#
  </isParameterPresent>
 </dynamic>   
</select> 
 
 
thanks in advance
Roy
 


		
---------------------------------
Do you Yahoo!?
 Yahoo! Mail - now with 250MB free storage. Learn more.

Re: Tech Support

[Roberto Rabe <ro...@apache.org>]

Roy,

If you grab the latest source from SVN, you can browse the various
DataMapper NUnit tests to see examples of various dynamic SQL statements. 
Here's one example from DynamicAccount.xml:

<statement id="ComplexDynamicStatement"
	cacheModel="account-cache"
	resultClass="Account"
	parameterClass="Account">
	select
	Account_ID			as Id,
	Account_FirstName	as FirstName,
	Account_LastName	as LastName,
	Account_Email		as EmailAddress
	from Accounts
	<dynamic prepend="WHERE">
		<isNotNull prepend="AND" property="FirstName">
			(Account_FirstName = #FirstName#
			<isNotNull prepend="OR" property="LastName">
			Account_LastName = #LastName#
			</isNotNull>
			)
		</isNotNull>
		<isNotNull prepend="AND" property="EmailAddress">
			Account_Email like #EmailAddress#
		</isNotNull>
		<isGreaterThan prepend="AND" property="Id" compareValue="0">
			Account_ID = #Id#
		</isGreaterThan>
	</dynamic>
	order by Account_LastName
</statement>

Hope this helps!

Roberto



On Thu, March 17, 2005 5:31, roy astronomia said:
> Team IBatis,
>
> i am a newbee in ibatis framework and i want to use ibatis framework on a
> .net project. i would like to know how can i have a dynamic call of select
> queries with varying number of where conditions. I am using this query for
> searching that can require varying number of where criterias such as
> CustomerID, LastName, FirstName and City. User can fill up all criterias
> but the can leave it blank to, so there will be a dynamic where clause.
> Currently this is my code. How can i  write a where clause that can have
> dynamic fields? Also there would be a problem with the insertion of AND/OR
> Clause
>
> <select id="SelectCustFilterByName" resultMap="SelectResult"
> parameterClass="Customer">
>  SELECT CustomerID, LastName FROM Customers
>  <dynamic prepend="WHERE">
>   <isParameterPresent>
>    LastName LIKE #value#
>   </isParameterPresent>
>  </dynamic>
> </select>
>
>
> thanks in advance
> Roy
>
>
>
>
> ---------------------------------
> Do you Yahoo!?
>  Yahoo! Mail - now with 250MB free storage. Learn more.