JDBC插入或更新实践
如果记录不存在，我需要将记录插入到表中；如果表中存在记录，则需要更新记录。 当然，我可以写： 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;
但是，我认为代码并不优雅。 或者，我也可以这样写： p-code：
int row = Statement.executeUpdate("INSERT INTO table1...", 2);
if(row==0)
update table1 SET ... WHERE id='abc' by JDBC;
您认为后一种方式更好更快吗？谢谢！
编辑：在 MYSQL 中
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:
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:
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这取决于您使用的数据库类型以及您是否可以利用数据库特定功能。例如，MySQL 允许您执行以下操作：
但是，上述内容不符合标准 (SQL-92)。也就是说，它很可能不适用于所有数据库。换句话说，您必须坚持使用您编写的代码。它可能看起来不那么优雅，但它可能是最安全的解决方案。
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:
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.
您可能希望考虑使用 DBMS 在单个语句中进行检查，即使用 SQL EXISTS 条件：
WHERE EXISTS或WHERE NOT EXISTSYou might want to look at using the DBMS to do the check within a single statement i.e. use the SQL EXISTS condition:
WHERE EXISTSorWHERE NOT EXISTS也许您使用的数据库具有插入或更新功能，可以自动为您解决这个问题。例如，在 DB2 中，您可以使用 MERGE INTO。请参阅此处
Maybe the database you are using has an insert or update feature which solves this automatically for you. In DB2 you can use MERGE INTO for example. See here
这可能是切换到流行的 ORM 解决方案之一（Hibernate、Toplink、iBatis）的原因。这些工具“了解”各种 SQL 方言并相应地优化您的查询。
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.