使用 JDBC 将用户定义的表类型传递给 SQL Server 存储过程

问题描述

我们在 SQL Server 中有这个用户定义的表类型:

We've got this User-Defined-Table Type in SQL Server:

CREATE TYPE [dbo].[INITVALS_MSG] AS TABLE(
    [SDate] [decimal](8, 0) NOT NULL,
    [EDate] [decimal](8, 0) NOT NULL,
    [PlantCode] [nvarchar](10) NOT NULL,
    [LoadType] [nchar](8) NOT NULL,
    [Asset] [bigint] NOT NULL
)

和一个将该表作为输入的存储过程:

and a Stored Procedure which takes that table as input:

ALTER PROCEDURE [dbo].[RegisterInitAssets]
    @initmsg INITVALS_MSG ReadOnly
AS
BEGIN
    ...

现在，我需要从 java 调用这个过程.有可能做这样的事情吗?JDBC 支持吗?

Now, I need to call this procedure from java. Is it possible to do such a thing? Does JDBC support this?

--------编辑我在 java 中有一个对应的类用于该类型:

--------EDITI have a corresponding class in java for that type:

public class DBInitialAsset {
    private Integer sDate;
    private Integer eDate;
    private String plantCode;
    private String loadType;
    private Integer asset;

    public DBInitialAsset() {
    }
}

推荐答案

是的，现在可以了.用于 SQL Server 的 Microsoft JDBC 驱动程序 6.0 版增加了对表值参数的支持.

Yes, it is now possible. Version 6.0 of Microsoft's JDBC driver for SQL Server added support for table-valued parameters.

以下代码示例展示了如何

The following code sample shows how to

• 使用一个SQLServerDataTable对象来保存要传递的表数据，以及
• 调用 SQLServerCallableStatement#setStructured 方法将该表传递给存储过程.

• use a SQLServerDataTable object to hold the table data to be passed, and
• call the SQLServerCallableStatement#setStructured method to pass that table to the stored procedure.

SQLServerDataTable sourceDataTable = new SQLServerDataTable();
sourceDataTable.addColumnMetadata("SDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("EDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("PlantCode", java.sql.Types.NVARCHAR);
sourceDataTable.addColumnMetadata("LoadType", java.sql.Types.NCHAR);
sourceDataTable.addColumnMetadata("Asset", java.sql.Types.BIGINT);

// sample data
sourceDataTable.addRow(123, 234, "Plant1", "Type1", 123234);
sourceDataTable.addRow(456, 789, "Plant2", "Type2", 456789);

try (CallableStatement cs = conn.prepareCall("{CALL dbo.RegisterInitAssets (?)}")) {
    ((SQLServerCallableStatement) cs).setStructured(1, "dbo.INITVALS_MSG", sourceDataTable);
    boolean resultSetReturned = cs.execute();
    if (resultSetReturned) {
        try (ResultSet rs = cs.getResultSet()) {
            rs.next();
            System.out.println(rs.getInt(1));
        }
    }
}

有关详细信息，请参阅以下 MSDN 文章:

For more details, see the following MSDN article:

使用表值参数

这篇关于使用 JDBC 将用户定义的表类型传递给 SQL Server 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！