com.ibatis.common.jdbc.exception.NestedSQLException:

Question

I am gettng this exception for this code

 List<String> addlCarrierClause=new ArrayList<String>();

<select id="GET_SEARCH_RESULTS" parameterClass="Map" resultClass="HashMap">

<isNotEmpty  prepend=" AND " property="addlClauseGtwyTemp">
    l.imp_gtwy_i in
        <iterate property="addlClauseGtwyTemp" open="(" close=")" conjunction=",">
            #addlClauseGtwyTemp[]#
        </iterate>
</isNotEmpty>

com.ibatis.common.jdbc.exception.NestedSQLException:

The error occurred while preparing the mapped statement for execution.  
--- Check the GET_SEARCH_RESULTS.  
--- Check the parameter map.  
--- Cause: com.ibatis.sqlmap.client.SqlMapException: ParameterObject or property was not a Collection, Array or Iterator.
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)

Am I using the iterate tag correctly?
What else do i need to check to implement this

Answer 1

You use iBatis <iterate> tag only when you want to cycle through a list of items. Your iterate tag usage is correct and your property addlClauseGtwyTemp exists otherwise you'll be getting a NullPointerException which you aren't getting. So your problem simply is that your addlClauseGtwyTemp isn't a list or an iterable collection (implementing an iterable interface) but a flat value.

I was able to simulate your issue locally and i got the same stack trace in my logs

2013-09-02 17:04:59,724 ERROR UNEXPECTED_EXCEPTION: An unexpected error occurred processing wlm.search
com.xyz.event.InvalidEventStateException: wlm.searchCO:
--- The error occurred in workListManagementSearchService.xml.
--- The error occurred while preparing the mapped statement for execution.
--- Check the getWLMSearchCustomers.
--- Check the parameter map.
--- Cause: com.ibatis.sqlmap.client.SqlMapException: ParameterObject or property was not a Collection, Array or Iterator.
        at com.xyz.event.Query.execute(Query.java:67)
        at com.xyz.event.AbstractParentExecutable.executeChildren(AbstractParentExecutable.java:35)
        at com.xyz.event.Event.execute(Event.java:32)
        at com.xyz.event.impl.d$a.processEvent(EventContextFactory.java:56)

SOLUTION

Ensure that your property addlClauseGtwyTemp is a list or a collection and not a flat value.

LIST

addlClauseGtwyTemp = [Asia,America,Austalia,Africa,SouthAmerica,Europe] 

NON LIST

addlClauseGtwyTemp = Asia

EXTRA TIPS

In your case you are guarding and checking this property addlClauseGtwyTemp is not null and not empty ("" or size() < 1) using <isNotEmpty> tag.

Well, that's fine and i'll also commend that using <isNotEmpty> is more efficient than using <isNotNull> since <isNotNull> only checks if the property is not null but does not check if the property addlClauseGtwyTemp is not empty that is, the size of the property addlClauseGtwyTemp is less than 1 size() < 1. If it is null or if the size is less than 1 then the code nested in that tag will not run. But what happens if that property fails to met your <isNotEmpty> condition? Using your code that you have pasted in your question the query will throw an error. This means that you have to have a preceding code that does the reverse of this to handle situations where the condition fails to be satisfied.

<select id="GET_SEARCH_RESULTS" parameterClass="Map" resultClass="HashMap">

    <isNotEmpty  prepend=" AND " property="addlClauseGtwyTemp">
        l.imp_gtwy_i in
        <iterate property="addlClauseGtwyTemp" open="(" close=")" conjunction=",">
        #addlClauseGtwyTemp[]#
        </iterate>
    </isNotEmpty>

    <isEmpty  prepend=" AND " property="addlClauseGtwyTemp">
       1=2
    </isEmpty>

    <isNull property="addlClauseGtwyTemp">
          select 1 from dual (Oracle syntax)
          select 1 (Postgres/MySQL)
    </isNull>

</select>

I added a fragment to check the reverse of isNotEmpty using <isEmpty> tag and i also checked if addlClauseGtwyTemp is null using <isNull>. If it is null then the query does nothing rather than throw an error.