How to pass a list of parameter to stored procedur

问题:

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?

回答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.

回答2:

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

回答3:

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

回答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

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

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

Suppose your xml variable is something along this:

<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

回答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.