Java + MySQL完整性违规处理 [英] Java + MySQL integrity violation handling
问题描述
我使用JDBC(mysql数据库)编写Java程序. 当我违反mysql完整性时(例如，我尝试插入相同的主键值)，我捕获到 SQL异常. 我应该用它永远不会发生的方式编写它吗(例如，首先是布尔函数检查主键值是否不在数据库中，然后再调用insert)，还是可以通过异常处理它呢? 例子:
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(); }
推荐答案
实际上，基本上有两种方法可以实现此目的:
There are indeed basically two ways to achieve this:
-
在同一事务内部插入之前测试记录是否存在.
Test if record exists before inserting --inside the same transaction.
确定是否 SQL规范.也就是说，它可能是由比仅仅"违反约束更多的因素引起的.您不应将每个SQLException都修改为违反约束条件.
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?
这篇关于Java + MySQL完整性违规处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
