使用列表参数调用存储过程

本文介绍了使用列表参数调用存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我无法弄清楚如何使用myBatis将列表作为参数发送到SQL Server存储过程

     call sp(List<Object>)

我在SQL Server(2012)内部有一个存储过程，该存储过程采用类型为list的参数.

 CREATE TypeTable of Table
 (
   @FKId IN
@FKId INT
@FKId INT
@FKId INT
@FKId INT
@userName VARCHAR
)

我的存储过程调用

 ALTER PROCEDURE SP(@TypeTableList Typetable READONLY )

  AS
  BEGIN
  /*  My DB Operations To Enter New Records and Thier Child Records */
  END

MyMapper

<select id="mapperId" parameterType="map" statementType="CALLABLE">
     call sp(#{list})
</select>

POJO

public class ListClass {

private Long fk1;
private Long fk2;
private Long fk3;
private Long fk4;
private Long fk5;
private String userName;

public ListClass() {
    super();
}

public Long getFk1() {
    return fk1;
}

public void setFk1(Long fk1) {
    this.fk1 = fk1;
}

public Long getFk2() {
    return fk2;
}

public void setFk2(Long fk2) {
    this.fk2 = fk2;
}

public Long getFk3() {
    return fk3;
}

public void setFk3(Long fk3) {
    this.fk3 = fk3;
}

public Long getFk4() {
    return fk4;
}

public void setFk4(Long fk4) {
    this.fk4 = fk4;
}

public Long getFk5() {
    return fk5;
}

public void setFk5(Long fk5) {
    this.fk5 = fk5;
}

public String getuserName() {
    return userName;
}

public void setuserName(String userName) {
    this.userName = userName;
}
 }

我尝试使用类型数组的类型处理程序，但是我总是遇到异常.

在使用SQL Server为ArrayList创建自定义类型处理程序时，我找不到任何资源

任何帮助都将被申请

谢谢

解决方案

您发布的create语句在SQL Server 2017中不起作用，因此，我将向您展示一个更简单的示例.

DDL:

 create table Users (
  id int,
  name varchar(20)
);

create type UserTableType as table (
  id int,
  name varchar(20)
);

create procedure uspAddUsers
  @UserTable UserTableType READONLY
as
begin
    insert into Users (id, name)
      select * from @UserTable
end;

POJO:

 public class User {
  private Integer id;
  private String name;
  // getter/setter
}

映射器方法:

 @Options(statementType = StatementType.CALLABLE)
@Insert("{call uspAddUsers(#{users,typeHandler=pkg.UserListTypeHandler})}")
void insertUsers(@Param("users") List<User> users);

请注意typeHandler选项.
正如David Browne指出的那样，驱动程序需要SQLServerDataType作为输入，因此您可能需要一个类型处理程序，将列表转换为SQLServerDataType.
下面是一个简单的类型处理程序实现.

 import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;

public class UserListTypeHandler extends BaseTypeHandler<List<User>>{
  @Override
  public void setNonNullParameter(PreparedStatement ps,
      int i, List<User> parameter, JdbcType jdbcType)
      throws SQLException {
    SQLServerDataTable dataTable = new SQLServerDataTable();
    dataTable.addColumnMetadata("id", java.sql.Types.INTEGER);
    dataTable.addColumnMetadata("name", java.sql.Types.VARCHAR);
    for (User user : parameter) {
      dataTable.addRow(user.getId(), user.getName());
    }
    ps.unwrap(SQLServerPreparedStatement.class)
      .setStructured(i, "UserTableType", dataTable);
  }
  // getNullableResult() won't be used
}

可执行文件演示已通过...进行了测试... /p>

Microsoft SQL Server 2017(RTM-CU15)(KB4498951)-14.0.3162.1(X64)
mssql-jdbc 7.3.1.jre8-preview

I am unable to figure out how to send a list as a parameter to SQL Server Stored Procedure using myBatis

     call sp(List<Object>)

I have a stored procedure inside a SQL Server(2012) which takes a parameter of type list.

 CREATE TypeTable of Table
 (
   @FKId IN
@FKId INT
@FKId INT
@FKId INT
@FKId INT
@userName VARCHAR
)

My Stored Procedure call

 ALTER PROCEDURE SP(@TypeTableList Typetable READONLY )

  AS
  BEGIN
  /*  My DB Operations To Enter New Records and Thier Child Records */
  END

MyMapper

<select id="mapperId" parameterType="map" statementType="CALLABLE">
     call sp(#{list})
</select>

POJO

public class ListClass {

private Long fk1;
private Long fk2;
private Long fk3;
private Long fk4;
private Long fk5;
private String userName;

public ListClass() {
    super();
}

public Long getFk1() {
    return fk1;
}

public void setFk1(Long fk1) {
    this.fk1 = fk1;
}

public Long getFk2() {
    return fk2;
}

public void setFk2(Long fk2) {
    this.fk2 = fk2;
}

public Long getFk3() {
    return fk3;
}

public void setFk3(Long fk3) {
    this.fk3 = fk3;
}

public Long getFk4() {
    return fk4;
}

public void setFk4(Long fk4) {
    this.fk4 = fk4;
}

public Long getFk5() {
    return fk5;
}

public void setFk5(Long fk5) {
    this.fk5 = fk5;
}

public String getuserName() {
    return userName;
}

public void setuserName(String userName) {
    this.userName = userName;
}
 }

I have tried using type handler of type array but i always get a exception.

I have not found any resources on creating a custom type handler for ArrayList With SQL Server

Any Help would be appriciated

Thankyou

解决方案

The create statement you posted didn't work in SQL Server 2017, so I'll show you a simpler example.

DDLs:

create table Users (
  id int,
  name varchar(20)
);

create type UserTableType as table (
  id int,
  name varchar(20)
);

create procedure uspAddUsers
  @UserTable UserTableType READONLY
as
begin
    insert into Users (id, name)
      select * from @UserTable
end;

POJO:

public class User {
  private Integer id;
  private String name;
  // getter/setter
}

Mapper method:

@Options(statementType = StatementType.CALLABLE)
@Insert("{call uspAddUsers(#{users,typeHandler=pkg.UserListTypeHandler})}")
void insertUsers(@Param("users") List<User> users);

Note the typeHandler option.
As David Browne pointed out, the driver requires SQLServerDataType as the input, so you may need a type handler that converts the list into a SQLServerDataType.
The below is a simple type handler implementation.

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;

public class UserListTypeHandler extends BaseTypeHandler<List<User>>{
  @Override
  public void setNonNullParameter(PreparedStatement ps,
      int i, List<User> parameter, JdbcType jdbcType)
      throws SQLException {
    SQLServerDataTable dataTable = new SQLServerDataTable();
    dataTable.addColumnMetadata("id", java.sql.Types.INTEGER);
    dataTable.addColumnMetadata("name", java.sql.Types.VARCHAR);
    for (User user : parameter) {
      dataTable.addRow(user.getId(), user.getName());
    }
    ps.unwrap(SQLServerPreparedStatement.class)
      .setStructured(i, "UserTableType", dataTable);
  }
  // getNullableResult() won't be used
}

An executable demo tested with...

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64)
mssql-jdbc 7.3.1.jre8-preview

这篇关于使用列表参数调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！