2 Lists in query statement

[Debasish Dutta Roy <de...@gmail.com>]

hi all
I need to add 2 lists into the query. i.e. I have a select with 2 IN clause.
Used an object with 2 lists. Passed that to the query.

    <select id="getPSPIds" resultClass="java.util.ArrayList"
cacheModel="profile-cache" parameterClass="
com.mycriteria.ProfileProtocolCriteria">
        select distinct psp.profile_study_protocol_id
        from profile_study_protocol psp, profile_protocol_item ppi
        where
            psp.profile_study_protocol_id = ppi.profile_study_protocol_id
            <dynamic prepend="AND">
                <iterate open="psp.profile_id IN(" close=")"
conjunction=",">
                    #[profileIdList]#
                </iterate>
            </dynamic>
            and psp.status_id is null
            <dynamic prepend="AND">
                <iterate open="ppi.profile_protocol_item_id IN(" close=")"
conjunction=",">
                    #[ppiIdList]#
                </iterate>
            </dynamic>
            and ppi.is_target is not null
    </select>

ProfileCriteria has 2 member variables with the names in the dynamic clause.

Getting the error:

-- Cause: com.ibatis.sqlmap.client.SqlMapException: ParameterObject or
property was not a Collection, Array or Iterator.

Re: 2 Lists in query statement

[Jeff Butler <je...@gmail.com>]

Try something like this with different properties in your parameter object
for each list:

<iterate property="profileIdList" open="psp.profile_id IN(" close=")"
conjunction=",">
  #profileIdList[]#
</iterate>

Jeff Butler



On 6/15/06, Debasish Dutta Roy <de...@gmail.com> wrote:
>
> hi all
> I need to add 2 lists into the query. i.e. I have a select with 2 IN
> clause. Used an object with 2 lists. Passed that to the query.
>
>     <select id="getPSPIds" resultClass="java.util.ArrayList "
> cacheModel="profile-cache" parameterClass="
> com.mycriteria.ProfileProtocolCriteria">
>         select distinct psp.profile_study_protocol_id
>         from profile_study_protocol psp, profile_protocol_item ppi
>         where
>             psp.profile_study_protocol_id = ppi.profile_study_protocol_id
>             <dynamic prepend="AND">
>                 <iterate open="psp.profile_id IN(" close=")"
> conjunction=",">
>                     #[profileIdList]#
>                 </iterate>
>             </dynamic>
>             and psp.status_id is null
>             <dynamic prepend="AND">
>                 <iterate open=" ppi.profile_protocol_item_id IN("
> close=")" conjunction=",">
>                     #[ppiIdList]#
>                 </iterate>
>             </dynamic>
>             and ppi.is_target is not null
>     </select>
>
> ProfileCriteria has 2 member variables with the names in the dynamic
> clause.
>
> Getting the error:
>
> -- Cause: com.ibatis.sqlmap.client.SqlMapException: ParameterObject or
> property was not a Collection, Array or Iterator.
>
>

Re: 2 Lists in query statement

[Nathan Maves <Na...@Sun.COM>]

For starters you might want to include the full stack trance.

Second, I am not sure how you plan to use a resultClass of  
ArrayList.  If you need a list of Objects then use the queryForList  
instead of queryForObject.

Third, are you sure that you have both ppidList and profileIdList  
defined in your ProfileProtocolCriteria class?  And if so are they  
both of type Collection?

Nathan

On Jun 15, 2006, at 3:26 PM, Debasish Dutta Roy wrote:

> hi all
> I need to add 2 lists into the query. i.e. I have a select with 2  
> IN clause. Used an object with 2 lists. Passed that to the query.
>
>     <select id="getPSPIds" resultClass="java.util.ArrayList "  
> cacheModel="profile-cache"  
> parameterClass="com.mycriteria.ProfileProtocolCriteria">
>         select distinct psp.profile_study_protocol_id
>         from profile_study_protocol psp, profile_protocol_item ppi
>         where
>             psp.profile_study_protocol_id =  
> ppi.profile_study_protocol_id
>             <dynamic prepend="AND">
>                 <iterate open="psp.profile_id IN(" close=")"  
> conjunction=",">
>                     #[profileIdList]#
>                 </iterate>
>             </dynamic>
>             and psp.status_id is null
>             <dynamic prepend="AND">
>                 <iterate open=" ppi.profile_protocol_item_id IN("  
> close=")" conjunction=",">
>                     #[ppiIdList]#
>                 </iterate>
>             </dynamic>
>             and ppi.is_target is not null
>     </select>
>
> ProfileCriteria has 2 member variables with the names in the  
> dynamic clause.
>
> Getting the error:
>
> -- Cause: com.ibatis.sqlmap.client.SqlMapException: ParameterObject  
> or property was not a Collection, Array or Iterator.