Mapping a nested Oracle CURSOR (ResultSet) with myBatis

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)ISBEGIN  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    }}

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>