Why is SQLException a checked exception [closed]

Question

Can anyone think of a rational reason why SQLException is a checked exception?

Yes, there could be a syntax error in the query
Yes, the connection might have died
Yes, there might be a permission problem
Etc, etc blah blah blah

But practically 100% of the time (once you're running in production), there isn't any problem.

If there is a problem, the calling code can't do anything to recover, so for that reason it should be unchecked.

Being checked create masses of perfunctory try catch blocks throughout the code, as anyone who has been involved with a project that uses JDBC will attest. The code clutter is significant.

Because of the esoteric nature of SQL, the myriad of reasons you may get an SQLException and their complexity means you basically can not recover, unless the exception is caused by temporary network problem, but even then in a synchronous call, you're sunk anyway because you can't wait indefinitely for the network problem to be resolved, so you're going to have to fail the transaction.

Typically, calling SQL looks like this:

try {
    // make some SQL call(s)
} catch {SQLException e) { 
    // log the exception
    return; // and give up
}

Such code adds no value. There nothing reasonable you can do to recover. You might as well let a runtime exception bubble up - ie SQLException should be a runtime (unchecked) exception.

Arrhenius

A) Networks, database servers, load balancers, etc don't have 100% uptimes B) You most certainly can do something when a failure occurs. – Soybean

The server failing happens more often then you would expect, and is something the client should handle. (Most likely, by showing a error message, and trying again a little later). Mostly with deadlock issues. – Transpacific

@BrianRoach OK - say the network goes away. What can the calling code going to do to bring the network back? Nothing! So, again, why is it checked? Checked implies that the caller can recover. – Arrhenius

@Transpacific Well with deadlock, the calling code definitely can not recover - so there's that one dealt with. And servers do not fail "more often than you would expect". Actually, unless you're running your database on a windows platform, you can expect databases to remain up for a very long time indeed. – Arrhenius

>But practically 100% of the time (once you're running in production), there isn't any problem. So you are saying 100% of the time, at least, the db is up ? What if it were a cluster of db servers and the one you were reading/writing from died ? – Greenaway

@Greenaway Exactly - you'd get failover and it wouldn't be a problem. With a cluster. your SLA is very high indeed. And if something did fail, you'd have no hope of recovering in the calling code, so we're back to the old "it should be unchecked" – Arrhenius

Using the integer error code that isreturned by the underlying database, you can connect to an alternate database. Or the exception handling mechanism can notify admins of the db being down and also inform the user of an error. – Greenaway

Apparently you've never worked in a real environment? Number one, I'm certainly not going to let my application just crash. It's also possible I have a backup I can try. Or perhaps I need to notify something that I've failed, or at least log it. Or ... any number of other things. "recover" doesn't necessarily mean what you think it does, and that's even ignoring a transient network failure where trying to reconnect would work. And that's not even getting to talking about connection pools where perhaps the connection you're trying to use simply timed out or was disconnected. – Soybean

And also, since I am swimming in this right now, if you were to weave spring aop, it helps in identifying criteria for running some blocks of code. – Greenaway

@BrianRoach database connections from connection pools are checked before being give to you to use. It is highly unlikely that you will have a connection problem with one. And there's always a top-level catch Exception in all good apps that would catch an unchecked exception, just like it catches NPEs; just because exceptions are unchecked doesn't mean your "application will crash". And you're still welcome to catch specific unchecked exceptions if you want to. – Arrhenius

The application can easily recover from the Deadlock. Just run the queries in the transaction again. But there is one thing I did not think about before. Constraint violation. Might happen on things such as unique constraints for username/email with concurrent users, and that need to be handled(To inform the user). – Transpacific

If you generally want to return on the SQLException, then you should be happy it is a checked exception, as it differs from the default RuntimeException behavior: let the RuntimeException to bubbling-up – Rubricate

@bla But an SQLException is practically an Error: You basically can't recover from them - your app is hosed - and Errors are unchecked. Why not SQLException too. – Arrhenius

@Arrhenius I would agree SQLException should be a RuntimeException. I tend to believe all exceptions should be unchecked. But even if I generally prefer unchecked exceptions, your example demonstrate you expect a checked exception (as you do "return whatever"); if you expected SQLException to be unchecked, the exposed redundant code should consist in re-throwing an unchecked exception. – Rubricate

Answer 1

practically 100% of the time there isn't any problem - This is limited to your own observation which says nothing about other systems. There are various computer systems around the world with various bottlenecks. Your success rate is almost 100%. Others have to deal with much lower percentage.

Common misconception is to consider introducing/removing a Checked Exception by frequency of its occurrence. Checked exceptions serve as communication channels. As you know, every method has its public interface. This way, method tells us which arguments it accepts and what is result of the code in its body.

When it becomes impossible for a method currently being in progress to keep its promise (e.g. returned value) it needs a way to tell the other method that something went wrong and it can't do what was expected. But how to do it ? Sending the message as the value returned doesn't work, there is almost no chance for the calling method to distinguish between proper value and an error message. Not to say some methods have void as a return value. So what do you do when you're unable to keep your promise defined by your method's interface ? Well, you throw an exception (send a message).

If you expect ResultSet and there is no connection to your database established, what should you do ? Return empty ResultSet ? Hell no, this tells us that the database is empty. Return null ? Well, this only delegates the problem and makes finding the cause unclear.

You could use that empty resultset and make it a part of another query to another database, making it inconsistent.

Without SQLException, even one mistake could lead to data inconsistency.

Representationalism

If systems were failing a significant percentage of the time, people wouldn't use them. Success is so close to 100%, you might as well call it 100%. I'm not saying to not throw an exception, just that it should be unchecked. Let it bubble up. As I've said, a checked excpetion implies the caller can do something to recover, but with SQL you can't recover. Exactly what are you going to do in your code if you get a syntax error exception? Nothing, that's what! You'll maybe log the error and exit the method - you've added no value. It might as well have not caught it. – Arrhenius

Its not the responsibility of JDBC libraries to force people to deal with the fact that their infrastructure may go down. If the database can't be reached than make the SQL operation explode and say why. In the case that a SQLException occurs you're going to handle it no differently than if a runtime exception took place, because thats really what it is. – Aile

I'd also like to add the OP's question has nothing to do with Eliminating SQLException and the way JDBC works under the hood you can't return ResultSets and similar objects if the session/connection has failed. I think this answer is several layers beneath the layer the question is concerned with. – Aile

Answer 2

There are a couple of approaches to the checked vs unchecked dilemma. Checking if the calling code can recover from the exception or not is one approach, this one however, I agree, does not explain why SQLExcption is a checked exception.

Another one, provided by Martin Fowler in his great book "Refactoring" suggests to verify wether it is the calling or the called method responsibility to make a check that might result in an exception.

If the caller method should perform a check prior calling the called method (e.g. making sure the arguments are not not null) then if this check has not been done it is clearly a programming error and then the called method should thrown an unchecked exception.

Now if it is the called method responsibility to make the check, because only this method can know how to perform such check then the exception thrown from the called method should be checked.

In case of SQLException I think only this class can know:

• there is a syntax error in the query as this depends on the database
• the connection has died
• there is a permission problem

Ambassadress

OK. Let's suppose to check that the SQL syntax is correct (by re-writing the SQL parser in java!), and say there is an error - what are you going to do about it? Nothing! You can't fix it, so why are you catching an exception? The only action you are going to do is log the exception and throw another exception - why not just let an unchecked exception percolate up? – Arrhenius

Ok, I agree, in most cases your application will not be able to recover in such case. But in case of "the connection has died" it can easily recover from it by re-establishing a connection to the database. I guess, the way you perceive if an exception is recoverable or not depends solely on the author of the calling method, which means it cannot be used as a criteria for deciding between a checked vs unchecked exception. – Ambassadress

Answer 3

One reason would be that a method should throw exception which are consistent with the abstraction level of what the method does.

So a method which loads information from a database should not raise a SQLException, but rather a ResourceNotFoundException or a ResourceUnavailableException.

Making the SQLException checked is a way to force the developper to catch the Exception and wrap it in this new level of abstraction.

This argument is taken from Effective Java Second Edition by Joshua Bloch (Item 61: Throw exceptions appropriate to the abstraction).

Seasickness

But why would "loading data from the database" fail? The fact is, it doesn't fail! It might "fail" in the sence that a row wasn't found, but that won't throw an SQLException. And if it does fail, it "explodes" and the caller can't do anything to recover - that's the essence of why to use an unchecked exception. – Arrhenius

I agree in most cases there is nothing to do except "explode" the application. But I can imagine some cases where you should not. For example if there exist a backup database, or the information you cannot fetch is not in the primary database (e.g. error when fetching your wishlist on an ecommerce site). – Seasickness

Note that "information is not the the primary database" won't throw an exception - you'll just get an empty rowset. You're talking about the primary database having gone down - pretty catastrophic. I you've got code to deal with that, there's nothing stopping you from catching an unchecked SQLException and doing something. What if folks don't have that capability - they would like SQLException to be a runtime. – Arrhenius

The ResourceNotFoundException is a fair one but I would argue if you're building this type of library or SDK around JDBC you would want to wrap lots of lower level methods in generic Try/Catch/Exception blocks anyway, for any possible error but you'd still want the underlying SQLExeption and its cause to be made known. Its not like you'll change the SQLException into a ResourceNotFound without mentioning SQL syntax is wrong, connection pool closed, etc etc. – Aile

Answer 4

Catching exceptions grant us the ability to recover from exceptional conditions, true, but they also allow us to do other things.

You cannot recover from a SQLException in that there isn't much you can do to fix the problem at run time, but there are some useful things you can do:

• Log the exception for debugging information
• Rollback a transaction

You could always log the exception at a higher level (or lower, depending on perspective), but you lose some semantic value, both at debugging time and when reviewing the code.

If you do something like:

try { ... }
catch(SQLException e) 
{ 
    SomeLogger.log(...);
    rollback();
    throw e;
}

and come back to this code later, you'll instantly realize that the code in the try can fail without having to mentally parse the code to determine if it can fail.

Another thing you could do is ensure any database resources have been released, though I'm not sure if this can happen off hand.

Barrera

The question is not about the relevancy of catching the exception (as RuntimeException can be caught), but about the relevancy of forcing to catch it (as adding throw SQLException in the method signature is probably not relevant either and according to the question, in most cases, nothing is done except rethrowing) – Rubricate