I'm trying to figure out what the best way to report to the user that the object they are trying to save violates a unique column constraint on the table.

Right now, if I try to save a new object with a duplicate value, I get an SQLException wrapped in a hibernate.JDBCException.

The error message in the SQLException is: "Duplicate entry '12345' for key 2"

The error message in the Hibernate Exception is: "could not insert: [store.model.Product]"

There is no select-before-insert available in the <class> element and even if there was, I'm not sure what good it will do.

Is there something stored in the session on transaction that can be used as some type of error code to determine what type of database error occured (duplicate key/unique, string too long, number to big/small, etc).[/i]

You try to re-use the integrity constraints of your database for user input validation checks, right? In reality, you can't do that for several reasons:

1. Business constraints on data can be much more complex than you may be able to declare in SQL databases. You would need an additional layer "in front" of the database for validation anyway.

2. There is no way to distinquish error messages from SQL DBMSs, they are vendor dependent. Hibernate can actually not really help here.

3. A programming language is what you usually require for business constraint validation, plain SQL (or datatype, column and table constraints) may not be enough anyway.

For these reasons, it is common to implement user input validation in the presentation (or business) layer of an application. The constraints in the database are of course also important: They at least guarantee the integrity of the data in the database, using the "internal" semantics of the database, instead of the "external" semantics of the more complex business model.

This is of course not a perfect solution, but thats the way it is today with the current SQL databases.

So lets talk about this in the context of what I'm trying to do.

I have a Product object, it has an id (native long) and serial number (unique string).

I'll let hibernate/mysql generate the id, but I want a user to define the serial number for the product.

Even if I replaced the autogenerated key with the user defined one, I'm still stuck with the problem of have to check to see if the product exists before I insert it.

So are you saying that I need to manually check to see if this is a violation of the contraints before I try to save the object? Would the rule be that if any unique columns exist in a table (aside from autogenerated primary keys) a query must be made in order to avoid collisions?

I've done this before without hibernate and it usually involved matching a substring for "Duplicate entry" or whatever error message the DMBS spit out.

I just thought this might be a common problem for assiged keys that didn't require a custom solution.