Java + MySQL 完整性违规处理

发布于 2024-08-20 06:00:59 字数 262 浏览 7 评论 0原文

我使用 JDBC（mysql 数据库）编写 Java 程序。当我违反 mysql 完整性时（如果我尝试插入相同的主键值），我会捕获 SQL 异常。 我应该以永远不会发生的方式编写它（首先布尔函数检查主键值是否不在数据库中，然后调用插入），或者可以仅通过异常来处理它吗？例子：

catch (SQLException ex) {ex.printStackTrace(); showSomeErrorDialog(); }

原文

I write Java program using JDBC (mysql database).
When I violate mysql integrity (f.e. I try to insert same primary key value) I catch SQL exception.
Should I write it in way it may never happen (f.e. at first boolean function checking whether primary key value isn't already in DB and then calling insert), or is it okay to handle it just by exception?
Example :

catch (SQLException ex) {ex.printStackTrace(); showSomeErrorDialog(); }

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谈下烟灰 2024-08-27 06:00:59

实际上，基本上有两种方法可以实现此目的：

在插入之前测试记录是否存在 - 在同一事务内。
确定是否<捕获的 SQLException 的 code>SQLException#getSQLState() 以 23 开头，这是根据 SQL 规范。也就是说，它可能是由更多因素引起的，而不仅仅是“仅仅”违反约束。您不应将每个 SQLException 修改为违反约束。
```
public static boolean isConstraintViolation(SQLException e) {
    return e.getSQLState().startsWith("23");
}
```

我会选择第一个，因为它在语义上更正确。事实上，这并非特例。也就是说，您知道这可能会发生。但在事务未同步（无意中或为了优化性能）的高并发环境中，它可能会失败。然后您可能想要确定异常。

也就是说，您通常不应该在主键上遇到约束违规。在使用技术键作为主键的精心设计的数据模型中，它们通常由数据库本身管理。该字段不应该是唯一键吗？

There are indeed basically two ways to achieve this:

Test if record exists before inserting --inside the same transaction.
Determine if SQLException#getSQLState() of the catched SQLException starts with 23 which is a constraint violation as per the SQL specification. It can namely be caused by more factors than "just" a constraint violation. You should not amend every SQLException as a constraint violation.
```
public static boolean isConstraintViolation(SQLException e) {
    return e.getSQLState().startsWith("23");
}
```

I would opt for the first one as it is semantically more correct. It is in fact not an exceptional circumstance. You namely know that it is potentially going to happen. But it may potentially fail in heavy concurrent environment where transactions are not synchronized (either unawarely or to optimize performance). You may then want to determine the exception instead.

That said, you normally shouldn't get a constraint violation on a primary key. In well designed datamodels which uses technical keys as primary keys they are normally to be managed by the database itself. Isn't the field supposed to be an unique key?

回复收藏 0 原文