如何使用 MyBatis 将 Java 对象列表传递给 Oracle 存储过程?
我已经在谷歌上搜索了一段时间,似乎找不到任何真正的答案.
I have been googling this for a while and cannot seem to find any real answers.
我有一个 Oracle 存储过程,它有许多 in 参数,这些参数的类型是表行类型的表.例如:
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>
对象本身是简单的 VO,具有 String 和 Integer 属性以及它们各自的 getter 和 setter.
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}
)
相关文章