如何使用Spring JDBC存储过程从Java将用户定义的数据类型变量的值传递给MS SQL [英] How to pass the values of user defined datatype variable to MS SQL from java using spring jdbc store procedure

问题描述

我有以下带有用户定义变量(@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.)

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！