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

2016-12-13 09:39:45来源:作者:隔叶黄莺Unmi Blog人点击

第七城市

当我们调用 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 的记录时什么事也不干,存在的话还是会进行更新。

链接:

HSQLDB Merge Statement MERGE (Transact-SQL)
第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台