Java Catch and Display SqlExceptionHelper

Question

Using Hibernate JPA to execute native queries in Oracle DB due to their complexity, I want to catch an exception like " ORA-01722: Nombre non valide " thrown from SqlExceptionHelper class, but what was catching is:

class javax.persistence.PersistenceException: could not extract ResultSet

The logger error trace me that but not catched :

jdbc.spi.SqlExceptionHelper   : ORA-01722: Nombre non valide


BigDecimal customerId = null;
try {
    Query q = entityManager.createNativeQuery(
            "select acc.account_id as customerId from Account ...");
    customerId = (BigDecimal) q.getSingleResult();

} catch (Exception e) {

    logger.info("CLASS : " + e.getClass());
    if (e instanceof PersistenceException) {  // should display ORA-01722: Nombre non valide ?
        logger.info("ERRROR : " + e.getMessage());
        throw new SQLException(e.getMessage());
    }else
    if (e instanceof SQLException) {
        logger.info("ERRROR : " + e.getMessage());
        throw new SQLException(e.getMessage());
    }
    logger.info("NOOOOOOOOOOOOO : " + e.getMessage());
    throw new Exception(e.getMessage());
}

You do not need to catch it as generic exception. Doing instance of checks are unnecessary. You can catch different exceptions in different catch blocks. — Akiner Alkan, Jan 23 '19 at 09:07
I agree, but i want to display this exception [ORA-01722: Nombre non valide] to the front user to let him know his issue ! — Mirlo, Jan 23 '19 at 09:12
https://stackoverflow.com/a/9257574/4993989 - view this answer. — ARr0w, Jan 23 '19 at 09:16

score 1 · Answer 1 · answered May 24 '21 at 16:14

@Entity
@Table(uniqueConstraints = { @UniqueConstraint(columnNames = { "continentName" }) })
public class Continent implements Serializable 

    @NotEmpty(message = "continentName is a required field")
    private String continentName;

In your controller, you can handle the @NotEmpty with this:

@ExceptionHandler({ ConstraintViolationException.class })
    public ResponseEntity<String> handleError1(HttpServletRequest req, Exception ex) {

        String msg = null;
        if (ex.getCause().getCause() instanceof ConstraintViolationException) {
            ConstraintViolationException e = (ConstraintViolationException) ex.getCause().getCause();
            Optional<ConstraintViolation<?>> optional = e.getConstraintViolations().stream().findFirst();
            msg = optional.isPresent() ? optional.get().getMessageTemplate() : ex.getMessage();
        }

        return new ResponseEntity<>(msg, HttpStatus.CONFLICT);
    }

This will return "continentName is a required field"

Again in the controller, this method will handle a unique constraint violation. Assume you already entered a value "Brucrumus" and try to do it again:

@ExceptionHandler({ DataIntegrityViolationException.class })
    public ResponseEntity<String> handleError2(HttpServletRequest req, Exception ex) {

        String msg = ex.getMessage();
        if (ex.getCause().getCause() instanceof SQLException) {
            SQLException e = (SQLException) ex.getCause().getCause();

            if (e.getMessage().contains("Key")) {
                msg = e.getMessage().substring(e.getMessage().indexOf("Key"));
            }
        }

score 0 · Answer 2 · answered Jan 23 '19 at 09:24

0

SQLException is passed as an argument to SQLGrammarException constructor.

You can catch that, extract the root and display the native sql error or simply rethrow it:

} catch (SQLGrammarException e) {
    logger.info("CLASS : " + e.getClass());
    throw e.getSQLException();
} catch (Exception e){
   ...
}

answered Jan 23 '19 at 09:24

Maciej Kowalski

25,605
12
54
63

the catched class is : CLASS : class javax.persistence.PersistenceException – Mirlo Jan 23 '19 at 09:35
1

so edit the post.. there is explicitly stated that SQLGrammarException is catched – Maciej Kowalski Jan 23 '19 at 09:44

Java Catch and Display SqlExceptionHelper

2 Answers2

Linked