我可以使用MERGE INTO来模拟"upsert"吗?在Apache Derby中? [英] Can I use MERGE INTO to simulate "upsert" in Apache Derby?

问题描述

我们正在使用Derby，并且有很多这样的代码:

We're using Derby and have a lot of code which goes like this:

try (ResultSet rs = executeQuery(...)) {
    if (rs.next()) {
        updateRowSet(rs, ...);
        rs.updateRow();
    } else {
        executeUpdate(...);
    }
}

过去，我们一直在寻找一种方法来执行此逻辑服务器端，并发现某些数据库支持"upsert"(更新或插入)操作. Derby对MERGE INTO提出了功能请求，这应该是SQL:2003的标准方式，所以我们坐下来看票，并花费了很多时间.

In the past, we were searching for a way to do this logic server-side, and found that some databases supported an "upsert" (update or insert) operation. Derby had a feature request for MERGE INTO which was supposedly the SQL:2003 standard way of doing this, so we sat and watched the ticket, and much time passed.

Derby 10.11最终添加了 MERGE INTO .没有人有时间检查和更新代码以使用它，但是在阅读他们的文档时，他们的所有示例都显示了从一个表合并到另一个表.但是等等，我们的数据还没有在表格中！

Derby 10.11 finally added MERGE INTO. Nobody has had time to go through and update the code to use it yet, but on reading their documentation, all their examples show merging from one table to another. But hang on, our data isn't in a table yet!

我知道我可以将放在表中，但随后又是多个查询，这完全打乱了使用它的目的.

I know I could put it in a table, but then it is multiple queries again, which completely defeats the point of using it.

我敢肯定可以将其放入表中而无需 ，但是由于文档中没有显示单个示例，因此我不确定如何进行操作.

I'm sure it is possible to do it without putting it into a table, but because the documentation does not show a single example, I'm not sure how to proceed.

这是我一直在尝试的:

try (PreparedStatement ps = connection.prepareStatement(
        "MERGE INTO things AS target " +
        // Awkward point 1:
        // It wants a "source" table, but I don't have one.
        // So I thought I would try to use the same table with
        // another name.
        "  USING things AS source ON target.id = ?" +
        "  WHEN MATCHED THEN" +
        "    UPDATE SET data = ?" +
        "  WHEN NOT MATCHED THEN" +
        "    INSERT (id, data) VALUES (??, ?)"))
{
    ps.setLong(1, id);
    ps.setBinaryStream(2, data);
    ps.setLong(3, id);
    // Awkward point 2:
    // Passing an InputStream into a query as two
    // parameters.
    ps.setBinaryStream(4, data);
    ps.execute();
}

这似乎没有执行任何插入操作，但也没有给出错误，因此我绝对没有任何事情要做.

This doesn't appear to do any of the inserts, but also doesn't give an error, so I have absolutely nothing to go on.

推荐答案

为仍然使用derby的所有可悲的人共享它:) 因此，我在merge into statement( https://db .apache.org/derby/docs/10.14/ref/rrefsqljmerge.html ):

Sharing it for all sad people that still use derby :) So I solved it with help of merge into statement( https://db.apache.org/derby/docs/10.14/ref/rrefsqljmerge.html) in that way:

MERGE INTO foo
USING SYSIBM.SYSDUMMY1
ON foo.id = '1' AND foo.language = 'en'
WHEN MATCHED THEN
  UPDATE SET name = 'name2', image = 'someImgUrl2'
WHEN NOT MATCHED THEN
  INSERT (id, name, language, image)
  VALUES ('1', 'name1', 'en', 'someImgUrl1')

其中foo是您要在其中更新行的表，而SYSIBM.SYSDUMMY1德比虚拟表仅具有1个无用的行(顺便说一句，它不适用于我的多行常规表之一)

Where foo is the table where u want to upsert row and SYSIBM.SYSDUMMY1 derby dummy table that has only 1 useless row (btw it doesn't work with one of my regular table that has multiple rows)

您可能会理解，这更像是解决方法，但总比没有达到目标要好.

As u might understand it's more like workaround but better than nothing to achive upsert aim.

这篇关于我可以使用MERGE INTO来模拟"upsert"吗?在Apache Derby中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！