java JDBC插入或更新实践
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6252997/
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
JDBC insert or update practice
提问by u290629
I need to insert a record to table if the record doesn't exist, and to update a record if the record exists in the table. Of course, I can write: p-code:
如果记录不存在，我需要将记录插入到表中，如果记录存在于表中，则需要更新记录。当然，我可以写：p-code：
SELECT * FROM table1 WHERE id='abc' by JDBC
if(exists)
UPDATE table1 SET ... WHERE id='abc' by JDBC;
else
INSERT INTO table1... by JDBC;
However, I don't think the code is elegant. Alternatively, I can also write it in this way: p-code:
但是，我认为代码并不优雅。或者，我也可以这样写：p-code：
int row = Statement.executeUpdate("INSERT INTO table1...", 2);
if(row==0)
update table1 SET ... WHERE id='abc' by JDBC;
Do you think the latter way is better and faster? Thanks!
你认为后一种方式更好更快吗？谢谢！
EDIT: in MYSQL
编辑：在 MYSQL
回答by sbrattla
It depends on what type of database your are using and whether or not you can take advantage of database specific features. MySQL for instance lets you do the following:
这取决于您使用的数据库类型以及您是否可以利用特定于数据库的功能。例如，MySQL 允许您执行以下操作：
INSERT INTO territories (code, territory) VALUES ('NO', 'Norway')
ON DUPLICATE KEY UPDATE territory = 'Norway'
However, the above is not standard (SQL-92) compliant. That is, it will most likely not work on all databases. In other words, you would have to stick with the code as you have written it. It might not look that elegant, but it is probably the most safe solution to go with.
但是，上述内容不符合标准 (SQL-92)。也就是说，它很可能不适用于所有数据库。换句话说，您必须坚持编写代码。它可能看起来不那么优雅，但它可能是最安全的解决方案。
回答by planetjones
You might want to look at using the DBMS to do the check within a single statement i.e. use the SQL EXISTS condition: WHERE EXISTSor WHERE NOT EXISTS
您可能希望使用 DBMS 在单个语句中进行检查，即使用 SQL EXISTS 条件：WHERE EXISTS或WHERE NOT EXISTS
回答by Kai
回答by AlexR
This is probably the reason to switch to one of popular ORM solutions (Hibernate, Toplink, iBatis). These tools "know" various SQL dialects and optimise your queries accrodingly.
这可能是切换到一种流行的 ORM 解决方案（Hibernate、Toplink、iBatis）的原因。这些工具“了解”各种 SQL 方言并相应地优化您的查询。
