Enhanced SQL Exception Handling

5. Enhanced SQL Exception Handling:

A lot of improvement has been done regarding Exception handling in the following fields:

1. Iterable SQL Exception
2. Concreate Sub Classes for specific SQL Exception

 Iterable SQL Exception

A new interface which is implemented by the java.sql.SQLException is Iterable<Throwable> interface. To get the Next Set of Exceptions, this interface (Iterable<Throwable> interface) can be iterated further. Not only this it can also be used in Enhanced For-Loops (available from Java 5.0). 

For example:

try{

// Some Database Access Code

}catch(SQLException exception){

for(Throwable throwable: exception){
System.out.println(throwable.getMessage());

}
}

However earlier to get the next Exception the JDBC 4.0 applications used to rely on SQLException.getNextException(). Unlike Iterable SQL Exception, the SQLException.getNextException() used to loop over until it gets the null as a returned exception.

try{

// Some Database Access Code

}catch(SQLException exception){

System.out.println(exception.getMessage());
SQLException nextException = exception.getNextException();

while (nextException != null){
System.out.println(nextException.getMessage());
nextException = nextException.getNextException();
}
}

Concreate Sub Class for specific SQL Exception

Another feature which is added to Java 6.0 is Sub Class for specific SQL Exception. Few SQL Exception related Sub-Classes have been  added to the API. These Exceptions which encapsulate the State of the SQL Error are divided into three groups namely Transient, Recoverable and Non-Transient which are described below.  

Transient Exception: It is a kind of exception in which the program recovers from the exception without altering the application. For example, during the program run-time any type of SQL Exception occurs and if in another attempt the program recovers from the same exception then this kind of exception is known as Transient Exception. The java.sql.TransientException class represents this exception. In JDBC 4.0 API following are the identified Transient Exceptions.

1. SQLTransientConnectionException
2. SQLTimeoutException
3. SQLTransactionRollbackException

Recoverable Exception: This exception is represented by java.sql.SQLRecoverableException. It is a bit different from the Transient Exception in the sense that this exception can be recovered by applying some Application Specific Recovery.

Non-Transient Exception: This exception is represented by java.sql.TransientException. This exception signifies that no matter how many times the application Program tries to recover the exception, it can never be recovered. The exceptions which belong to this group are:

1. SQLFeatureNotSupportedException
2. SQLNonTransientConnectionException
3. SQLDataException
4. SQLInvalidAuthorizationSpecException  
5. SQLSyntaxErrorException
6. SQLIntegrityConstraintViolationException

6. Support for SQL RowId

A Row Id is an unique Identifier which is supported by databases such as Oracle and DB2. It is used to identify a Row. For instance,  there are multiple records without having a unique identifier column then to store the query output in a Collection (such Hashtable) that doesn't allow duplicates ............................................................

The following piece of code shows how to retrieve the value of the Row Id for a particular Row.

String selectQuery = " select rowid from Employees where id ='123' ";
ResultSet resultSet = statement.executeQuery("selectQuery");
java.sql.Rowid rowId = resultSet.getRowId();

The RowID interface was added to JDBC 4.0 to support the ROWID data type which is supported by databases such as Oracle and DB2. RowId is useful in cases where there are multiple records that don't have a unique identifier column and you need to store the query output in a Collection (such Hashtable) that doesn't allow duplicates. We can use ResultSet's getRowId() method to get a RowId and PreparedStatement's setRowId() method to use the RowId in a query.

An important thing to remember about the RowId object is that its value is not portable between data sources and should be considered as specific to the data source when using the set or update methods in PreparedStatement and ResultSet respectively. So, it shouldn't be shared between different Connection and ResultSet objects

Most of the popular Databases support the concept of a Row Id. A Row Id is an unique Identifier which is used to identify a Row. Support for Retrieving and setting the Row Id for a row is made available from JDBC 4.0 version. For example, consider the following piece of code, which will retrieve the value of the Row Id for a particular Row.

String selectQuery = " select rowid from Employees where id ='123' ";
ResultSet resultSet = statement.executeQuery("selectQuery");
java.sql.Rowid rowId = resultSet.getRowId();

The Row Id is represented by java.sql.RowId class. Before doing any kind of manipulation on the Row Id, it is wise to check whether the under-lying Database implementation provides Support for Row Id(s), as well as the Lifetime of the Row Id objects. The life-time of the Row Id determines how long the Row Id is valid, whether the Row Id exists only for a particular Session or for a Set of Transactions within a Session or Outside the Database Session etc. This availability can be known with the help of DatabaseMetaData like the following,

RowIdLifeTime rowIdLifeTime = DatabaseMetaData.getRowIdLifetime();

if (rowIdLifeTime != ROWID_UNSUPPORTED){
// Row Id support is there for this Data source.
}

The returned RowIdLifeTime is an Enum which tells about the Life-Time of the Row Id object. Possible values are ROWID_UNSUPPORTED, ROWID_VALID_FOREVER, ROWID_VALID_OTHER, ROWID_VALID_SESSION, ROWID_VALID_TRANSACTION and ROWID_VALID_FOREVER.