User:Ajoshi66/JSET - The SQL Exception Translator

What is SQL Exception Translation?
Any business application developer who is involved in writing DAO or other way of interaction with database would have surely come across handling SQL Exceptions. There are mechanisms in which you can translate a SQL exception to the corresponding business exceptions. This is known as SQL Exception Translation.

jSET - The SQL Exception Translator is one such tool. Currently this tool is Java-based.

Why is it necessary?
Though you may have excellent framework that makes your application database independent, the application code that interacts with database may be still checking the SQL exception and converting it to application exception manually.

Most applications perform explicit database calls to avoid SQL exception, in spite of strong database design. For example, to avoid a primary key constraint violation, there could be a call to the database to query whether a record exist with the same primary key. If found, they would construct the corresponding business exception. Similarly, an explicit database call would be made to check for existence of a record in case of referenced entity.

But the above method is not feasible to handle all business cases and makes the application slower as the number of hits to the database increases. Moreover, to handle any new exception case it requires changes to the DAO or the persistor.

Think of a tool that can identify such exceptions based on the database constraints without performing explicit calls to validate these constraints and then converts them to corresponding business exceptions automatically when the constraint fails. If you are looking for such tools, then SQL Exceptions Translator tool is the answer.

What are the benefits?
You may see following benefits with such tool:


 * 1) Improvement in performance as you don’t have to make explicit calls to the database to validate the constraints
 * 2) Improved user experience as the user can understand what exactly caused the failure, than a cryptic messages.
 * 3) Code quality improves as you are no longer “assuming” the cause of failures for DB operations.
 * 4) Map the database exceptions to business exceptions. No need to write code to convert them.
 * 5) If you are using ORM tool like Hibernate, then you also have the additional advantage: You may generalize most common CRUD operations in to the base persistor that can act on any business entity that require typical database operations. Effort on persistor can be greatly reduced this way.

Following diagram how the translator works with your application.



As you may see in the above diagram, the DAO may catch the SQL exception during CRUD operations and pass it to SQL Exception Translator. The utility class (Exception Translator) uses the parser to get a key to the business exception. Using this key, it obtains the corresponding business message id and returns the same to the caller. The caller will then use the id to create the business exception. This is further explained in the section Concept.

Dependency
The SQL Exception Translator Lite depends on the following jars/libraries:


 * 1) Castor
 * 2) Xerces
 * 3) Apache Commons and Logging
 * 4) Castor Util Lite (A wrapper to Castor)

Concept
The tool works on the combination of error number, error state, and the error message of the SQL Exception to derive the business message. The database provides detailed information as part of error message, including the table name, column name, and the constraint names. The tool can use this information to map the message to the business message.

Each database implementation follows a standard to describe the error messages. Most SQL exceptions will have an error number and an error state. Based on the type of the error, the message may include table name, column name, constraint name, etc. The information in the error message may vary from database to database.

For example, when an unique constraint fails, the MS SQL Server may raise an error with the message:

Violation of UNIQUE KEY constraint 'constraint_name'. Cannot insert duplicate key in object 'table_name'.

Whereas, the same in case of MySQL database may generate an error with message:

Duplicate entry 'column_value' for key 'constraint_name'

However, as long as they are standardized for a given database, the tool should be able to work.

The SQL Exception Parser analyzes the message and constructs an exception key using error number, error state, table name, column name and/or the constraint name provide in the message.

For example, for the above message shown for the MySQL database, assuming the business entity is “Customer” and the constraint name is “PK_Customer_MS”, the exception key would be:

Customer.1062.PK_Customer_MS

This key can be used to map the exception to a business message. For example, Application_Resources_en_us.properties file may have the following message for the above key:

Customer.1062.PK_Customer_MS=Customer already exist The application can then display to the user that “Customer already exist”.

Conclusion
Usage of tool helps in improving the quality of deliverable and reduces the effort.

The example shown above is only a simple demonstration that uses most obvious scenario. However, using the tool, you may handle complex situation as long as you have strong database design and there is a SQL Exception that has enough information.

The tool, to my current knowledge, can easily work with Hibernate, but not JPA. The only reason that it cannot work with JPA is that JPA consumes the SQL / JDBC exception. The exception thrown by JPA is not good enough to understand the reason for failure of database operation.