Java – How do I map SQLException to business exceptions using JOOQ?

I’m using JOOQ and want to map some SQLExceptions to business exceptions. exception handling documentation page says:

The following section about execute listeners documents means of overriding jOOQ’s exception handling, if you wish to deal separately with some types of constraint violations, or if you raise business errors from your database, etc.

However, there are no practical examples of the page about execute listeners.

I

know I have to implement ExecuteListener's method exception(ExecuteContext), but it’s not clear to me whether I should throw another exception to start there, or use ExecuteContext.exception instead The Exception method overwrites the existing exception. For example.

@Override
public void exception(ExecuteContext ctx) {
    if (ctx.sqlException().getSQLState().equals("23503")) {
        throw new ForeignKeyViolationException("Foreign key violation", ctx.sqlException());
    }
}

Or:

@Override
public void exception(ExecuteContext ctx) {
    if (ctx.sqlException().getSQLState().equals("23503")) {
        ctx.exception(ForeignKeyViolationException("Foreign key violation", ctx.sqlException()));
    }
}

I’m afraid, you’ll have to do it yourself. That’s why…

Using ExecuteListener doesn’t work for you

If you want jOOQ 的 DataAccessException As an alternative to exceptions, it is useful to choose the ExecuteListener method to automatically and globally translate SQLException. – For example, Spring’s > DataAccessException is used for further processing (example translator here)。 It is not suitable for automatically reconnecting exceptions that violate constraints to specific “business exceptions” because the ExecuteListener (as a global participant) does not know in what context the constraint violation may have occurred. There may be:

• Errors that cause constraint violations (for example, you should update instead of insert).
• A user error that results in a constraint violation (for example, a user submits a form twice).
• The actual business rule that results in a constraint being violated, such as checking a constraint

I’m afraid you have to make a decision for each query individually. ExecuteListener only helps you reconnect the technical part of exception handling.

Why is “business error” mentioned in the manual?

You cited the manual:

or if you raise business errors from your database

These business errors

can be user-defined errors that you raise from database triggers, for example, in which case you do not receive a constraint violation, but instead receive meaningful business errors directly from the database.