java Apache Derby：如何“如果不存在则插入”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/407688/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apache Derby: how can I do "insert if not exists"?
提问by Limbic System
I'm giving Apache Derby, aka JavaDBa spin. I can't seem to get around duplicate key issues when inserting records that may already exist. Is there a Derby equivalent to "insert if not exists", or "merge" ?
我正在尝试Apache Derby，也就是JavaDB。在插入可能已经存在的记录时，我似乎无法解决重复的关键问题。是否有相当于“ insert if not exists”或“ merge”的德比？
Similarly, is there a way to do something like "drop table foo if exists"?
同样，有没有办法做类似“ drop table foo if exists”的事情？
回答by Snukker
I've never used apache derby, but a general solution that is fairly database independent is as follows:
我从来没有使用过 apache derby，但是一个完全独立于数据库的通用解决方案如下：
To insert the values 'a' and 'b' into table foo (with columns named A, B), but only where the values are not already there, try something like
要将值 'a' 和 'b' 插入表 foo（列名为 A、B），但仅在值不存在的情况下，尝试类似
INSERT INTO foo (
SELECT 'a' as A, 'b' as B
FROM foo
WHERE
A = 'a' AND B = 'b'
HAVING count(*)=0
)
This may need tweaking for a particular dbms, but the idea is to insert the result of a select that only returns values when there are non.
这可能需要针对特定的 dbms 进行调整，但其想法是插入仅在有非值时才返回值的选择结果。
This is a useful trick for creating an idempotent sql script (one that does nothing the second time it is run). However, be careful when using this in production code as the HAVING count(*)=0may be very slow on large tables.
这是创建幂等 sql 脚本（第二次运行时什么都不做）的有用技巧。但是，在生产代码中使用它时要小心，因为HAVING count(*)=0在大表上可能会非常慢。
回答by Bryan Pendleton
The request to support the SQL:2003 MERGE statement is logged in the Derby bug tracker as https://issues.apache.org/jira/browse/DERBY-3155
支持 SQL:2003 MERGE 语句的请求在 Derby 错误跟踪器中记录为https://issues.apache.org/jira/browse/DERBY-3155
You could vote for that issue, or, even better, contribute an implementation!
你可以为这个问题投票，或者更好的是，贡献一个实现！
Otherwise, the only solutions I know of require either selecting the row first to see if it exists, or inserting it and catching the exception, as others have noted.
否则，我所知道的唯一解决方案需要首先选择该行以查看它是否存在，或者插入它并捕获异常，正如其他人所指出的那样。
You can package this logic up in a database procedure to make it somewhat easier to execute.
您可以将此逻辑打包在一个数据库过程中，以使其更易于执行。
回答by Evan
A standard way I've used with PostgreSQL DBs is something like the following:
我与 PostgreSQL DB 一起使用的标准方法如下所示：
INSERT INTO foo ( col1, col2, col3, ... )
SELECT 'col1 value', 'col2 value', 'colc value', ...
WHERE NOT EXISTS (
SELECT 0
FROM foo
WHERE col1 = 'col1 value'
...
)
Not sure how portable or strictly ANSI compliant it is though. The missing FROM clause in the outer SELECT statement particularly may be non-standard. Give it a go though.
不确定它的便携性或严格的 ANSI 标准。外部 SELECT 语句中缺少的 FROM 子句尤其可能是非标准的。试一试吧。
回答by Alex
Derby implemented MERGE in 10.11: https://db.apache.org/derby/docs/10.11/ref/rrefsqljmerge.html
Note that you need to upgrade your DB to 10.11 before using it: https://db.apache.org/derby/docs/10.11/devguide/cdevupgrades.html
Derby 在 10.11 中实现了 MERGE：https: //db.apache.org/derby/docs/10.11/ref/rrefsqljmerge.html
请注意，您需要在使用之前将 DB 升级到 10.11：https: //db.apache.org /derby/docs/10.11/devguide/cdevupgrades.html
回答by Kjartan
I had the same problem, and I got this working for an insert with just one value / column on Derby. (I never got around to testing it with more, but I have no reason to assume it should not though):
我遇到了同样的问题，我在 Derby 上为只有一个值/列的插入工作了。（我从来没有想过用更多来测试它，但我没有理由认为它不应该）：
INSERT INTO my_table (my_column)
(SELECT 'new_value_to_insert'
FROM my_table
WHERE my_column = 'new_value_to_insert'
HAVING count(*)=0)
回答by daveb
There is no native support for this, to get round it I'm using eclipse-link, eclipse-link will try to create the tables and ignore any errors that arise from attempting to create tables that already exists.
对此没有本机支持，为了解决这个问题，我正在使用 eclipse-link，eclipse-link 将尝试创建表并忽略因尝试创建已存在的表而产生的任何错误。
If you make schema changes, you can tell eclipse link to drop the tables before creating them.
如果您进行架构更改，您可以告诉 eclipse link 在创建表之前删除它们。
回答by Argiropoulos Stavros
I'am using this solution but you must use it only if you understanding the difference between the duplicate from the database viewand the duplicate from the user view
我正在使用此解决方案，但只有在您了解数据库视图中的副本和用户视图中的副本之间的区别时才必须使用它
- Duplicates from the database view are two records with the same primary key
Duplicates from the user view are two records with all the fiels identical
while (ResultSet.next()) { try { PreparedStatement insertion = myConn.prepareStatement("insert into table values (?)"); insertion .setString(1, "test"); insertion .executeUpdate(); } catch (SQLException e) { if(e.getSQLState().equals("23505"))//Found duplicate from database view {continue;}//ignore duplicate and continue with the insert statement else{try { throw e; } catch (Exception ex) { } } } }
- 数据库视图中的重复项是具有相同主键的两条记录
用户视图中的重复项是所有字段相同的两条记录
while (ResultSet.next()) { try { PreparedStatement insertion = myConn.prepareStatement("insert into table values (?)"); insertion .setString(1, "test"); insertion .executeUpdate(); } catch (SQLException e) { if(e.getSQLState().equals("23505"))//Found duplicate from database view {continue;}//ignore duplicate and continue with the insert statement else{try { throw e; } catch (Exception ex) { } } } }
