Mapping a nested Oracle CURSOR (ResultSet) with myBatis

Question

Is it possible to map a nested java.sql.ResultSet in a myBatis resultMap?

For example. Say that I have a procedure mapping defined like so:

 <select id="selectBlog" statementType="CALLABLE">
        {call getCarsByYear(
           #{year,jdbcType=INTEGER,mode=IN},
           #{results, jdbcType=CURSOR, mode=OUT, javaType=java.sql.ResultSet, jdbcType=CURSOR, resultMap=cars}
        )}
    </select>

And my mapper. which returns a list of car objects, but also a list of dealerships (the nested CURSOR):

<resultMap id="cars" type="some.package.Car">
   <result property="name" column="car_name">
   <!-- here is my problem -->
   <collection property="dealerships" column="dealerships_rf" ofType="some.package.Dealership">
       <result property="model" column="model" />
       <result property="year" column="year" />
</resultMap>

<!-- dealership resultMap of type some.package.Dealership -->

Problem here is, when I inspect the resulting java object dealerships is an empty List.

I wrote some plain old java.sql JDBC code and it worked fine. Cany anyone put me on the right path? I am completely lost with this one.

Thanks in advance.

Here is the expected SQL output:

Car
|name  |dealerships|
|nissan|ref_cursor|

Dealership
|location     |established|....
|....         |1974       |...

Cars model:

public class Car {

 private String name;
 private List<Dealership> dealerships;

 // getters & setters ...

}

public class Dealership {

  private String model;
  private Integer year;

  // getters & setters ...
}

Answer 1

I made an example to show how it works.

package models contains two classes:

public class Result {
    public int start_from;    
    public List<Model> models;
}

public class Model {
    public int a;
    public String b;
}

stored procedure

CREATE OR REPLACE PROCEDURE get_data( p_start IN NUMBER
                                    , p_cur   OUT SYS_REFCURSOR)
IS
BEGIN

  OPEN p_cur FOR
    SELECT p_start a,'abc' b FROM dual
          UNION ALL
    SELECT p_start + 1,'cde' FROM dual
          UNION ALL
    SELECT p_start + 2,'xyz' FROM dual;

END;

mybatis-config.xml (you must provide the URL for Database)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="oracle.jdbc.OracleDriver"/>
        <property name="url" value="${set_the_url}"/>
      </dataSource>
    </environment>
  </environments>

  <mappers>
    <mapper resource="mybatis-mapper.xml"/>
  </mappers>
</configuration>

mybatis-mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

  <resultMap id="map_res_4" type="models.Model">
    <result property="a" column="a"/>
    <result property="b" column="b"/>
  </resultMap>

  <parameterMap id="map_par_4" type="models.Result">
    <parameter property="start_from" jdbcType="INTEGER" mode="IN"  />
    <parameter property="models" jdbcType="CURSOR" mode="OUT" resultMap="map_res_4"  />
  </parameterMap>

  <select id="select_4" parameterMap="map_par_4" statementType="CALLABLE">
    {CALL get_data(?, ?)}
  </select>

</mapper>

And the sample of calling the procedure get_data with mybatis:

You are to notice that the selectOne method returns null, because we execute a callable statement. All interaction with the procedure call goes using the second parameter: we pass start_from and receive models as fields of Result object (MyBatis can get and set them through reflection). So the Result object is the same before and after the method call: you can even make the fields private (here I left them public to keep the code shorter).

import models.Result;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;

/**
 *
 */
public class Main {
    private static SqlSessionFactory sessionFactory = null;
    private static String CONFIGURATION_FILE = "mybatis-config.xml";

    static {
        try {
            Reader reader = Resources.getResourceAsReader(CONFIGURATION_FILE);
            sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String... args) {

        SqlSession session = sessionFactory.openSession();

        Result res = new Result();

        res.start_from = 5;

        Object obj = session.selectOne("select_4", res);

        // `obj` must be NULL
        // `res` contains all the results of Oracle procedure call
    }
}

Answer 2

from ibatis examples:

<parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>

the difference from you code:

 - javaType was specified
 - jdbcType = ORACLECURSOR

see the examples from iBatis Oracle REF CURSOR

   <sqlMap namespace="KOMUNIKA_REPORT">
   <resultMap id="BaseResultMap" class="javaapplication4.StockAreaAndWarehouse" >
   <result column="PRODUCT_CODE" property="productCode"  />
   <result column="PRODUCT_NAME" property="productName" />
   <result column="INCOMING" property="incoming" />
   <result column="UNIT_SOLD" property="unitSold" />
   <result column="TOTAL_STOCK" property="totalStock" />
   </resultMap>
   <parameterMap id="resultMap" class="java.util.Map">
   <parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
   </parameterMap>
   <procedure id="selectStockAreaAndWarehouse"
   parameterMap="resultMap"
   resultMap="BaseResultMap"
   >
   { call KOMUNIKA.LP_STOCK_AREA_WAREHOUSE(?) }
   </procedure>
   </sqlMap>

example for ResultSet

<resultMap id="userDataResultMap" type="TestUserData"> 
<id property="userid" column="userid" /> 
<result property="firstName" column="firstName"/> 
<result property="lastName" column="lastName"/> 
<result property="zip" column="zip"/> 
<result property="summary" column="summary"/> 
<result property="specialities" column="specialities"/> 
<result property="isActive" column="isActive"/> 
<result property="country" column="country"/> 
<result property="platform" column="platforms"/> 
</resultMap>
<select id="getFullPublicData" statementType="CALLABLE" parameterType="User" >
{call p_user_public_data(#{userId}
,#{userDataList,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet, resultMap=com.test.data.UserPublicViewMapper.userDataResultMap}
,#{noOfConnections,mode=OUT,jdbcType=NUMERIC,javaType=int}
,#{noOfRecommendations,mode=OUT,jdbcType=NUMERIC,javaType=int})}
</select>