CODEWITHSUNDEEP
javasqlexception
ferro
ferro

Reputation: 165

Find if an SQLException was thrown because of a duplicate

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.

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.

Has anyone done this before? Any ideas?

Many 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.

Upvotes: 14

Views: 21607

Answers (10)

KrzyKoder
KrzyKoder

Reputation: 1

Maybe it's not the right way to solve this problem, but I have the same issue and I resolved with the following code:

try{

   //here goes your code
   //Conection to DB, stmt, ...


}catch (SQLException sqlEx){
            
    //Check if the SQLException message contains the words "duplicate entry" and "for key"
    if(sqlEx.getMessage().contains("Duplicate entry") 
        && sqlEx.getMessage().contains("for key")){

           System.out.println("This Key already exists in the database");

      }else {

         //if your Exception is not due to the duplicate key, print your SQLException
           System.out.println("Error SQL:" + sqlEx.getMessage());          
      }
}

I hope this solution can be helpful to someone!!

Upvotes: 0

smndiaye
smndiaye

Reputation: 468

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

Upvotes: 0

Matt Solnit
Matt Solnit

Reputation: 33534

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

Upvotes: 11

kenj0418
kenj0418

Reputation: 6838

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

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).

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

Upvotes: 8

wentbackward
wentbackward

Reputation: 546

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.

Upvotes: 0

matt b
matt b

Reputation: 139921

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 DuplicateKeyException or something similar.

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.

Upvotes: 2

Brad Barker
Brad Barker

Reputation: 2083

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

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.

Upvotes: -1

Lou
Lou

Reputation: 161

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

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

Upvotes: 0

Mark Brackett
Mark Brackett

Reputation: 85645

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

Upvotes: 2

tpdi
tpdi

Reputation: 35141

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;"

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.

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

Upvotes: 1

Related Questions