如何将参数列表传递给存储过程并在SQL Server中执行批量插入

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

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

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

我有一个需要插入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.

但是现在,我想将参数List 列表传递给存储过程,该存储过程尽可能高效地处理批量插入。

May I ask how to implement this?

请问如何实施?

6 个解决方案

#1

First of all don't write query directly as it can be security breach to Your application. For bulk data storage the way normally get followed is XML Way. Try to implement it through XML.

首先不要直接编写查询,因为它可能会对您的应用程序造成安全漏洞。对于批量数据存储,通常遵循的方式是XML Way。尝试通过XML实现它。

#2

You can use Table variable parameter for your stored procedure :

您可以将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

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

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

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

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

C# and Table Value Parameters

C#和表值参数

#3

If you can't use Table-Valued parameters, why not xml parameter in your Stored Procedure?

如果您不能使用表值参数,为什么不在存储过程中使用xml参数?

#4

Pasre XML to dataset:

DECLARE @ParseXML xml

--SET @ParseXML  = ?        Your xml, don't use UTF-8
SET  @ParseXML = '
<row>
  <id>1</id>
  <name>Alex</name>
  <amount>3</amount>
  <location>GBR</location>
  <time>2014-02-27T08:25:19.113</time>
  <price>5.25</price>
</row>
<row>
  <id>2</id>
  <name>John</name>
  <amount>1</amount>
  <location>USA</location>
  <time>2014-02-27T08:25:19.113</time>
  <price>3.00</price>
</row>
<row>
  <id>3</id>
  <name>Sam</name>
  <amount>4</amount>
  <location>CAN</location>
  <time>2014-02-27T08:25:19.113</time>
  <price>7.55</price>
</row>
';

--INSERT INTO Temp
SELECT
    T.c.value('./id[1]','int') as id
    ,T.c.value('./name[1]','nvarchar(255)') as name
    ,T.c.value('./amount[1]','int') as amount
    ,T.c.value('./location[1]','nvarchar(255)') as location
    ,T.c.value('./time[1]','datetime') as [time]
    ,T.c.value('./price[1]','money') as [time]
FROM @ParseXML.nodes('//row') T(c);

#5

XML Stored procedures are available since SQL 2005

XML存储过程自SQL 2005起可用

basically, you declare an input parameter of xml type in your procedure declaration.

基本上,您在过程声明中声明了xml类型的输入参数。

then, in your procedure, you could use different methods to parse xml. Maybe the easiest is to use the OPENXML method and sp_xml_preparedocument/sp_xml_removedocument

然后,在您的过程中,您可以使用不同的方法来解析xml。也许最简单的方法是使用OPENXML方法和sp_xml_preparedocument / sp_xml_removedocument

Suppose your xml variable is something along this:

假设您的xml变量是这样的:

<Order>
    <OrderID>12345</OrderID>
    <CustomerID>ABCD-1234</CustomerID>
    <OrderDate>20140101</OrderDate>
</Order>

Procedure should be something like :

程序应该是这样的:

CREATE PROCEDURE MyProc @xmlData xml
AS

EXEC sp_xml_preparedocument @i OUTPUT, @xmlData

select *
from
  openxml (@i, '/ROOT/Order', 1)
  with
  ( OrderID        int
  , CustomerID     nchar(5)
  , OrderDate      datetime)
  INTO MyOrders

EXEC sp_xml_removedocument @i

see: http://technet.microsoft.com/it-it/library/ms186918.aspx on how to use OPENXML

请参阅:http://technet.microsoft.com/it-it/library/ms186918.aspx如何使用OPENXML

#6

While a table-valued parameter or constructing XML is the modern approach, if that's not available to you or you don't want that overhead, you could simply concatenate the many INSERT statements and send them in as a single batch to SQL Server. If you need to do some processing as part of the insertion, you the batch could use a temporary table which would be accessible to a stored procedure called at the end of the batch.

虽然表值参数或构造XML是现代方法,但如果您无法使用它或者您不需要这些开销,则可以简单地连接许多INSERT语句并将它们作为单个批处理发送到SQL Server。如果您需要在插入过程中进行一些处理,则批处理可以使用临时表,该表可以在批处理结束时调用的存储过程访问。

#1