如何使用MyBatis将对象的Java列表传递给Oracle存储过程?

问题描述

我已经使用谷歌搜索了一段时间，但似乎找不到真正的答案.

I have been googling this for a while and cannot seem to find any real answers.

我有一个Oracle存储过程，该存储过程具有许多in参数，这些参数的类型是表rowtype的表.例如:

I have an Oracle stored procedure that has a number of in parameters that have a type that is table of the table rowtype. So for example:

在任务栏中声明:

TYPE param1_type_t IS TABLE OF table1%ROWTYPE;
TYPE param2_type_t IS TABLE OF table2%ROWTYPE;
TYPE param3_type_t IS TABLE OF table3%ROWTYPE;

Oracle过程:

PROCEDURE my_proc
(
   parameter1    IN param1_type_t,
   parameter2    IN param2_type_t,
   parameter3    IN param3_type_t
)

在Java方面，我有3个对应的对象列表，它们表示用Java填充的每个参数.在这种情况下是否可以使用MyBatis调用Oracle过程?

On the java side, I have 3 corresponding Lists of objects representing each of the parameters that are populated in Java. Is it possible to call the Oracle procedure using MyBatis in this scenario?

<update id="callOracleSP" statementType="CALLABLE">
    {CALL my_proc( #{param1, mode=IN},
                   #{param2, mode=IN},
                   #{param3, mode=IN}
                 )
    }
</update>

对象本身是具有String和Integer属性以及它们各自的getter和setter的简单VO.

The objects themselves are simple VOs with String and Integer properties and their respective getters and setters.

我不太确定该如何进行.我是否需要以某种方式将Java对象列表映射到Oracle类型?

I am not really sure how to proceed. Do I need to somehow map the Java object lists to the Oracle types?

推荐答案

我无法确定您是否已经做过，但是您需要定义Oracle对象.

I can't tell if you do already or not, but you'll need Oracle objects defined.

CREATE OR REPLACE TYPE SCHEMA."YOUR_OBJECT" AS OBJECT
(
    field_one    varchar2(50),
    field_two    varchar2(100)
);
/
CREATE OR REPLACE TYPE SCHEMA."YOUR_OBJECT_ARRAY" AS TABLE OF YOUR_OBJECT;
/

然后，您可以编写类型处理程序以将Java对象映射到Oracle对象.

Then you can write type handlers to map the Java objects to the Oracle objects.

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
....
public class YourTypeHandler implements TypeHandler
{
....
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException
    {
        List<YourObject> objects = (List<YourObject>) parameter;

        StructDescriptor structDescriptor = StructDescriptor.createDescriptor("YOUR_OBJECT", ps.getConnection());

        STRUCT[] structs = new STRUCT[objects.size()];
        for (int index = 0; index < objects.size(); index++)
        {
            YourObject pack = packs.get(index);
            Object[] params = new Object[2];
            params[0] = pack.getFieldOne();
            params[1] = pack.getFieldTwo();
            STRUCT struct = new STRUCT(structDescriptor, ps.getConnection(), params);
            structs[index] = struct;
        }

        ArrayDescriptor desc = ArrayDescriptor.createDescriptor("YOUR_OBJECT_ARRAY", ps.getConnection());
        ARRAY oracleArray = new ARRAY(desc, ps.getConnection(), structs);
        ps.setArray(i, oracleArray);
    }
}

然后调用该过程

call your_proc
(
#{yourObjects, javaType=Object, jdbcType=ARRAY, jdbcTypeName=YOUR_OBJECT_ARRAY, mode=IN, typeHandler=YourObjectArrayTypeHandler}
)

这篇关于如何使用MyBatis将对象的Java列表传递给Oracle存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！