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.

Answer 1

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.