The problem with all your three approaches is the severe code duplication it would lead to. The first one appears particularly scary to me:
When you're inserting a product, the DAL checks if the product name already exists. If yes, throw an exception. Then it checks if SKU exists. Yes? Throw an exception. What about EAN-13? Throw an exception!
Now you're inserting a customer. Same story: multiple checks, which eventually result in an exception.
What about a product review? Same thing here.
The second and third approaches are slightly better, since you don't need to check manually for every UNIQUE field. Just do the query, catch the exception of the underlying third-party library, and throw your own exception instead. Still, you'll duplicate this logic in a method which inserts a product, and the one which creates a customer, and the one which adds a product review. Not good.
Another issue is that by catching and throwing your own exception instead of catching-and-rethrowing (or simply letting the exception to go up the stack) is that you're breaking the stack. It creates debugging nightmares.¹
In general, you catch exceptions when you can handle them. For instance, you catch an exception which tells that the file doesn't exist in a method which is able to decide whether it should substitute the contents of the file by default contents, or go search for contents somewhere else—such as a backup location—or ask user for help. So in which layer should we catch exceptions related to UNIQUE constraint violation? Probably in UI layer: for some reason (for instance, performance) the user interface was unable to prevent the user from typing a duplicate information in the first place, submitted the input downstream, and caused the constraint violation. It's also the UI which can actually handle the exception, for example by telling the user that the entered value is already in use.
The problem with this approach is that different database drivers could theoretically throw different exceptions. You would have to implement it for each implementation of the database interface. [...] I support 5 different types of databases through java odbc.
This is not a problem.
UI catches the exception which comes right from the third-party library and so looks more like
java.sql.SQLIntegrityConstraintViolationException rather than
myapp.UniqueFieldExcepton. But UI doesn't know how to parse the message within the exception in order to extract the name of the concerned field. The DAL knows it.
So UI does a call to BL, passing the exception message and asking which field is concerned. BL propagates the request down the DAL. The DAL parses the message and returns the field ID. Now, UI can highlight the corresponding field and show a meaningful error.
¹ I'm not even talking figuratively here. I still remember how painful it was to work with legacy code I had to support six years ago.
One of the WTFs was that developers loved catching exceptions in places where they couldn't possibly handle them anyway. So sometimes, they were rethrowing their own. Sometimes, they were throwing the same exception,
but doing it wrongly and losing the stack trace. And sometimes, they would just swallow those exceptions and pretend nothing wrong happened, reinventing On Error Resume Next. A SELECT query failed? That's OK, we'll still loop on the sequence it expected to return to us. What do you mean by “NullPointerException”?