Distinguish basic SQLException types in Java?

Question

Sometimes a command is expected to fail in certain situations.

Suppose I have pseudo-code

try {
    // insert row
} catch(SQLException ex) {
    // create table
    // insert row
}

I would like to be more specific than just catching a general SQLException. I would like to distinguish between.

A normal error such as table doesn't exist or column count doesn't match.

and

A more serious error such as storage space exceeded or communication error between Java and MySQL.

How can I do this? I've looked at the various choices such as error codes or subclasses, but I'm not sure which way to do it.

My target database is MySQL, but I would like this to be cross-database if there is an easy way to do it. (such as just catching a certain class more specific than SQLException)

possible duplicate of [Where can I find the list of SQLException error codes for MySQL?](http://stackoverflow.com/questions/7799086/where-can-i-find-the-list-of-sqlexception-error-codes-for-mysql) — Nir Alfasi, Jul 22 '14 at 17:55
That duplicate assumes the asker already knows that error codes are the way to go, which I didn't (until now), so I am not sure whether it qualifies as duplicate. — 700 Software, Jul 22 '14 at 18:14
@Sanjeev, That's what I wanted to know. If you post an answer then I'll accept. — 700 Software, Jul 22 '14 at 18:15

DavidPostill · Answer 1 · 2014-07-22T18:22:26.190

1

From Class SQLException:

Direct Known Subclasses:
    BatchUpdateException, RowSetWarning, SerialException, SQLClientInfoException, SQLNonTransientException, SQLRecoverableException, SQLTransientException, SQLWarning, SyncFactoryException, SyncProviderException

And:

public int getErrorCode()

Retrieves the vendor-specific exception code for this SQLException object.

Returns:
    the vendor's error code

Error codes for MySQL can be found at Appendix B Errors, Error Codes, and Common Problems

You should catch the most specific exception (the appropriate subclass) and then use getErrorCode() if you require additional information.

edited Jul 22 '14 at 18:22

answered Jul 22 '14 at 18:01

DavidPostill

7,734
9
41
60

This answer does not give me guidance as to whether I should use error codes or subclasses. – 700 Software Jul 22 '14 at 18:15
You could use either or both :) Answer has been expanded. – DavidPostill Jul 22 '14 at 18:19

score 1 · Answer 2 · answered Jul 22 '14 at 18:16

This is very opinion-based, but I think SQLException is too general to be used as is.

I understand that most programs accessing a database won't be faced with the task of handling cases when tables do not exist, but there are cases, such as when writing a framework or a tool when checking if the table exists IS important. For these cases it would make more sense to check also if the required columns are also present and have the right types. In these cases it makes sense to access the data dictionary. Of course only if the dictionary is available, which is not the case on certain production products.

When the data dictionary is not available then the only recourse is the error message and the error code, and both of them are vendor-specific. Which means there's no one solution fits all.

A long time ago I wrote a JDBCExceptionHandler, which would receive the SQLException, analyze its contents and would throw a more specialized Exception, even judging if the exception was recoverable or not. Eventually I moved on to Hibernate, which has a more comprehensive set of exceptions, coupled with the fact that I got tired of maintaining it as vendors changed their implementations.

But if you do want to pursue this line, then my advice is to use error codes and parsers on the error message. Subclassing will work, but only for your own set of exceptions. So, have an Exception Handler that will read the code and the message and then throw you own set of exceptions, subclassing them as you see fit.

score 0 · Answer 3 · answered Jul 22 '14 at 18:21

In my opinion SQL Error codes are the way to go in this kind of scenario because they may provide actual implementation specific error codes as mentioned in Oracle docs:

SQL error code. This is an integer value identifying the error that caused the SQLException instance to be thrown. Its value and meaning are implementation-specific and might be the actual error code returned by the underlying data source. Retrieve the error by calling the method SQLException.getErrorCode.

For example, if you call the method CoffeesTable.dropTable with Java DB as your DBMS, the table COFFEES does not exist, and you remove the call to JDBCTutorialUtilities.ignoreSQLException, the output will be similar to the following:

SQLState: 42Y55
Error Code: 30000
Message: 'DROP TABLE' cannot be performed on
'TESTDB.COFFEES' because it does not exist.

Hope this helps.

score 0 · Answer 4 · edited Aug 15 '18 at 11:50

0

you can do some thing like this

catch(SQLException e)
{

    if(e.getMessage().indexOf("ORA-00001")!=-1)
    JOptionPane.showMessageDialog(null,"you have add some 
    information not duplicated ,please click ok and try again...");
}

edited Aug 15 '18 at 11:50

dhilmathy

2,800
2
21
29

answered Aug 15 '18 at 11:37

Zan Di

1

Distinguish basic SQLException types in Java?

4 Answers4