Search code examples
spring-bootjooq

Unexpected mapping of exception from trigger - spring-boot 2.4.x + jOOQ

We are using spring-boot with spring-boot-starter-jooq. For some complex checks of constraints triggers are used which raise error with specific message. The exception is caught on persistence layer and converted into business exception.

The behavior has been changed in last spring boot versions 2.4.x. For example hsqldb with scherma:

CREATE TABLE tab1 (
    K INT PRIMARY KEY
)^;

CREATE TRIGGER trig1 BEFORE INSERT ON tab1
BEGIN ATOMIC
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'some error';
END^;

and the code for insert into table:

    @Transactional
    public void withTriggerException() {
        create.insertInto(DSL.table("tab1"))
                .set(DSL.field("k"), 1)
                .execute();
    }

The type and the message of thrown exception differs from spring-boot version.

2.3.6, 2.3.9

org.springframework.jdbc.UncategorizedSQLException: jOOQ; uncategorized SQLException for SQL [insert into tab1 (k) values (cast(? as int))]; SQL state [45000]; error code [5800]; some error; nested exception is java.sql.SQLException: some error

2.4.0, 2.4.1, 2.4.2

java.lang.NullPointerException: null

2.4.3

org.jooq.exception.DataAccessException: SQL [insert into tab1 (k) values (?)]; Unspecified RuntimeException

    at org.jooq_3.14.7.HSQLDB.debug(Unknown Source) ~[na:na]
    at org.jooq.impl.Tools.translate(Tools.java:2892) ~[jooq-3.14.7.jar:na]
    at org.jooq.impl.DefaultExecuteContext.exception(DefaultExecuteContext.java:730) ~[jooq-3.14.7.jar:na]
    at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.handle(JooqExceptionTranslator.java:83) ~[spring-boot-autoconfigure-2.4.3.jar:2.4.3]
    at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.exception(JooqExceptionTranslator.java:55) ~[spring-boot-autoconfigure-2.4.3.jar:2.4.3]
    at org.jooq.impl.ExecuteListeners.exception(ExecuteListeners.java:274) ~[jooq-3.14.7.jar:na]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:390) ~[jooq-3.14.7.jar:na]
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:119) ~[jooq-3.14.7.jar:na]
    at com.example.demo.FooDAO.withTriggerException(DemoApplication.java:38) ~[classes/:na]
...

The behavior has been reproduced on hsqldb but is same on PostgreSQL. Another errors (duplicate key, foreign keys, etc.) are working as expected.

It is possible to obtain error message ("some error" in example) from trigger with spring-boot 2.4.x?

Solution

  • You probably ran into this issue:

    Some background on the incompatible change in Spring that produced this NPE regression in jOOQ can be seen here:

    The fix should be available in Spring Boot 2.5.0 and 2.4.3:

    You should be able to access the JDBC SQLException from jOOQ's DataAccessException using DataAccessException.getCause(Class), e.g.

    exception.getCause(SQLException.class);

    But there's probably a better way to have the Spring exception translator provide you with the actual cause directly.