java - 使用myBatis映射嵌套的Oracle CURSOR(ResultSet)

是否可以在myBatis java.sql.ResultSet中映射嵌套的resultMap？

例如。假设我有一个过程映射定义如下:

 <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>

还有我的 map 绘制者它返回汽车对象的列表，还返回经销商列表(嵌套的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 -->

这里的问题是，当我检查生成的Java对象dealerships是一个空列表时。

我写了一些简单的旧java.sql JDBC代码，它运行良好。有人可以把我放在正确的道路上吗？我完全迷失了这个。

提前致谢。

这是预期的SQL输出:

Car
|name  |dealerships|
|nissan|ref_cursor|

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

车型:

public class Car {

 private String name;
 private List<Dealership> dealerships;

 // getters & setters ...

}

public class Dealership {

  private String model;
  private Integer year;

  // getters & setters ...
}

最佳答案

我举了一个例子来说明它是如何工作的。

软件包模型包含两个类:

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

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

存储过程

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 (您必须提供数据库的URL)

<?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>

以及使用mybatis调用过程get_data的示例:

您会注意到selectOne方法返回null，因为我们执行了一个可调用的语句。与过程调用的所有交互都使用第二个参数:我们传递start_from并接收models作为Result对象的字段(MyBatis可以通过反射来获取和设置它们)。因此，在方法调用之前和之后Result对象都是相同的:您甚至可以将字段设为私有(private)(在这里我将其保留为公共(public)状态，以使代码更短)。

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