DuplicateKeyException in JooQ

Question

I am using JooQ to perform CRUD operation on Aurora MySQL.

I have the following scenario:

1. If insertion fails due to duplicate key exception then retry same operation again otherwise ignore the exception.
2. If insertion fails then update the records if the conditions are met.

Is there any way to catch DuplicateKeyException only? JooQ is throwing exception as DataAccessException.

On enabling OnDuplicateKeyUpdate, it seems that if the record exist it will blindly update the record in database. My use case is if the record exist in database then update the record if the update conditions are met.

I can do that in two separate call of Insert and Update but is there any way t do it in single call?

Thanks!!

Answer 1

There are two questions in your question:

Is there any way to catch DuplicateKeyException only? JooQ is throwing exception as DataAccessException.

Yes of course. jOOQ doesn't have separate exception types for individual errors like JDBC, but it still:

• References the JDBC SQLException that caused the DataAccessException. You can access it through dae.getCause() or dae.getCause(SQLException.class), the latter being useful in case the original SQLException having been wrapped several times.
• References the DataAccessException.sqlStateClass(). The one you're looking for is SQLStateClass.C23_INTEGRITY_CONSTRAINT_VIOLATION. If your JDBC driver propagates the SQL state class in its exceptions, then this information will be available to you through jOOQ.

On enabling OnDuplicateKeyUpdate, it seems that if the record exist it will blindly update the record in database. My use case is if the record exist in database then update the record if the update conditions are met.

You could encode your update condition in the ON DUPLICATE KEY UPDATE's SET clause:

INSERT INTO t (id, value)
VALUES (1, 2)
ON DUPLICATE KEY UPDATE SET
  value = CASE WHEN value = 2 THEN 3 ELSE value END

In the above example, the UPDATE clause will update value to 3 only if it is 2.