承接近两年前的 用 PreparedStatement 向 SqlServer 中一次性插入多条记录，其文后用 User-Defined Type 可用下面简单的代码把 Java 本地内存中表格数据一股脑的刷入到 SQL Server 数据库表格中
String sql = "INSERT INTO Customers SELECT * FROM ?";
SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) conn.prepareStatement(sql);
SQLServerDataTable dataTable = ..... // 生成好的本地表格数据
pstmt.setStructured(1, "CustomersTableType", dataTable);
pstmt.execute();
上面的 dataTable 本地表格类型变量容易生成，关键是必须在正式数据库数须预先用 CREATE TYPE 创建好 CustomersTableType 这个用户自定义类型，这会受权限的约束。如果由 DBA 预先完全依照目标表来创建好这个用户自定义类型，又无法确定是否总是要操作该目标表的所有字段。
数据库是允许我们创建临时的用户自定义类型
use tempdb;
create type CustomersTableType as table (...
但是在临时数据库中定义的 Data Type 确无法在操作正式表时引用，所以想要创建用户自定义数据类型还得获得相应的权限才行。
这之前都是在回顾上一篇中的内容，这里仍然使用了基本相同的办法，只是以此来体验一下怎么把内存中排好的表格用 merge into 语句合并到目标数据库表中去。操作代码如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Connection conn = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=master", "sa", "yourStrong(!)Password"); // String sql = "INSERT INTO Customers SELECT * FROM ?"; String sql = "MERGE INTO Customers AS t\n" + " USING ? as s\n" + " ON t.Id = s.Id\n" + " WHEN NOT MATCHED THEN\n" + " INSERT(Id, Name, Age) VALUES(s.Id, s.Name, s.Age)\n" + " WHEN MATCHED THEN\n" + " UPDATE SET t.Name=s.Name, t.Age=s.Age;"; SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) conn.prepareStatement(sql); SQLServerDataTable dataTable = new SQLServerDataTable(); dataTable.addColumnMetadata("Id", Types.INTEGER); dataTable.addColumnMetadata("Name", Types.VARCHAR); dataTable.addColumnMetadata("Age", Types.DECIMAL); dataTable.addRow(1, "Name1", 21.7); // addRow 中的参数不一定要对应的类型，全为字符串都行 dataTable.addRow(2, "Name2", 32.7); // addRow("2", "Name2", "32.7") 字符串会被依据上面的 Metadata 解析为具体类型值 pstmt.setStructured(1, "CustomersTableType", dataTable); pstmt.execute(); |
操作后看看表中的数据
欲完成上面的操作，前面说过还必须基于 Customers 表定义好 CustomersTableType 类型
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Customers( Id INTEGER, Name VARCHAR(20), Age DECIMAL(5, 2) ) CREATE TYPE CustomersTableType AS TABLE ( Id INTEGER, Name VARCHAR(20), Age DECIMAL(5, 2) ) |
弄到这里其实还是没什么意思，夺取创建用户自定义类型的权限才是关键，如果 SQL Server 的 JDBC 驱动允许把自定义类型维持中本地内存才好。至于怎么生成内在表格数据倒不是难事，况且还支持多种类型的表格
|
1 2 3 |
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) |
本文主要是验证了一下能够通过 merge into 把本地内存中的表格数据直接合并到实际数据库表格，而不用借助于临时表格或拼凑一大段的 merge into 语句，唯一不可突破的是为目标表格创建用户自定义类型。
上面提供有三个 setStructured(...) 方法，再来尝试最后一种 ISQLServerDataRecord, SQL Server 驱动中目前没有相应的实现，自己实现一个便是
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
ISQLServerDataRecord dataRecords = new ISQLServerDataRecord() { private int currentRow = -1; Object[][] records = new Object[][] { {1, "Name1", 21.5}, {2, "Name2", 32.5} }; @Override public SQLServerMetaData getColumnMetaData(int column) { switch (column) { case 1: return new SQLServerMetaData("Id", Types.INTEGER); case 2: return new SQLServerMetaData("Name", Types.VARCHAR); default: return new SQLServerMetaData("Age", Types.DECIMAL); } } @Override public int getColumnCount() { return 3; } @Override public Object[] getRowData() { return records[currentRow]; } @Override public boolean next() { currentRow ++; return currentRow != 2; } }; pstmt.setStructured(1, "CustomersTableType", dataRecords); pstmt.execute(); |
再次可见，生成本地数据是件容易的事，关键还是那个 User-Defined Type.
链接：
