且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

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

更新时间:2023-02-07 12:13:03

是的,现在可以了. Microsoft的SQL Server 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:

使用表值参数