我有两个涉及用户定义类型的相关存储过程。第一个接受一个对象ID并返回相应的用户定义类型实例。第二个接受同一用户定义类型的实例并对其进行某些操作。
我正在使用Java、JDBC和少量Spring JDBC。我已经成功完成了第一个存储过程,也就是我可以从数据库检索到我的用户定义类型的实例,但我无法使第二个存储过程工作。
这是我目前所拥有的基本概述:
模式(PL/SQL)
create or replace type example_obj as object
(ID NUMBER,
NAME VARCHAR2(100))
create or replace type example_tab as table of example_obj
create or replace package
example as
procedure getExample
(p_id in number,
p_example out example_tab);
procedure useExample
(p_example in example_tab);
end example;
实体(Java)- 表示Java中用户自定义的类型。
public class Example {
public BigDecimal ID;
public String Name;
}
Mapper(Java)-将SQL类型映射到Java类型并反向映射
public class ExampleMapper extends Example implements SQLData {
public static final String SQL_OBJECT_TYPE_NAME = "example_obj";
public static final String SQL_TABLE_TYPE_NAME = "example_tab";
@Override
public String getSQLTypeName() throws SQLException {
return SQL_TABLE_TYPE_NAME;
}
@Override
public void readSQL(SQLInput stream, String typeName) throws SQLException {
ID = stream.readBigDecimal();
Name = stream.readString();
}
@Override
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeBigDecimal(ID);
stream.writeString(Name);
}
}
第一个存储过程(Java)- 根据其ID检索Example对象
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import org.springframework.jdbc.core.JdbcTemplate;
public Example getExample(BigDecimal ID) throws SQLException {
String query = "begin example.getExample(?, ?); end;";
Connection connection = jdbcTemplate.getDataSource().getConnection();
CallableStatement callableStatement = connection.prepareCall(query);
callableStatement.setBigDecimal("p_id", ID);
Map<String, Class<?>> typeMap = connection.getTypeMap();
typeMap.put(Example.SQL_OBJECT_TYPE_NAME, ExampleMapper.class);
callableStatement.registerOutParameter("p_example", Types.ARRAY, Example.SQL_TABLE_TYPE_NAME);
connection.setTypeMap(typeMap);
callableStatement.execute();
Array array = (Array)callableStatement.getObject("p_example");
Object[] data = (Object[])array.getArray();
Example example = (Example)data[0]; // It's an ExampleMapper, but I only want Example
return example;
}
正如我之前所指出的,第一个存储过程运行正确。从数据库检索到的对象会自动映射到相应的Java对象中。下一步是能够调用接受此用户定义类型实例的存储过程。
第二个存储过程(Java)- 使用示例对象 - 不完整
public void useExample(Example example) throws SQLException {
String query = "begin example.useExample(?); end;";
Connection connection = jdbcTemplate.getDataSource().getConnection();
CallableStatement callableStatement = connection.prepareCall(query);
// Is this required (as per getExample())?
Map<String, Class<?>> typeMap = connection.getTypeMap();
typeMap.put(Example.SQL_OBJECT_TYPE_NAME, ExampleMapper.class);
connection.setTypeMap(typeMap);
/***
*** What goes here to pass the object in as a parameter?
***/
callableStatement.setObject("p_example", ???);
callableStatement.execute();
}