如何将用户定义的类型作为输入传递给存储过程？

Question

我有两个涉及用户定义类型的相关存储过程。第一个接受一个对象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();
}

- dave

Answer 1

经过一番折腾，我成功地开发出了一个解决方案。以下是我的几点观察：

• 网络上关于如何做到这一点的文档很少。
• 在我看来，使用用户定义类型作为输入并不得到很好的支持。
• 我发现我必须使用一个结构体，这是反直觉的（因为只有数组被用于输出）。
• 没有使用SQLData接口，即writeSQL()从未被调用，因为我发现我必须手动构建结构体。readSQL()在映射输出时被调用。
• 我不得不使用特定于数据库的代码来创建数组，在我这种情况下，这意味着使用Oracle类。

可能我走了一些错误的路，所以我欢迎对我的解决方案进行评论。

public void useExample(Example example) throws SQLException {
    String query = "begin example.useExample(?); end;";
    Connection connection = jdbcTemplate.getDataSource().getConnection();
    CallableStatement callableStatement = connection.prepareCall(query);

    Map<String, Class<?>> typeMap = connection.getTypeMap();
    typeMap.put(Example.SQL_OBJECT_TYPE_NAME, ExampleMapper.class);
    connection.setTypeMap(typeMap);

    // Manually convert the example object into an SQL type.
    Object[] exampleAttributes = new Object[]{example.ID, example.Name};
    Struct struct = connection.createStruct(type.getObjectType(), exampleAttributes);

    // Build the array using Oracle specific code.
    DelegatingConnection<OracleConnection> delegatingConnection = (DelegatingConnection<OracleConnection>) new DelegatingConnection(connection);
    OracleConnection oracleConnection = (OracleConnection) delegatingConnection.getInnermostDelegate();
    Object[] data = new Object[]{struct};
    Array array oracleConnection.createOracleArray(Example.SQL_TABLE_TYPE_NAME, data);

    // Set the input value (finally).
    callableStatement.setObject("p_example", array);

    callableStatement.execute();
}

- dave

Answer 2

您不必手动转换数据。而且您绝对不需要使用 Struct。这是简化版本：

final OracleConnection oracleConnection = (OracleConnection) connection.getClass().
    getMethod("getUnderlyingConnection").invoke(connection);

List<Example> example = new ArrayList<>();
example.add(new Example(1L, "something"));
example.add(new Example(2L, "something else"));

Map<String, Class<?>> typeMap = connection.getTypeMap();
typeMap.put(Example.SQL_OBJECT_TYPE_NAME, Example.class);
connection.setTypeMap(typeMap);

Array array = oracleConnection.createOracleArray(Example.SQL_TABLE_TYPE_NAME, example.toArray());

statement.setObject(1, array);
statement.execute();

请注意，我将Example和ExampleMapper合并为一个类（为了简单起见）。你的示例中也有错误之处：

@Override
public String getSQLTypeName() throws SQLException {
    return SQL_OBJECT_TYPE_NAME;
}

从代码可以看出，这个覆盖方法必须返回对象类型名称而不是表类型名称。

- paaro