转载

SQL Server 和 HSQLDB 中使用 merge into 完成 saveOrUpdate 操作

当我们调用 Hibernate 的 saveOrUpdate() 或 JPA 的 save() 方法的 Hibernate 实现时，都会做两步操作：1）按 ID 查询记录是否已存在，2）不存在插入新记录，存在则更新原记录。这种两步操作其实可以在 SQL Server 和 HSQLDB 中一条语句完成，这就是本文要介绍的 merge into 语句。感觉到用数据库自己的特性，并且一条语句会比 saveOrUpdate() 两步操作性能要好，还需实测。

之所以把 SQL Server 和 HSQLDB 扯到一块来讲，是因为我们在实际项目中的单元测试是基于 HSQLDB 内存数据库的。 merge into 如其名所示，它应该是给予我们便利的去根据把一个表中符合条件的记录合并到另一个表中去。我们这里只利用它的这特性去实现类似 Hibernate 的 saveOrUpdate() 操作。

假设我们有一个简单的表

CREATE TABLE user (
  id INT,
  name VARCHAR(32),
  address VARCHAR(128)
);

如果指 id 的记录已存在更新原来记录的 name 和 address, 不存在则插入新记录

SQL Server 的 merge into 实现 saveOrUpdate

MERGE INTO user u1
  USING (SELECT 1 as id) u2
    ON u1.id = u2.id  --这里可以写组合条件， 如 ON u1.id = u2.id AND u1.id > 0
  WHEN MATCHED THEN
    UPDATE SET u1.name = CONCAT(u1.name, 'N'), u1.address = CONCAT(u1.address, 'A')
  WHEN NOT MATCHED THEN
    INSERT (id, name, address) values(1, 'Yanbin', 'Chicago')
;

未找到 id 为 1 的记录插入新记录，找到的话更新 name 和 address 值

HSQLDB 的 merge into 实现 saveOrUpdate

MERGE INTO user u1
  USING (VALUES 1) u2(id)
    ON u1.id = u2.id
  WHEN MATCHED THEN
    UPDATE SET u1.name = CONCAT(u1.name, 'N'), u1.address = CONCAT(u1.address, 'A')
  WHEN NOT MATCHED THEN
    INSERT (id, name, address) values(1, 'Yanbin', 'Chicago')
;

在 HSQLDB 中不能直接 select 1 无源的查询一个常量值，如果有某个表只有一条记录的话，可以 select 1 from TABLE_WITH_ONE_RECORD , 可者用 VALUES 创建一临时表立即查询，这样做

SELECT 1 FROM (VALUES 'ANY')

所以上面的 merge into 语句中的 USING 行也可以写成

USING (SELECT 1 from (VALUES 'ANY')) u2

JDBC 中参数化 `merge into` 语句

实际应用中一般都需要传递参数到 SQL 中，应用 ? 或 :name 在 SQL 作为占位符。参数通常是作为 WHERE 语句的条件，或 UPDATE 中 SET 的值，不能直接用作 SELECT 的查询字段，如下面的语句

String sql = "select ? from user";

如果采用 jdbcTemplate 来执行上面的语句并套入自己的参数

jdbcTemplate.queryForList(sql, 123);

会得到类似下面的错误

data type cast needed for parameter or null literal

原因是上面的 ? 处无法确定数据类型，它可以是任何类型，所以我们需要用 CAST 函数，正确的带参数的语句应该是

String sql = "select cast(? as int) from user";

对于 HSQLDB 也类似， (VALUES 1) u2(id) 需参数化的话，要写成

(VALUES CAST(? as INT)) u2(id)

现在以 SQL Server 的 merge into 为例来说明如何参数化，分别又为 ? 与 :paraName 的形式

用 ? 号参数化 merge into 语句

MERGE INTO user u1
  USING (SELECT CAST(? as INT) as id) u2
    ON u1.id = u2.id
  WHEN MATCHED THEN
    UPDATE SET u1.name = ?, u1.address = ?
  WHEN NOT MATCHED THEN
    INSERT (id, name, address) values(?, ?, ?)

我们实际只需要传入 3 个参数(id, name, address), 然而上面的语句有 6 个问号，也就是我们在代码中必须老老实实的传入 6 个参数，即使重复也没办法。假设上面的语句赋值给了 String sql 变量，那么

jdbcTemplate.update(?, 1, 'Yanbin', 'Chicago', 1, 'Yanbin', 'Chicago');

如果操作的表字段更多的话就更恐怖，很容易在参数匹配上出问题，所以更好的办法是

用命名参数 :paraName 来参数化 merge into 语句

MERGE INTO user u1
  USING (SELECT CAST(:id as INT) as id) u2
  ON u1.id = u2.id
  WHEN MATCHED THEN
    UPDATE SET u1.name = :name, u1.address = :address
  WHEN NOT MATCHED THEN
    INSERT (id, name, address) values(:id, :name, :address)

此时要用 NamedParameterJdbcTemplate , 具体操作如下：

namedParameterJdbcTemplate.update(sql, ImmutableMap.of("id", 1, "name", "Yanbin, "address", "Chicago");

不需要重复列出参数值。

如果不愿用 CAST 也无妨，我们可以试着把参数转移到 USING 部分的 WHERE 条件中去，如

MERGE INTO user u1
  USING (SELECT id from user where id = :id) u2
    ON u1.id = u2.id
  WHEN MATCHED THEN
    UPDATE SET u1.name = :name, u1.address = :address
  WHEN NOT MATCHED THEN
    INSERT (id, name, address) values(:id, :name, :address)

上面的语句在 SQL Server 中是没问题，但是接下来的语。但同样的语句在 HSQLDB 中却有点莫名其妙的问题了，在当表中不存在 id 为 1 的记录时什么事也不干，存在的话还是会进行更新。

链接：