Better way for DB exception handling in java

Question

Which one will be better: ErrorCode or Exception for that situation?

I have ever been seeing these two error handling techniques. I don't know the disadvantages and advantages for each technique.

public void doOperation(Data data) throws MyException {
    try {
        // do DB operation
    } catch (SQLException e) {
        /* It can be ChildRecordFoundException, ParentRecordNotFoundException
         * NullValueFoundException, DuplicateException, etc..
         */
        throw translateException(e);
    }
}

or

public void doOperation(Data data) throws MyException {
    try {
        // do DB operation
    } catch (SQLException e) {
        /* It can be "CHILD_RECORD_FOUND, "PARENT_RECORD_NOT_FOUND"
         * "NULL_VALUE_FOUND", "DUPLICATE_VALUE_FOUND", etc..
         */
        String errorCode = getErrorCode(e);
        MyException exc = new MyException();
        exc.setErrorCode(errorCode);
        throw exc;
    }
}

For second method, the error code retrieve form configuration file. We can add Error Code based on the SQL Vender Code.

SQL_ERROR_CODE.properties

#MySQL Database
1062=DUPLICATE_KEY_FOUND
1216=CHILD_RECORD_FOUND
1217=PARENT_RECORD_NOT_FOUND
1048=NULL_VALUE_FOUND
1205=RECORD_HAS_BEEN_LOCKED

Caller client for method 1

    try {

    } catch(MyException e) {
        if(e instanceof ChildRecordFoundException) {
            showMessage(...);
        } else if(e instanceof ParentRecordNotFoundException) {
            showMessage(...);
        } else if(e instanceof NullValueFoundException) {
            showMessage(...);
        } else if(e instanceof DuplicateException) {
            showMessage(...);
        }
    }

Caller client for method 2

    try {

    } catch(MyException e) {
        if(e.getErrorCode().equals("CHILD_RECORD_FOUND")) {
            showMessage(...);
        } else if(e.getErrorCode().equals("PARENT_RECORD_NOT_FOUND") {
            showMessage(...);
        } else if(e.getErrorCode().equals("NULL_VALUE_FOUND") {
            showMessage(...);
        } else if(e.getErrorCode().equals("DUPLICATE_VALUE_FOUND") {
            showMessage(...);
        }
    }

artbristol · Accepted Answer · 2012-09-19 09:58:24Z

6

I recommend using Spring's JDBCTemplate. It will translate most existing databases' exceptions into unchecked exceptions that are specific, e.g. DataIntegrityViolationException. It will also include the original SQL error in the message.

answered Sep 19, 2012 at 9:58

artbristol

32.5k5 gold badges73 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens Schauder · Accepted Answer · 2012-09-19 10:14:40Z

Strange question, since both approaches do the same thing: they transform a checked SqlException in a different exception which seems to be unchecked. So the first one is the better one because it moves this into a single method.

Both leave some questions to be asked:

Isn't there some infrastructure that can do this conversion (Spring Template was mentioned in another answer)
Do you really want checked Exceptions, in my mind they are hardly ever worth the trouble.
Who is doing the real handling of the exception, does it get all the information needed? I would normaly expect some additional information about the transaction that failed inside of MyException, like: What did we try to do? (e.g. update a busines object); On what kind of object? (e.g. a Person); How can we/the user Identify the object (e.g. person.id + person.lastname + person.firstname). You will need this kind of information if you want to produce log/error message that tell you or your user more than 'Oops, something is wrong'
Why is MyException mutable (at least in the 2nd example)

SJuan76 · Accepted Answer · 2012-09-19 10:04:06Z

2

IMHO, it depends as how tightly your code is coupled with SQL.

If the method is to always (*1) be coupled with SQL, I would just declare and rethrow the SQLException (after cleanup / closing resources). Upper methods that are SQL-aware would then process it as they see fit (perhaps they need all the detail, perhaps they not).

If sometime in the future you could change the method for another which does not use SQL, then I would go for the second option.

(1): Be extra pessimistic with this assumption: "I think we are not going to change" should be interpreted as "Probably we will want to change". "We are not going to change" means "We cannot change without breaking lots of other methods anyway".

answered Sep 19, 2012 at 10:04

SJuan76

24.9k7 gold badges52 silver badges95 bronze badges

Comments

duffymo · Accepted Answer · 2012-09-19 09:56:15Z

1

A better design than either one would be to make your custom exceptions unchecked by extending RuntimeException.

I'd want your exception to wrap the first one, so coding it this way would be better, too:

MyException exception = new MyException(e); // wrap it.

If you do that, the second one is preferred. More information is better.

answered Sep 19, 2012 at 9:56

duffymo

310k46 gold badges376 silver badges571 bronze badges

4 Comments

SJuan76 Over a year ago

Why would be a RuntimeException better? Any developer that uses this code will find about the exception when it happens (which could be at production time).

duffymo Over a year ago

C# has shifted the idiom from unchecked to checked exceptions. Spring framework does exactly what you're doing, except much better and 12 years ago. They use unchecked exceptions.

Jens Schauder Over a year ago

why do you think, the second approach retains more information? All the information is comming from the exception which gets passed as an argument in the first example.

duffymo Over a year ago

I know that SQLException in Java is a checked exception that explicitly retains vendor SQL error codes - something that no other checked exception does. I'm assuming that the second one is in that spirit.

basiljames · Accepted Answer · 2012-09-19 10:15:31Z

0

One differnce would the way you will catch the exception. In the first cases you can just catch the exception and you know what the error is. In the second case you have to catch the exception and check the code to see what the error is.

answered Sep 19, 2012 at 10:15

basiljames

4,8574 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

Better way for DB exception handling in java

5 Answers 5

Comments

Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related