java 查找是否因重复而引发 SQLException

Find if an SQLException was thrown because of a duplicate

提问by ferro

I have a Java program that is agnostic from the database and I need to know, while inserting, if an SQLException was thrown because of a duplicate key.

我有一个与数据库无关的 Java 程序，我需要知道在插入时是否由于重复键引发了 SQLException。

If I was using a single database driver I would simply use the ErrorCode, but since I can be using very different engines the ErrorCode are not the same.

如果我使用单个数据库驱动程序，我将简单地使用 ErrorCode，但由于我可以使用非常不同的引擎，因此 ErrorCode 并不相同。

Has anyone done this before? Any ideas?

以前有人这样做过吗？有任何想法吗？

Many TIA!

许多 TIA！

Edit:I have a configuration file where I store the driver class (ie: org.apache.derby.jdbc.ClientDriver) and some other needed information (ie: username, password, url...). The connection is always passed as a "java.SQL.Connection" so I don't really care what drivers is being used.

编辑：我有一个配置文件，用于存储驱动程序类（即：org.apache.derby.jdbc.ClientDriver）和一些其他需要的信息（即：用户名、密码、url...）。连接总是作为“java.SQL.Connection”传递，所以我并不关心正在使用什么驱动程序。

采纳答案by kenj0418

With basic JDBC, there really isn't a way to do what you are saying in a cross-database manner. As you mentioned getErrorCodecould be used, but required vendor-specific error codes.

使用基本的 JDBC，确实没有办法以跨数据库的方式执行您所说的操作。正如您提到的，可以使用getErrorCode，但需要特定于供应商的错误代码。

The only three ways I see to get around this is:

我认为解决这个问题的唯一三种方法是：

1. Use some sort of framework that does all of the translating from error code to meaningful exceptions (Hibernate would probably do this, someone else mentioned that Spring does)
2. Check for the duplicate manually (with a select) prior to doing your insert. (This wouldn't be 100%, as its technically possible that someone could have done an insert after your query).
3. After you get any sql exception on the insert, try to query for that id. If you can actually find the match - you can be fairly sure that the error you received was due to a duplicate primary key. (Although its possible that there was multiple problems, and that wasn't actually the one that was thrown).

1. 使用某种框架来完成从错误代码到有意义的异常的所有转换（Hibernate 可能会这样做，其他人提到 Spring 会这样做）
2. 在插入之前手动检查重复项（使用选择）。（这不会是 100%，因为从技术上讲，有人可以在您的查询之后进行插入操作）。
3. 在插入时遇到任何 sql 异常后，尝试查询该 ID。如果您确实可以找到匹配项 - 您可以相当确定您收到的错误是由于主键重复造成的。（虽然可能存在多个问题，但实际上并不是抛出的问题）。

My recommendation would be to write your code to avoid the problem as much as possible, and then (if absolutely necessary), use #3.

我的建议是编写代码以尽可能避免该问题，然后（如果绝对必要）使用 #3。

回答by Matt Solnit

This is exactly what SQLException.getSQLState() is for. Acoording to Google, "23000" indicates a unique constraint violation in at least MySQL, PostgreSQL, and Oracle.

这正是 SQLException.getSQLState() 的用途。根据谷歌的说法，“23000”表示至少在MySQL、PostgreSQL和Oracle 中存在唯一的约束违规。

回答by matt b

I think the ideal solution would be to have the data layer throw a specific exception in this case, perhaps a subclass of SQLException for DuplicateKeyExceptionor something similar.

我认为理想的解决方案是让数据层在这种情况下抛出一个特定的异常，可能是 SQLException 的子类DuplicateKeyException或类似的东西。

If you want to be able to treat different exceptions differently, then you have to throw different exception types (or sub-types) to begin with.

如果您希望能够以不同的方式处理不同的异常，那么您必须首先抛出不同的异常类型（或子类型）。

I think this is an area where the Spring Framework gets things really right: they provide a very rich hierarchy of "database exceptions"all of which extend DataAccessException, with sub-trees of types for "recoverable exceptions", "transient exceptions", "data integrity exceptions", etc etc. This leaves your client code free to catch any (or none) of the exception types which it can handle or care about: exceptions that indicate an error that may not be repeatable if you re-run the transaction, a fatal non-recoverable error, or you can simply catch the root type.

我认为这是 Spring Framework 真正正确处理的一个领域：它们提供了非常丰富的“数据库异常”层次结构，所有这些层次结构都扩展了DataAccessException，具有“可恢复异常”、“瞬态异常”、“数据完整性异常”等。这使您的客户端代码可以自由地捕获它可以处理或关心的任何（或不捕获）异常类型：如果您重新运行事务，则指示可能无法重复的错误的异常，致命的不可恢复错误，或者您可以简单地捕获根类型。

回答by Mark Brackett

You could "train" the program on startup (or config) by inserting a known duplicate key, and recording the thrown error code.

您可以通过插入已知的重复键并记录抛出的错误代码在启动（或配置）时“训练”程序。

回答by tpdi

Well, if you can't rely on the exception to tell you why it was thrown, you could test by following the exception with a "select count(*) from table where key = @keyfailedtoinsert;"

好吧，如果你不能依靠异常来告诉你它为什么被抛出，你可以通过在异常后面加上“select count(*) from table where key = @keyfailedtoinsert;”来测试。

Unfortunately, the exception isn't guaranteed to give you the table name and key name. In some cases, the java code that called called the JDBC driver may never have had them, e.g., if the insert happened wihin a stored procedure, or as in a trigger.

不幸的是，该异常不能保证为您提供表名和键名。在某些情况下，调用 JDBC 驱动程序的 Java 代码可能永远不会拥有它们，例如，如果插入发生在存储过程中，或者在触发器中。

So you're back to having to trust each JDBC driver's vendor.

因此，您又不得不信任每个 JDBC 驱动程序的供应商。

回答by smndiaye

If you are using spring, set your unique KEY name as UK_user_idfor example and catch the DataIntegrityViolationException.
You can then compare ex.getCause().getConstraintName()with UK_user_id.

如果您使用的是 spring，请将您唯一的 KEY 名称设置UK_user_id为例如并捕获DataIntegrityViolationException.
然后您可以ex.getCause().getConstraintName()与UK_user_id.

回答by Lou

Am I missing something? If you're using JDBC you should get back a duplicate key exception, regardless of the DB being used.

我错过了什么吗？如果您使用的是 JDBC，无论使用的是什么数据库，您都应该返回一个重复的键异常。

Or did you ask how you would determine a dupkey BEFORE you tried teh insert?

或者你有没有问过你在尝试插入之前如何确定一个dupkey？

回答by wentbackward

I believe a simple and reliable way is to check if the key exists prior to doing the insert. As you have rightly pointed out, each database has it's own way of reporting the error.

我相信一个简单而可靠的方法是在插入之前检查密钥是否存在。正如您正确指出的那样，每个数据库都有自己的错误报告方式。

回答by Brad Barker

I'm assuming you aren't using JDBC or this would be a very simple error lookup.

我假设您没有使用 JDBC，否则这将是一个非常简单的错误查找。

Do you have a different set of classes for accessing the different databases? If so, you could catch the exception in the database specific classes and throw your own exception type that is shared among all the database types.

您是否有一组不同的类来访问不同的数据库？如果是这样，您可以在特定于数据库的类中捕获异常并抛出您自己的在所有数据库类型之间共享的异常类型。