Hibernate - Multiplatform handling of exception/errors

I'm trying to implement DAO using hibernate. The final product will be multi-platform, and  I need to find a platform-independent form of receiving exceptions/errors during dabase operations. Hibernate wraps everthing in very high level exceptions (For instance, ConstraintViolationException). This is not good for me, I need something better to explain the users. After extensive research, all I could find is using the SQLState code returned by the JDBC driver (which is accesible in a nested exception). The problem is that, I cannot find a complete reference of the codes and an explanation of what they mean, to see if they are usefull for what I need to do.

Answer should be either a link to a complete list of Standards-defined SQLStates with the meaning of each one, or another alternative on how to solve the problem.
gmartelliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
You could enable internal logging, which AFAIK can be configured to use log4j. This may give you even better info than simply db debug output
gmartelliAuthor Commented:
My problem is not particuarly about logging.... is about knowing what happened exactly when I operate in the database.

For instance:

An user tries to create a new Customer
Web layer calls Business Service
Business Service calls DAO
DAO implementation uses hibernate to insert into a database
Insert fails, because a duplicated UK constraint
Insert fails, for a invalid FK
etc.

All of these get wrapped into a ConstraintViolationExcepton for hibernate. There's no way to know exactly what kind of error I had but diving into the database specific error.
Only thing I could find is SQLCode which might be multiplatform, but I coudn't find a clear list / explanation of SQLCodes
CEHJCommented:
>>is about knowing what happened exactly when I operate in the database.

Yes but enabling internal logging may tell you this
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

beegorCommented:
Hi,

You should not depend on sql error codes, because every database and every jdbc driver can have it's own codes.

Instead your application architecture should provide you way to know what happend. For example you should have method createNewCustomer(). Then you can catch Exception inside that method and display it or dispatch it to some higher level.

If you are dispatching the Exception, you are probably catching it in centrialized way (you are catching Exceptions on top level of your architecture) you need some way to recognize the type of Exception in order to know what message to display to user or logg it. One way is to introduce your own exceptions by extending Exception and then throwing it from your data acces layer - createNewCustomer method for example.

Then you could recognize the type of exception by calling instanceof operator.

if(exception instanceof CreateCustomerException)
   showMessage("error creating customer");

Another way is to have just one custom Exception which you will throw every time data acces exception occures. In that case you could have property "type" as instacne variable of your exception which will be used for recoognizig the type of the error.

You can have static final fields to map all possible exception types inside your Exception

class MyDAOException extends Exception
{
int errorType=0;

public static final int CREATECUSTOMERERROR =1;
}

theb ont top level you cah distinct error types lik this

MyDAOException ex;

if(ex.type==MyDAOException.CREATECUSTOMERERROR)
    showMessage("error creating customer");





gmartelliAuthor Commented:
CEHJ: Enabling internal logging might allow me to see there was an error and the DB specific code, but helps in no way to create a program that can diferenciate between a UK, PK or FK constraint violation without diving into DB specific issues.

Beegor:

>
>if(ex.type==MyDAOException.CREATECUSTOMERERROR)
>    showMessage("error creating customer");

Great explanation of exception handling from the java side, but my question was different. A "error creating customer" helps in no way to the user. I want to show him something  along the lines of "duplicated record" or "invalid value of Country for Customer" or "Cannot delete the customer that has pending payments" or "Check failed" or whatever. I needed a platform independent way of figuring that out using hibernate, as the constraints are already on the database (db independently).
Thanks for the comment about SQLCodes not working, it seems like , there's no way to figure it DB independently.
I wanted to avoid having to code db-specific functionality, but seems like I'll have no choice.
beegorCommented:
database will not give you such specific explaination...

my point was that you have to do it on hihher level...
db will return something like this "integrity constraint (WILLEVOLVE.M_SHELFOFMATTER) violated - child record found"
if you show this to user he will be totally confused, and if yiu try to parse it you will just lose much time without good result.

So, basically, the answer is that there is no easy way to do it like you said: " needed a platform independent way of figuring that out using hibernate"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gmartelliAuthor Commented:
Yup, I figured it out...I ended up creating a view on the DB schema (which I will have to develop for each different DB engine) that extracts constraint info from the data dictionary...That way I can extract info (type, columns, table, etc) and from the java code its still DB independent.

Still, I find a bit odd that hibernate handles a lot of the operations in a tech independent way, but it really doesn't do much about the errors.

Thanks, you get credit.
CEHJCommented:
>> database will not give you such specific explaination...

(which is why wrt to Hibernate i suggested enabling its own logging)
beegorCommented:
My advise is that you handle checks and data validation in your business and presentation layer. Persistance layer is last place and if there is error while you are updating or inserting something, that means that you didn't check enough.
If you think about it, message like this: "Cannot delete the customer that has pending payments" shold be result of checking for pending payments before you really try to delete it.


Thanx for credit!
gmartelliAuthor Commented:
Np on the credits.

About checking for pending payments: I dont want to do a select-before-insert, or select-before-delete, it has a lot of performance penalities. And, the point of having a relational database is that it can manage referencial integrity on itself, I'm not intending to recode the database in the business layer and use the relational database as a flat file :)
gmartelliAuthor Commented:
Cehj:
Enabling logging will do no good except for debugging, there's nothing I can do about checking a constrain in the logs unless I start to parse the debug log in runtime (EWWW!). It might help for diagnosing a problem, but really doesn't help at all on the constraint checking of the data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.