如何使用Spring JDBC存储过程从Java将用户定义的数据类型变量的值传递给MS SQL

问题描述

我有以下带有用户定义变量(@Location

I have the below MS SQL store procedure with the user defined variable (@Location

CREATE PROCEDURE [Organization].[Organization_Insert]
(
 @OrganizationID NVARCHAR(256),
 @Location Locationtype ReadOnly
)

@Location具有以下属性:OrganizationSubID，LocationCode

@Location has following attributes: OrganizationSubID, LocationCode

Am使用下面的java类来调用存储过程，

Am using the below java class to invoke the store procedure,

class OrganizationInsertProcedure extends StoredProcedure {

  private final String[] outputParameters = new String[] {OUTPUT};

  public PlanActivityInsertProcedure(DataSource dataSource) {
    super(dataSource, "Organization_Insert");

    declareParameter(new SqlParameter("@OrganizationID", Types.NVARCHAR));
    declareParameter(new SqlParameter("@Location", Types.ARRAY, "Locationtype"));

    compile();
  }

在这里，我的问题是，如何从java构造@Location变量并将其传递给MS SQL数据库. (上午使用sqljdbc4.jar驱动程序连接数据库)

Here, my question is, how to construct the @Location variable from java and pass it to the MS SQL database. (am using sqljdbc4.jar driver to connect the database)

我整天用Google搜索并尝试了许多实现，但没有得到回报.

I whole day Googled and tried many implementations and nothing paid off.

请有人对此有所帮助...

Please someone shed some lights on this...

推荐答案

(我假设您已将Locationtype声明为表变量.在存储过程的参数上使用ReadOnly暗示了这一点. )

(I am going to assume that you have declared Locationtype as a table variable. The use of ReadOnly on the stored procedure's parameter hints at this.)

根据SQL Server论坛上的帖子，Microsoft SQL Server JDBC驱动程序当前不支持表变量.因此，似乎您必须构建一个T-SQL字符串，该字符串声明一个表变量，将数据插入表中，然后执行存储过程.

According to this post on the SQL Server forums, the Microsoft SQL Server JDBC driver doesn't currently support table variables. So, it seems you have to build up a string of T-SQL which declares a table variable, inserts data into the table and then executes the stored procedure.

在普通" JDBC中，即不使用Spring，执行此操作的代码如下所示:

In 'plain' JDBC, i.e. without using Spring, the code to do this looks something like the following:

    int numRows = /* number of rows in table variable */;

    StringBuilder sqlBuilder = new StringBuilder("DECLARE @Location AS LocationType;");
    for (int i = 0; i < numRows; ++i) {
        sqlBuilder.append(
            "INSERT INTO @Location (OrganizationSubID, LocationCode) VALUES (?, ?);");
    }

    sqlBuilder.append("EXEC [Organization].[Organization_Insert] ?, @Location;");

    PreparedStatement stmt = connection.prepareStatement(sqlBuilder.toString());
    for (int i = 0; i < numRows; ++i) {
        stmt.setString(i * 2 + 1, /* OrganizationSubID for row i */);
        stmt.setString(i * 2 + 2, /* LocationCode for row i  */);
    }

    stmt.setString(numRows * 2 + 1, /* Organization ID */);

    ResultSet resultSet = stmt.executeQuery();
    resultSet.close();

(我发现在PreparedStatement上调用executeQuery()比execute()更有帮助.我没有您的sproc代码，所以我使Organization_Insert引发错误，其消息包含表中的行数变量.必须使用executeQuery()才能在SQLException中引发此错误消息:对于execute()，不会引发任何异常.)

(I found it more helpful to call executeQuery() on the PreparedStatement than execute(). I didn't have your sproc code, so I made Organization_Insert raise an error whose message contained the number of rows in the table variable. Using executeQuery() was necessary to get this error message thrown in a SQLException: with execute() no exception was thrown.)

这篇关于如何使用Spring JDBC存储过程从Java将用户定义的数据类型变量的值传递给MS SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！