Oracle refcursor in output variable problem: nested mapper not recognized as being nested

[Dagostino, Ronald]

I believe there is a bug when referring to a nested map definition in an output variable.  For example, given the following select to call an Oracle stored procedure:

        <select statementType="CALLABLE" id="getUserByAlias"

                parameterType="MyBatisBean" resultType="object">

                call PKG_USER_DATA_SERVICE_MYBATIS.get_user_by_alias (

                #{alias}

                ,#{retvalUsers,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=DetailedObjUserVo}

                )

        </select>

I have the following nested resultMap definition:

        <resultMap id="DetailedObjUserVo" type="ObjUserVo">

                <id property="userId" column="user_id" />

                <result property="firstName" column="first_name" />

                <result property="middleName" column="middle_name" />

                <result property="lastName" column="last_name" />

                <collection property="accounts" javaType="List"

                        ofType="ObjAccountVo">

                        <id property="accountId" column="account_id" />

                        <result property="alias" column=" alias" />

                </collection>

        </resultMap>

Unfortunately MyBatis doesn’t seem to be recognizing the resultMap as being nested.  For example, in a simple case I should be getting one user with 2 accounts in the collection, but instead I am getting 2 users, each with 2 accounts.  I’ve stepped through the code with the debugger, and I confirm that in the following Configuration code the mappedStatement.hasNestedResultMaps() method is returning false and therefore I am *not* getting an instance of NestedResultSetHandler, which result in two users each with no accounts:

public ResultSetHandler newResultSetHandler(Executor executor, MappedStatement mappedStatement, RowBounds rowBounds, ParameterHandler parameterHandler, ResultHandler resultHandler, BoundSql boundSql) {

    ResultSetHandler resultSetHandler = mappedStatement.hasNestedResultMaps() ?

        new NestedResultSetHandler(executor, mappedStatement, parameterHandler, resultHandler, boundSql, rowBounds)

        : new FastResultSetHandler(executor, mappedStatement, parameterHandler, resultHandler, boundSql, rowBounds);

    resultSetHandler = (ResultSetHandler) interceptorChain.pluginAll(resultSetHandler);

    return resultSetHandler;

  }

I correctly get one user with 2 accounts when I use the debugger to cause mappedStatement.hasNestedResultMaps() to return true.

Ron

P.S. Also, BTW, I believe the “javaType=java.sql.ResultSet” in the SELECT definition is required even though the user guide says it is not – I get the following error if I leave it out:

### Error querying database.  Cause: org.apache.ibatis.executor.ExecutorException: Type handler was null on parameter mapping for property retvalUsers.  It was either not specified and/or could not be found for the javaType / jdbcType combination specified.

### The error may involve PkgUserDataServiceMapper.getUserByAlias-Inline

### The error occurred while setting parameters

### Cause: org.apache.ibatis.executor.ExecutorException: Type handler was null on parameter mapping for property retvalUsers.  It was either not specified and/or could not be found for the javaType / jdbcType combination specified.

CallStack=org.apache.ibatis.exceptions.PersistenceException:

### Error querying database.  Cause: org.apache.ibatis.executor.ExecutorException: Type handler was null on parameter mapping for property retvalUsers.  It was either not specified and/or could not be found for the javaType / jdbcType combination specified.

### The error may involve PkgUserDataServiceMapper.getUserByAlias-Inline

### The error occurred while setting parameters

### Cause: org.apache.ibatis.executor.ExecutorException: Type handler was null on parameter mapping for property retvalUsers.  It was either not specified and/or could not be found for the javaType / jdbcType combination specified.

        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)

        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:61)

        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:53)

        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:38)

        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:66)

        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)

        at $Proxy6.getUserByAlias(Unknown Source)

etc…

[Ron Dagostino]

Opened the following issues:

#29 "javaType=java.sql.ResultSet" required in select definition even
though user guide says it is not
(http://code.google.com/p/mybatis/issues/detail?id=29)

#30 Oracle refcursor in output variable: nested mapper not recognized
as being nested
(http://code.google.com/p/mybatis/issues/detail?id=30)

> - I get the following error if I leave it out:

> etc...

[Ron Dagostino]

There is a workaround for this problem: set the resultMap at the top-
level select in addition to setting it at the parameter level. For
example:

<select statementType="CALLABLE" id="getUserByLdapAlias"
resultMap="DetailedObjUserVo"
parameterType="MyBatisBean" resultType="object">
call PKG_USER_DATA_SERVICE_MYBATIS.get_user_by_ldap_alias (
#{ldapAlias}
,#{retvalUsers,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=DetailedObjUserVo}
)
</select>

I don't know if this is the way it should work -- the resultMap of the
output parameter being identified as nested based on whether the
resultMap at the top level select is nested -- but it seems to be the
way it works as of v3.0.1. This might become a problem if there are
multiple output parameters and not all of them are nested -- not sure.

Added above comment to issue #30 as well.

[daver]

Thanks for this info Ron. Was about to run headlong into it had you
not posted this solution workaround. We're heavily using Oracle SP's
and MyBatis for mapping.