How to map SQLException from SQL-Object in JDBI3

Question

Lets say I have table defined as:

CREATE TABLE ITEMS(
  ID BIGINT PRIMARY KEY,
  NAME VARCHAR2,
  CONSTRAINT NAME_IS_UNIQUE UNIQUE (NAME)
);

Important part is NAME_IS_UNIQUE constraint.

With corresponding POJO Item as:

class Item{
  private Long id;
  private String name;
  /** getters and setters */
}

And SQL-Object interface with methods defined as:

@SqlUpdate("insert into items(id, name) values(:id, :name)")
int insert(@BindBean Item itemToInsert);

If I'll try to insert into ITEMS with already existing NAME then I will get DB vendor specific SQLException about constraint NAME_IS_UNIQUE violation.

Is there a way to provide mapping between SQLException and application specific Exception (for example ItemNameUniqueConstraintException) so insert method essentially changed it signature to something like the one below?

@SqlUpdate("insert into items(id, name) values(:id, :name)")
int insert(@BindBean Item itemToInsert) throws ItemNameUniqueConstraintException;

Question is not about specific UNIQUE constraint, but more about general case, where SQLException can be about anything: Like referential integrity violation or check constraint violation, etc.

score 0 · Accepted Answer · answered Jan 28 '21 at 08:50

At this moment there is no supported way to handle SQLException -> ApplicationException mapping, you can read discussions and reasoning in the issue.

But you can use workaround with default methods and handle exception manually, e.g.:

    class ItemNameUniqueConstraintException extends Exception {
        public ItemNameUniqueConstraintException(String message, Throwable cause) {
            super(message, cause);
        }
    }

    interface Repository {
        default void insert(String name) throws ItemNameUniqueConstraintException {
            try {
                _insert(name);
            } catch (JdbiException e) {
                if (e.getCause() instanceof SQLException) {
                    var cause = (SQLException) e.getCause();
                    if (cause.getSQLState().equals("11111")) {
                        throw new ItemNameUniqueConstraintException("Name not unique.", cause);
                    }
                }
                // ...
            }
        }
        @SqlUpdate("INSERT INTO test (name) VALUES (:name)")
        void _insert(@Bind("name") String name);
    }

It is not very pretty, but can be made a little better with separate interfaces for contract of repository and JDBI implementation, which can allow not to expose _insert and similar methods to the caller.

Thanks for reply. I did it exactly the way you described, as default method of interface. Also, in case of spring, I wrote dedicated interceptor which was mapping exceptions in generic fashion. But it made it dependent on spring which is not an universal solution. It should be not that complicated to use java proxy to wrap JDBI-object and filter exceptions (as I did with spring). The thing is that I feel that this should be part of JDBI. Also, I read the discussion you pointed me on - I think I have something to add into it :) — alebu, Jan 29 '21 at 09:32

How to map SQLException from SQL-Object in JDBI3

1 Answers1