使用 myBatis 映射嵌套的 Oracle CURSOR (ResultSet)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33556996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mapping a nested Oracle CURSOR (ResultSet) with myBatis
提问by JohnStrong
Is it possible to map a nested java.sql.ResultSetin a myBatis resultMap?
是否可以将嵌套映射java.sql.ResultSet到 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):
还有我的映射器。它返回一个汽车对象列表，还有一个经销商列表（嵌套的 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 dealershipsis an empty List.
这里的问题是，当我检查生成的 java 对象时，它dealerships是一个空列表。
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.
我写了一些普通的旧 java.sql JDBC 代码，它运行良好。任何人都可以让我走上正确的道路吗？我完全迷失了这个。
Thanks in advance.
提前致谢。
Here is the expected SQL output:
这是预期的 SQL 输出：
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 ...
}
回答by diziaq
I made an example to show how it works.
我做了一个例子来展示它是如何工作的。
package modelscontains 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)
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
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_datawith mybatis:
以及get_data用mybatis调用过程的示例：
You are to notice that the selectOnemethod returns null, because we execute a callable statement. All interaction with the procedure call goes using the second parameter: we pass start_fromand receive modelsas 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).
您会注意到该selectOne方法返回 null，因为我们执行了一个可调用语句。与过程调用的所有交互都使用第二个参数：我们作为 Result 对象的字段传递start_from和接收models（MyBatis 可以通过反射获取和设置它们）。所以 Result 对象在方法调用之前和之后是相同的：你甚至可以将字段设为私有（这里我将它们设为 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
}
}
回答by are
from ibatis examples:
来自ibatis的例子：
<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
查看来自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>
<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>
