Catch duplicate entry Exception

Question

How can i catch this Exception :

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: 
                                      Duplicate entry '22-85' for key 'ID_CONTACT'

Answer 1

I use spring so we resolve it by org.springframework.dao.DataIntegrityViolationException

try {
    ao_history_repository.save(new AoHistory(..));
} catch (DataIntegrityViolationException e) {
    System.out.println("history already exist");
}

But as @KevinGuancheDarias mention it:

Please note that while this works. I suggest to solve the problem by issuing a findBy before the save, as this is messy, and I think it's not warranted that it will work in future versions, may even break without notification.

Answer 2

catch SQLIntegrityConstraintViolationException, if you are using Java 1.6+

e.g.

try {
    ps.executeUpdate("INSERT INTO ...");
} catch (SQLIntegrityConstraintViolationException e) {
    // Duplicate entry
} catch (SQLException e) {
    // Other SQL Exception
}

or

try {
    ps.executeUpdate("INSERT INTO ...");
} catch (SQLException e) {
    if (e instanceof SQLIntegrityConstraintViolationException) {
        // Duplicate entry
    } else {
        // Other SQL Exception
    }
}

Answer 3

I use Spring. So catch org.springframework.dao.DuplicateKeyException

try{
    ...
} catch (DuplicateKeyException dke) {
    ...
} 

Answer 4

vendorCode 2601 is for unique index constraint violate so you can check SQLException cewndorCode by e.getErrorCode() == 2601. sample code:

try {
    ao_history_repository.save(new AoHistory(..));
} catch (SQLException e) {
    if (e.getErrorCode() == 2601) {
        System.out.println("handle duplicate index error here!");
    } else {
        System.out.println("handle other error code here!");
    }
}

Answer 5

A - Log the exception in detail

Here is what I use to log SQL Exceptions so that I can be sure of what to catch;

private static void handleSQLError(SQLException e) throws SQLException {
    log.info("SQL Error Type : " + e.getClass().getName());
    log.info("Error Message  : " + e.getMessage());
    log.info("Error Code     : " + e.getErrorCode());
    log.info("SQL State      : " + e.getSQLState());

    throw e;
}

Here is the sample Output;

2018 Nis 05 11:20:32,248 INFO MySQLUtil: SQL Error Type : com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException
2018 Nis 05 11:20:32,248 INFO MySQLUtil: Error Message  : Duplicate entry 'test2 CAMT052' for key 'customer_file_customer_file_name_idxu'
2018 Nis 05 11:20:32,249 INFO MySQLUtil: Error Code     : 1062
2018 Nis 05 11:20:32,249 INFO MySQLUtil: SQL State      : 23000
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'test' for key 'customer_file_customer_file_name_idxu'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)

B - Catch the Exception and check the parameters

To catch duplicate key Exception, we need to catch the specific class which is MySQLIntegrityConstraintViolationException. Also the following parameter must match;

SQL State      : 23000

So I use the following block to catch duplicate entries;

try {
    dbUtil.persistEntry(entry);
} catch (SQLException e) {
    if(e instanceof MySQLIntegrityConstraintViolationException) {
        if(e.getSQLState().equals("23000")) {
            if(e.getMessage().contains("Duplicate")) {
                isDuplicateEntryError = true;
            }
        }
    }
}

Answer 6

Loook at Spring framework source code Look at spring JDBC error resolve code.

org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator#doTranslate

else if (Arrays.binarySearch(this.sqlErrorCodes.getDuplicateKeyCodes(), errorCode) >= 0)

{ logTranslation(task, sql, sqlEx, false); return new DuplicateKeyException(buildMessage(task, sql, sqlEx), sqlEx); }

There are multiple ways how you can hit different Exception translators:

• Spring load metadata/error codes from your db - one translator
• Spring fails to connect to db - another one
• Hibernate JPA may have different translator

So Dublicate behavior may change from DuplicateKeyException to DataIntegrityViolationException.

Answer 7

In my Spring project, the exception thrown was org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement.

So after debugging I had a parent JpaSystemException with a cause PersistenceException and that had a cause ConstraintViolationException, this last one had the Database specific exception, but I ended up catching ConstraintViolationException with

public static boolean isSqlDuplicatedKey(Exception e) {
        return e.getCause() != null && e.getCause().getCause() != null
                && ConstraintViolationException.class.isInstance(e.getCause().getCause());
}
// ....

try {
   someRepository.save(some);
} catch (JpaSystemException e) {
    if (ExceptionUtilService.isSqlDuplicatedKey(e)) {
        // Do something to handle it
    } else {
        throw e;
    }
}

Please note that while this works. I suggest to solve the problem by issuing a findBy before the save, as this is messy, and I think it's not warranted that it will work in future versions, may even break without notification.

Answer 8

The following code works for me:

try {
    requete.executeUpdate();
} catch (final ConstraintViolationException e) {

}

Answer 9

I agree, but we've something like this in my Spring Application,:- RequestValidationException/MessageConstants are custom one's:

import org.springframework.dao.DuplicateKeyException;
|
|
|
catch (Exception exception) {
if(exception instanceof DuplicateKeyException) {
 logger.error("exception as duplicate Name Found: " + exception.getMessage());
 throw new RequestValidationException(MessageConstants.DUPLICATE_NAME_FOUND_ERROR_CD, MessageConstants.DUPLICATE_NAME_FOUND_ERROR_MSG); 
 }else{
        logger.error("exception on update: " + exception.getMessage());
        throw exception;
    }
 }