用 PreparedStatement 向 SqlServer 中一次性插入多条记录

2017-12-07 12:17:17来源:https://unmi.cc/sqlserver-insert-multiple-rows-onetime/作者:隔叶黄莺Unmi Blog人点击

分享

标准 SQL都提供了下面这种方式一条 INSERT INTO语句插入多条记录



INSERT
INTO
Customers(Id, Name, Age)VALUES
(1
,'Name1'
,21
.5
), (2
,'Name2'
,32
.3
)


VALUES之后用括号列出每一条记录。但是在 Java中想把上面的语句转换成 PreparedStatement 来插入多条记录时就有些问题。要么写成


INSERT INTO Customers(Id, Name, Age) VALUES(?, ?, ?), (?, ?, ?), (?, ?, ?) ....


我们不知道 VALUES后应该列多少个问号,而且 JDBC对参数的个数是有限制的,最多 2000个参数。如果根据字段个数来算一次添加多少条记录,那么这条SQL语句也是动态的,不能很好的作为 PreparedStatement进行预编译。以一个表三个字段为例,2000个参数下一次性最多能插入记录数 666条,也可能由于输入是 666条记录的任意数量,所以生成的语句非静态的。


这种方式与每次手动拼凑一个完全静态的 INSERT INTO语句应该不会有太多的差别。


如果只是写成


INSERT INTO Customers(Id, Name, Age) VALUES(?, ?, ?)


然后试图进行下面的操作


PreparedStatement pstmt = connection.prepareStatement(sql);
for(customer: Customers) {
   pstmt.setInt(customer.id);
   pstmt.setString(customer.name);
   pstmt.setDouble(customer.age);
   pstmt.addBatch();
}
pstmt.executeBatch();

从起来好像是做了批量操作,实际上在数据端只是预编译了一次 sql,还是针对每一条记录执行了一次 INSERT操作。


那么是否可以在 SQL语句中只放一个 ?,然后在 JDBC操作时为那个 ?问号传入一个相当于表变量,的确如此。比如说用 PreparedStatement来一次性插入多条记录的 SQL语句这样写


INSERT INTO Customers SELECT * FROM ?

这个好理解了,从一个传入的表变量中查出所有记录插入到表 Customers中去。下面来看如何向 FROM后的那个问号传参数。


首先,后会将会用到 SQL ServerJDBCDrvier特有的 API,而且需要至少 Microsoft JDBC Driver 6.0及以上的驱动。


String sql = "INSERT INTO Customers SELECT * FROM ?";
SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) connection.prepareStatement(sql);  

接着应该如何为 FROM后的问号赋值了,setInt(1, xxx)?, setObject(1, xxx)?,都不对。参 pStmt的参数赋值有两要素:类型和数据值


先说对上面语句中问号的赋值不再是标准 JDBC的 PreparedStatement中的方法,而是 SQLServerPreparedStatement独有的 setStructured(...)方法,它有三个版本,分别是


public void setStructured(int n, String tvpName, SQLServerDataTable tvpDataTable)
public void setStructured(int n, String tvpName, ResultSet tvpResultSet)
public vpod setStructured(int n, String tvpName, ISQLServerDataRecord tvpBulkRecord)
第一个参数好说,这里就是 1。
第二参数 tvpName,全称是 Table Valued Parameter Name,需要事先创建一个用户自定义表类型User-Defined Table Types
.
第三个参数就看我们如何构造表结构的对象类型。可以是一个 SQLServierDataTable,或 ResultSet(比如从别的库查询得到的),或者是 ISQLServerDataRecord(与 BCP操作数据类型 ISQLServerBulkRecord 有点像)

假定我们操作的 Customers表的创建语句如下:


CREATE TABLE Customers(
    Id INTEGER,
    Name VARCHAR(20),
    Age DECIMAL(5, 2)
)

在此,我们必须为 setStructured(...)的第二个参数创建一个表类型,用下面的语句


CREATE TYPE CustomersTableType AS TABLE (
    Id INTEGER,
    Name VARCHAR(20),
    Age DECIMAL(5, 2)
)


是不是觉得很罗嗦啊,完全可以用和 Customers一样的字段类型,SELECT *FROM ?
中的问号就是要用到CustomersTableType
类型。"INSERT INTO Customers SELECT * FROM ?"就是要从这个 CustomersTableType 表类型中查询,setStructure(1, "CustomersTableType",????
)也就要为它构造数据结构,这里用字符串 "CustomersTableType"指明了后面????
位置中变量的类型。


此篇打算用 SQLServerDataTable来构造待传入的参数对象


SQLServerDataTable dataTable = new SQLServerDataTable();
dataTable.addColumnMetadata("Id", Types.INTEGER);
dataTable.addColumnMetadata("Name", Types.VARCHAR);
dataTable.addColumnMetadata("Age", Types.DECIMAL);
dataTable.addRow(1, "Name1", 21.5);
dataTable.addRow(2, "Name2", 32.3);

构造 sqlServer时,我们又要为每个字段指定名称和类型,看下面如何绑定到 pStmt上去


pStmt.setStructured(1, "CustomersTableType", dataTable)
 
pStmt.execute();

其实在用 setStructured(...)方法绑定参数时,dataTable中只要有列名就能与 "CustomersTableType"匹配上每个字段的类型。最好是无需创建 CustomersTableType,直接使用 SQLServerDataTable 中指定的字段类型。注意:对 SQLServerDataTable.addColumnMetadata(name, type)时不用指定列宽和数据精度。


这样执行后,就会在数据库表 Customers中插入两条记录。


这样一条 SQL语句插入多条记录效率上肯定比拼 SQL语句,或多插入语句要高。构建 SQLServerDataTable对象还好说,美中不足的是需要预先在数据库中创建一个与实际表类似的 Table类型(这里的 CustomersTableType),开发人员还不一定有这个权限。如果不用创建这个表类型就完美了,而且如果操作 setStructured(...)设置的是一个 ISQLServerDataRecord类型的数据同样是要用到 CustomersTableType那样的表类型。


需要探索一种不用创建 User-Defined Table Types的方式进行批量插入,是时候考虑下 BCP协议了。


参考:


Using Table-Valued Parameters
Using Bulk Copy with the JDBC Driver


注:本文测试是用的微软出品的 SqlServer Docker容器,参考该容器仓库页面microsoft/mssql-server-linux
.


我是用下面的命令启动的一个 SQL Express版本


docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -e 'MSSQL_PID=Express' -p 1433:1433 -d microsoft/mssql-server-linux:latest


密码就是 'yourStrong(!)Password'。启动后有任何的异常可执行


dockerlogs <容器 id>


来查看容器启动时的日志,看是否default虚拟机内存设置不足(至少 2GB,在 2017-CU2版本前要求至少 3.25G),或者是密码复杂度不够,等等。


相关文章

    无相关信息

微信扫一扫

第七城市微信公众平台