jdbijdbi3

How to map SQLException from SQL-Object in JDBI3


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.


Solution

  • 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.