Prepend does work. The <dynamic> tag removes the prepend of the first
content producing tag. So, i gave you a bad example. Sorry.
Yes, i have an example that works just fine.
This should work:
<select id="findCountsBySession" parameterClass="list" resultMap="counts">
SELECT
session_id as id, count(*) count
FROM
events
<dynamic prepend="WHERE">
<iterate
open=" session_id IN ("
close=")" conjunction=",">
#[]#
</iterate>
</dynamic>
GROUP BY
session_id
</select>
You could also try (I'm not sure if this example will work, but give it a try):
<select id="findCountsBySession" parameterClass="list" resultMap="counts">
SELECT
session_id as id, count(*) count
FROM
events
<dynamic prepend="WHERE" removeFirstPrepend="false">
<iterate
prepend=" session_id IN " open=" ("
close=")" conjunction=",">
#[]#
</iterate>
</dynamic>
GROUP BY
session_id
</select>
Here is a place where i use it:
<delete id="removeCategories" parameterClass="list">
DELETE FROM Category
WHERE
<iterate conjunction="OR" >
(leftNode > #[].leftNode# AND rightNode < #[].rightNode#)
</iterate>
</delete>
Brandon
On 6/9/05, Paul Barry <[EMAIL PROTECTED]> wrote:
> Looks like prepend doesn't work, but that's ok, I can just hard code
> "session_id IN" before the iterate tag. This works, if I wrap the
> List in a map with "sessionIds" as the key:
>
> <select id="findCountsBySession" parameterClass="map" resultMap="counts">
> SELECT
> session_id as id, count(*) count
> FROM
> events
> WHERE
> session_id IN
> <iterate property="sessionIds" open="(" close=")" conjunction=",">
> #sessionIds[]#
> </iterate>
> GROUP BY
> session_id
> </select>
>
> This gives me the java.lang.StringIndexOutOfBoundsException: String
> index out of range: -2 exception:
>
> <select id="findCountsBySession" parameterClass="list" resultMap="counts">
> SELECT
> session_id as id, count(*) count
> FROM
> events
> WHERE
> session_id IN
> <iterate property="value" open="(" close=")" conjunction=",">
> #value[]#
> </iterate>
> GROUP BY
> session_id
> </select>
>
> Seems like a bug, does anyone else have something like this working
> with #value[]#?
>
> On 6/9/05, Paul Barry <[EMAIL PROTECTED]> wrote:
> > That makes sense, that is probably what I want, but I am still getting
> > the same exception.
> >
> > I am calling it this way (uses SpringFramework 1.1.5 SqlMapClientDaoSupport)
> >
> > public Map<Long, Integer> findCountsBySession(List<Long> sessionIds) {
> > return
> > getSqlMapClientTemplate().queryForMap("event.findCountsBySession",sessionIds,"id","count");
> > }
> >
> > Could the List<Long> giving it problems?
> >
> > On 6/9/05, Brandon Goodin <[EMAIL PROTECTED]> wrote:
> > > <select id="findCountsBySession" parameterClass="list"
> > > resultMap="counts">
> > > SELECT
> > > session_id as id, count(*) count
> > > FROM
> > > events
> > > <dynamic prepend="WHERE">
> > > <iterate property="value" prepend="session_id IN" open="("
> > > close=")" conjunction=",">
> > > #value[]#
> > > </iterate>
> > > </dynamic>
> > > GROUP BY
> > > session_id
> > > </select>
> > >
> > > Brandon
> > >
> > > On 6/9/05, Paul Barry <[EMAIL PROTECTED]> wrote:
> > > > I am using iBatis 2.1.0.565 with JDK 1.5.0. I am trying to pass a
> > > > List to a sqlmap and then use it to generate a list of comma-separated
> > > > values for the IN clause of a query, like this:
> > > >
> > > > <resultMap id="counts" class="map">
> > > > <result property="id" column="id" javaType="long"/>
> > > > <result property="count" column="count" javaType="int"/>
> > > > </resultMap>
> > > >
> > > > <select id="findCountsBySession" parameterClass="list"
> > > > resultMap="counts">
> > > > SELECT
> > > > session_id as id, count(*) count
> > > > FROM
> > > > events
> > > > <dynamic prepend="WHERE">
> > > > <iterate property="value" open="(" close=")" conjunction=",">
> > > > </iterate>
> > > > session_id IN #value[]#
> > > > </dynamic>
> > > > GROUP BY
> > > > session_id
> > > > </select>
> > > >
> > > > The query I would like to end up with is:
> > > >
> > > > SELECT
> > > > session_id, count(*) count
> > > > FROM
> > > > events
> > > > WHERE
> > > > session_id IN (1,2,3)
> > > > GROUP BY
> > > > session_id
> > > >
> > > > But instead I get an exception. Can you not use #value[]#? Here is
> > > > the exception:
> > > >
> > > > java.lang.StringIndexOutOfBoundsException: String index out of range: -2
> > > > at java.lang.String.substring(String.java:1768)
> > > > at
> > > > com.ibatis.common.beans.GenericProbe.getObject(GenericProbe.java:55)
> > > > at
> > > > com.ibatis.sqlmap.engine.mapping.sql.dynamic.elements.IterateTagHandler.doStartFragment(IterateTagHandler.java:34)
> > > > at
> > > > com.ibatis.sqlmap.engine.mapping.sql.dynamic.DynamicSql.processBodyChildren(DynamicSql.java:129)
> > > > at
> > > > com.ibatis.sqlmap.engine.mapping.sql.dynamic.DynamicSql.processBodyChildren(DynamicSql.java:132)
> > > > at
> > > > com.ibatis.sqlmap.engine.mapping.sql.dynamic.DynamicSql.processBodyChildren(DynamicSql.java:99)
> > > > at
> > > > com.ibatis.sqlmap.engine.mapping.sql.dynamic.DynamicSql.process(DynamicSql.java:79)
> > > > at
> > > > com.ibatis.sqlmap.engine.mapping.sql.dynamic.DynamicSql.getParameterMap(DynamicSql.java:61)
> > > > at
> > > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:152)
> > > > at
> > > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:119)
> > > > at
> > > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
> > > > at
> > > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
> > > > at
> > > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForMap(SqlMapExecutorDelegate.java:700)
> > > > at
> > > > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForMap(SqlMapSessionImpl.java:121)
> > > > at
> > > > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForMap(SqlMapClientImpl.java:93)
> > > >
> > >
> >
>
