用 Java 把内存中的表格数据合并到 SQL Server 表中

Sep 26, 2019 --- · 2 min read · merge into ·

Share on:

承接近两年前的用 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 语句合并到目标数据库表中去。操作代码如下：

 1Connection conn = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=master",
 2    "sa", "yourStrong(!)Password");
 3
 4//  String sql = "INSERT INTO Customers SELECT * FROM ?";
 5String sql = "MERGE INTO Customers AS t\n" +
 6    "  USING ? as s\n" +
 7    "    ON t.Id = s.Id\n" +
 8    " WHEN NOT MATCHED THEN\n" +
 9    "   INSERT(Id, Name, Age) VALUES(s.Id, s.Name, s.Age)\n" +
10    " WHEN MATCHED THEN\n" +
11    "   UPDATE SET t.Name=s.Name, t.Age=s.Age;";
12SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) conn.prepareStatement(sql);
13
14SQLServerDataTable dataTable = new SQLServerDataTable();
15dataTable.addColumnMetadata("Id", Types.INTEGER);
16dataTable.addColumnMetadata("Name", Types.VARCHAR);
17dataTable.addColumnMetadata("Age", Types.DECIMAL);
18dataTable.addRow(1, "Name1", 21.7);  // addRow 中的参数不一定要对应的类型，全为字符串都行
19dataTable.addRow(2, "Name2", 32.7);  // addRow("2", "Name2", "32.7") 字符串会被依据上面的 Metadata 解析为具体类型值
20
21pstmt.setStructured(1, "CustomersTableType", dataTable);
22
23pstmt.execute();

操作后看看表中的数据

欲完成上面的操作，前面说过还必须基于 Customers 表定义好 CustomersTableType 类型

 1CREATE TABLE Customers(
 2    Id INTEGER,
 3    Name VARCHAR(20),
 4    Age DECIMAL(5, 2)
 5)
 6
 7CREATE TYPE CustomersTableType AS TABLE (
 8    Id INTEGER,
 9    Name VARCHAR(20),
10    Age DECIMAL(5, 2)
11)

弄到这里其实还是没什么意思，夺取创建用户自定义类型的权限才是关键，如果 SQL Server 的 JDBC 驱动允许把自定义类型维持中本地内存才好。至于怎么生成内在表格数据倒不是难事，况且还支持多种类型的表格

1public void setStructured(int n, String tvpName, SQLServerDataTable tvpDataTable)
2public void setStructured(int n, String tvpName, ResultSet tvpResultSet)
3public vpod setStructured(int n, String tvpName, ISQLServerDataRecord tvpBulkRecord)

本文主要是验证了一下能够通过 merge into 把本地内存中的表格数据直接合并到实际数据库表格，而不用借助于临时表格或拼凑一大段的 merge into 语句，唯一不可突破的是为目标表格创建用户自定义类型。

上面提供有三个 setStructured(...) 方法，再来尝试最后一种 ISQLServerDataRecord, SQL Server 驱动中目前没有相应的实现，自己实现一个便是

 1ISQLServerDataRecord dataRecords = new ISQLServerDataRecord() {
 2    private int currentRow = -1;
 3    Object[][] records = new Object[][] {
 4        {1, "Name1", 21.5},
 5        {2, "Name2", 32.5}
 6    };
 7
 8    @Override
 9    public SQLServerMetaData getColumnMetaData(int column) {
10        switch (column) {
11            case 1:
12                return new SQLServerMetaData("Id", Types.INTEGER);
13            case 2:
14                return new SQLServerMetaData("Name", Types.VARCHAR);
15            default:
16                return new SQLServerMetaData("Age", Types.DECIMAL);
17        }
18    }
19
20    @Override
21    public int getColumnCount() {
22        return 3;
23    }
24
25    @Override
26    public Object[] getRowData() {
27        return records[currentRow];
28    }
29
30    @Override
31    public boolean next() {
32        currentRow ++;
33        return currentRow != 2;
34    }
35};
36
37pstmt.setStructured(1, "CustomersTableType", dataRecords);
38pstmt.execute();

再次可见，生成本地数据是件容易的事，关键还是那个 User-Defined Type.

链接：

使用資料表值參數

永久链接 https://yanbin.blog/merge-in-memory-table-data-into-sql-server-table/, 来自隔叶黄莺 Yanbin's Blog
[版权声明]

本文采用署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。