ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From xianwinwin <xianwin...@gmail.com>
Subject RE: what is the correct SQL for IN ?
Date Tue, 17 Apr 2007 15:51:30 GMT

thanks.

I did this and it's working (with out the IN):

	  <isNotNull prepend="AND" property="states">	  	   	  
		<iterate  property="states"
			open="(" close=")" conjunction="OR">
			LG.state=#states[]#
	  	</iterate>
	  </isNotNull>


Poitras Christian wrote:
> 
> The iterate tag is explained in the documentation
> http://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf
> On page 49.
> The complete doucmentation of dynamic sql starts on page 46.
> 
> The notation ?property? Is used mainly in cases where the property must
> not be converted to a jdbc type.
> For example.
> <select resultMap="someMap" parameterClass="map">
> SELECT *
> FROM ?tableName?
> WHERE id = #id#
> </select>
> 
> In your case, the best solution would look more like this.
> <select resultMap="someMap" parameterClass="myClass">
>   SELECT *
>   FROM LG
>   WHERE LG.state
>   <iterate property="stateList" prepend="IN" open="(" close=")"
> conjunction=",">
>     #stateList[]#
>   </iterate>
> </select>
> 
> Christian
> 
> 
> -----Original Message-----
> From: xianwinwin [mailto:xianwinwin@gmail.com] 
> Sent: Monday, 16 April 2007 18:21
> To: user-java@ibatis.apache.org
> Subject: RE: what is the correct SQL for IN ?
> 
> 
> thank you! 
> 
>  IN ?state? WORKS!
> 
> where in the docs does it explain this issue?
> thanks again
> 
> 
> 
> Poitras Christian wrote:
>> 
>> Make a List of states (or an array) and use the iterate tag (see the 
>> documentation).
>> 
>> Another possibility is to use
>> 	  <isNotNull prepend="AND" property="state">
>> 	  	LG.state IN ?state?
>> 	  </isNotNull>
>> But I don't recommend this in your case. You should really use the 
>> iterate tag.
>> 
>> Christian
>> 
>> -----Original Message-----
>> From: xianwinwin [mailto:xianwinwin@gmail.com]
>> Sent: Monday, 16 April 2007 17:26
>> To: user-java@ibatis.apache.org
>> Subject: what is the correct SQL for IN ?
>> 
>> 
>> Hi there,
>> 
>> I wonder how to implement this query in ibatis as it gives me an error
> 
>> message.
>> 
>> query: ..... AND LG.state IN ('ny', 'bj')   (in plain sql) 
>> 
>> in ibatis: 
>> 
>> 	  <isNotNull prepend="AND" property="state">
>> 	  	LG.state IN #state:VARCHAR#
>> 	  </isNotNull>
>> 
>> 
>> error:  You have an error in your SQL syntax; check the manual that 
>> corresponds to your MySQL server version for the right syntax to use 
>> near
>> ''(\'ny\', \'nj\')' 	   	
>> 
>> the state value is: "('ny', 'nj')" 
>> 
>> thanks
>> --
>> View this message in context:
>> http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#
>> a1
>> 0024994
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>> 
>> 
>> 
> 
> --
> View this message in context:
> http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a1
> 0025815
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a10037979
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


Mime
View raw message