How to handle a PSQLException in java?

Question

I have a unique constraint on one of my entities and whenever I get a PSQLException which occurs whenever that constraint is violated, I want to respond with a bad request.

This is my exception handler which I tried to implement:

@ControllerAdvice
public class DatabaseExceptionHandler {
    @ExceptionHandler(value = PSQLException.class)
    @ResponseStatus(HttpStatus.BAD_REQUEST)
    public void handleDatabaseExceptions(PSQLException e) {
        // i want to respond with a bad request only when this condition is satisfied
//
//        if (e.getSQLState().equals("23505")) {
//
//        }
    }

}

And this is where the model is saved in db:

 public DepartmentForHoliday setDepartment(DepartmentForHoliday department) {
        if (department.getDepartmentId() == null) {
            Department savedDepartment = new Department();
            savedDepartment.setName(department.getName());
            try {
                departmentRepository.save(savedDepartment);
            } catch (PSQLException e) {
              /*here i have a compiler error which says that this exception is never thrown in the corresponding try block, but where ?*/
            }
}

This is the exception that is thrown when I add a duplicate entry:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uk_1t68827l97cwyxo9r1u6t4p7d"
  Detail: Key (name)=(Tech) already exists.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458) ~[postgresql-9.4.1211.jre7.jar:9.4.1211.jre7]

How to handle PSQLExceptions ? Should I make my own exception as a wrapper or how to solve this problem ?

Answer 1

Key problem is that PSQLException is wrapped into some Spring exception (which I assume from your code); you have to unwrap it (for example using guava's Throwables):

public DepartmentForHoliday setDepartment(DepartmentForHoliday department) {
    if (department.getDepartmentId() == null) {
        Department savedDepartment = new Department();
        savedDepartment.setName(department.getName());
        try {
            departmentRepository.save(savedDepartment);
        } catch (RuntimeException e) {
            Throwable rootCause = com.google.common.base.Throwables.getRootCause(e);
            if (rootCause instanceof SQLException) {
                if ("23505".equals(((SQLException) rootCause).getSQLState())) {
                    // do smth interesting :)
                }
            }
        }
    }
}

Once you do that you can throw your custom exception and handle it in DatabaseExceptionHandler

Answer 2

You are catching PSQLException. Instead of that, please catch SQLException. With SQLException you will can handle all this SQL exceptions.

You can check the SQLException knowledge at this link

Then in your code just treat the SQLException as you want. The most generic catch clause is the following one:

    catch (SQLException e)
   {
   System.out.println("ERROR: Fetch statement failed: " +
      e.getMessage());
   }

With this code you are printing the exception. If you want more information, check this

Answer 3

This is quite late, but building on previous responses I was able to solve it as so:

try {
    return this.projectRepository.saveAndFlush(patchedProjectEntity);
} catch (DataIntegrityViolationException e) {
    if (e.getMostSpecificCause().getClass().getName().equals("org.postgresql.util.PSQLException") && ((SQLException) e.getMostSpecificCause()).getSQLState().equals("23505"))
        throw new UniqueConstraintViolationException("", e.getMostSpecificCause());
    throw e;
}

Where UniqueConstraintViolationException is a custom exception and handled with a spring controller advice.