如何使用MyBatis将Java对象列表传递给Oracle存储过程?
问题描述:
我一直在使用谷歌搜索一段时间,似乎无法找到任何真正的答案。如何使用MyBatis将Java对象列表传递给Oracle存储过程?
我有一个Oracle存储过程,它有一些参数的类型是表rowtype的表。因此,例如:
在pacakge宣称:
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过程?
<update id="callOracleSP" statementType="CALLABLE">
{CALL my_proc(#{param1, mode=IN},
#{param2, mode=IN},
#{param3, mode=IN}
)
}
</update>
对象本身是简单的虚拟组织与字符串和整数属性及其各自getter和setter。
我不太确定如何继续。我是否需要以某种方式将Java对象列表映射到Oracle类型?
在此先感谢。
答
我不知道你是否已经做了,但你需要定义Oracle对象。
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对象。
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}
)
答
安迪·普赖尔的回答是我测试,它真正的作品非常好。但它在类型控制器的错误:
call your_proc
(
#{yourObjects, javaType=Object, jdbcType=ARRAY, jdbcTypeName=YOUR_OBJECT_ARRAY, mode=IN, typeHandler=YourObjectArrayTypeHandler}
)
应该是:
call your_proc
(
#{yourObjects, javaType=Object, jdbcType=ARRAY, jdbcTypeName=YOUR_OBJECT_ARRAY, mode=IN, typeHandler=YourTypeHandler}
)
该类型处理器有错误,以及:(没有“包装”,并没有在方法的参数中的一些差异我的版本)
@Override
public void setParameter(PreparedStatement ps, int i, Object parameter, String arg3) 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 = objects.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);
}
这里是XML映射的例子:
<parameterMap id="updateHierPersonAssignMap" class="java.util.Map" >
<parameter property="p_array" jdbcType="ARRAY" javaType="Object" mode="IN" typeHandler="com.aamtech.ria.model.domain.typehandler.YourTypeHandler"/>
</parameterMap>
<procedure id="updateHierPersonAssign" parameterMap="updateHierPersonAssignMap" >
<![CDATA[
{ call ria_am_util_pkg.j_update_hier_person_assign(?) }
]]>
</procedure>
这里是如何可以从DAO调用它:
public void update(List array) {
Map<String, Object> queryParams = new HashMap<String, Object>();
queryParams.put("p_array", array);
try {
client.update("HashMapResult.updateHierPersonAssign", queryParams);
} catch (SQLException e) {
}
}
我的过程是这样的(它只是将行插入测试表):
Procedure j_update_hier_person_assign (p_array IN YOUR_OBJECT_ARRAY) is
begin
FOR i IN 1..p_array.count LOOP
--dbms_output.put_line();
insert into test (a) values (p_array(i).field_one);
END LOOP;
end;
找不到TypeHandler的类/ jar。已经有ojdbc6.jar – aishu