如何将参数列表传递给存储过程并在SQL Server中执行批量插入 [英] How to pass a list of parameter to stored procedure and perform batch insert in SQL Server

问题描述

我正在使用Spring JDBCTemplate连接到SQL Server.

我有一个需要插入到SQL Server表中的对象列表.

我所做的是以下事情:

public void batchInsert(final List<Bean> list) {

    final String sql = "insert into temp"
            + "(id, name, amount, location, time, price) "
            + " values (?, ?, ?, ?, ?, ?)";

    getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Bean vo = list.get(i);
            ps.setString(1, vo.getId());
            ps.setString(2, vo.getName());
            ps.setDouble(3, vo.getAmount());
            ps.setString(4, vo.getLocation());
            ps.setString(5, vo.getTime());
            ps.setDouble(6, vo.getPrice());
        }

        @Override
        public int getBatchSize() {
            return list.size();
        }
    });
}

但是现在，我想将参数List<Bean> list传递给存储过程，该存储过程将尽可能高效地处理批处理插入.

请问如何实施?

解决方案

您可以将Table变量参数用于存储过程:

CREATE TYPE RecordList AS TABLE (Id Int, Name NVarChar(100), Amount int, location nvarchar(100), ...)

CREATE PROCEDURE test 
    @RecordList dbo.RecordList READONLY
AS Begin
    Insert Into temp (id, name, amount, location , ...)
    Select id, name, amount, location, ...)
    from @RecordList
End

我不使用Spring JDBCTemplate，但可以使用以下参考来在C#中使用表变量参数:

如何从.net代码将表值参数传递给存储过程

C#和表值参数

I'm using Spring JDBCTemplate to connect to the SQL Server.

I have a list of Objects that needed to be inserted into a table of SQL Server.

What I did is the following:

public void batchInsert(final List<Bean> list) {

    final String sql = "insert into temp"
            + "(id, name, amount, location, time, price) "
            + " values (?, ?, ?, ?, ?, ?)";

    getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Bean vo = list.get(i);
            ps.setString(1, vo.getId());
            ps.setString(2, vo.getName());
            ps.setDouble(3, vo.getAmount());
            ps.setString(4, vo.getLocation());
            ps.setString(5, vo.getTime());
            ps.setDouble(6, vo.getPrice());
        }

        @Override
        public int getBatchSize() {
            return list.size();
        }
    });
}

But now, I'd like to pass the parameter List<Bean> list to a stored procedure which handle the batch insert as high efficient as possible.

May I ask how to implement this?

解决方案

You can use Table variable parameter for your stored procedure :

CREATE TYPE RecordList AS TABLE (Id Int, Name NVarChar(100), Amount int, location nvarchar(100), ...)

CREATE PROCEDURE test 
    @RecordList dbo.RecordList READONLY
AS Begin
    Insert Into temp (id, name, amount, location , ...)
    Select id, name, amount, location, ...)
    from @RecordList
End

I don't work with Spring JDBCTemplate but you can use following reference in order to use table variable parameter in C#:

How to pass table value parameters to stored procedure from .net code

C# and Table Value Parameters

这篇关于如何将参数列表传递给存储过程并在SQL Server中执行批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！