File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Catching PK violations in JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Catching PK violations in JDBC" Watch "Catching PK violations in JDBC" New topic
Author

Catching PK violations in JDBC

Graeme Byers
Ranch Hand

Joined: Apr 16, 2004
Posts: 127
Catching PK violations in JDBC
====================
Oracle XE 10.2.0 , JDK 1.6

public class SQLIntegrityConstraintViolationException
The subclass of SQLException thrown when the SQLState class value is '23'.
This indicates that an integrity constraint (foreign key, primary key or unique key) has been violated.

The following code calls a PL/SQL stored procedure (SYSTEM.INSERTEMPPROC) to insert a single row.

When I intentionally insert a duplicate emp.empno (PK) , it displays :
Caught SQLException 1/23000
ORA-00001: unique constraint (SYSTEM.SYS_C006691) violated
ORA-06512: at "SYSTEM.INSERTEMPPROC", line 11 ORA-06512: at line 1


Why is not SQLIntegrityConstraintViolationException thrown ?
I note that SQLState is 23000 (not Sun's 23).

If I wanted code to handle a PK violation , I could search in getMessage() for a suitable string to identify it.
But that's data base specific (and many try to avoid such code).
So , what does everyone do ?

Thank you
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

In general, its better to avoid throwing exceptions all-together than to throw them and catch them as a regular practice. In other words, check the violation before doing the INSERT.


My Blog: Down Home Country Coding with Scott Selikoff
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

...though just to add unless you are pessimistically locking a table to perform an insert this approach can't work 100% of the time. For primary keys I prefer delegating to a trustable key generation pattern, e.g. a sequence. I agree otherwise - conditional logic based on exception is bad.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

The insert occurs in a PL/SQL procedure you call. If you cannot reliably prevent the exception from happening by prior checking, you could wrap the procedure in another one in PL/SQL and catch and resolve the error there. In PL/SQL, should be able to catch the ORA-00001 exception specifically (name of the exception should be dup_val_on_index). The wrapper procedure could then return an error code (remember you can have an OUT parameter in PL/SQL and therefore transfer additional error code to Java easily), so that you don't have to parse exceptions in Java code at all.
Graeme Byers
Ranch Hand

Joined: Apr 16, 2004
Posts: 127
1. The question is why is not java.sql.SQLIntegrityConstraintViolationException being raised. It's first in the list.
2. In real life most inserts will work - so why check first before inserting ? It's a waste of a call.
3. The idea that PKs should preferably be generated by sequences seems silly - a PK might be customerNumber + paymentDate or a String.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Maybe this is a stupid question, but what JDBC driver version you use? If you don't use version written for JDK 1.6, you can't get the exception you want, as it was introduced in 1.6.

I'm using ojdbc5.jar (with both JKD 1.5 and 1.6) in my project and therefore get the plain old SQLException, just as you do. SQLState is also "23000" in my test case.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

We're off topic a bit but here goes... BTW I wrote an article covering this subject in detail awhile back: Database Key Generation in Java Applications

Graeme Byers wrote:2. In real life most inserts will work - so why check first before inserting ? It's a waste of a call.


In real life, writes are extremely uncommon compared to reads, so the cost of checking before writing data is negligible. In fact, database caching may allow you to detect a conflict right away without the need to go to the database directly. Database cost is not an excuse for avoiding checking for duplicates prior to insert unless it is known ahead of time that duplicates are nearly impossible.

Graeme Byers wrote:3. The idea that PKs should preferably be generated by sequences seems silly - a PK might be customerNumber + paymentDate or a String.


Have you ever used an application-level sequence generator for primary key generation? It makes things worlds simpler since you can guarantee that you will never have a conflict during insert. If you do, its a bug in your application key generator. They are wonderful things when implemented correctly.

As for using 'natural keys' as the primary key, I'd advise against ever doing so. In your example, lets assume a developer used customerNumber+paymentDate. A few days later, a customer comes in and says "Opps! The payment date was incorrect!". Now you need to update the paymentDate not only in the table that created the record but since its a primary key, you need it update it in all referencing tables. That would be a data management nightmare. In general, I advocate against natural keys with rare exceptions.

That's not to say you can't have a "key" (as opposed to a "primary key" of which there is always exactly one) or uniqueness constraint on a column, but again, that's something you'd want to verify ahead of time. In this example, though, maybe a customer is in fact an organization, and has multiple people placing orders. Then it might make sense to have duplicate customerNumber+paymentDate's in a record. In short, there aren't many good situations where you want uniqueness constraints, e-mail being one of the few.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Paul Sturrock wrote:...though just to add unless you are pessimistically locking a table to perform an insert this approach can't work 100% of the time. For primary keys I prefer delegating to a trustable key generation pattern, e.g. a sequence. I agree otherwise - conditional logic based on exception is bad.


Thanks for finishing my sentences for me Paul! Correct, you can never rule out duplicates but checking ahead of time should rule out 99.99% of them (unless you have a lot of inserts/second!).
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

At least in Oracle, nothing can beat sequences as key generators. Clever usage of properly configured sequences, especially with the CURRVAL pseudocolumn (feature probably unknown in the Java world), is an ingenious way to populate scores of parent-child tables without the need to ever transfer the generated keys from the database (unless it is really needed). Set the CACHE parameter properly and your sequences will scale well even on RAC clusters. Done in ten seconds.

With application key generator, you need a lot of work (I'm inferring this much from the statement "They are wonderful things when implemented correctly.") to implement a feature that is already present in the database.

Oh yes, we lose the database independence with sequences. I personally see database independence as one of the worst paradigms perpetuating the Java world. Scores of Java programmers strive to use only the barest possible set of database operations, avoiding the use of precisely those features that were designed and implemented for the very task being solved, only to arrive at the inevitable conclusion that the database does not perform well, leading into yet more logic being implemented in Java (and not in the database, close to the data). Which is where the vicious cycle begins anew.

I've seen large business system, deployed worldwide, designed to be so database independent that its transactional logic was nonexistent. Unfortunately I've got to import data from this system as part of our current project. We were reduced to read and check every row's timestamp to verify that the data we read is internally self-consistent. Astonishing. I can only hope this just an exception, though a really horrible one.

If I had to design database independent architecture, I'd probably try to put all the dependent logic into stored procedures, and tried to have programmer knowledgeable of the target database implement them. Not a single ad-hoc SQL query in the Java code, just stored procedure calls. But that's just me
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Martin Vajsar wrote:With application key generator, you need a lot of work (I'm inferring this much from the statement "They are wonderful things when implemented correctly.") to implement a feature that is already present in the database.


The advantage is that you can do multiple inserts on a single trip to the database since you can create all ids ahead of time. For example, let's say you need to insert a user and 3 addresses (4 records total). By using an application-level key generator you can set the parent-child relationship on all 4 records before ever communicating with the database. If you rely on database sequences, you need to insert the user first then read the generated keys before being able to insert the addresses, requiring multiple trips [alternatively you could use a stored procedure]. BTW, the code to set this up is only 1-2 pages.

Martin Vajsar wrote:I personally see database independence as one of the worst paradigms perpetuating the Java world.


I wish I could show you all the places where I've seen this go wrong. It's not hard to write database independent JDBC code. More than anything it takes discipline on the part of the developer, and honestly most developers just aren't that clean with their code. That's not to say there aren't some excellent database features you can take advantage of [Oracle offers alot] but those instances should be few and far between and have well define impact points.

And because I hear this all the time, "Yes, it is possible to change an applications database in the real world". I've been hired to do it multiple times and its rarely 'fun', definitely not for the faint of heart.

Martin Vajsar wrote:If I had to design database independent architecture, I'd probably try to put all the dependent logic into stored procedures


I loath stored procedures and only use them when performance requires them. For example, processing a million records on the database side rather than transmitting them locally. Otherwise, writing a stored procedure means if anyone ever wants to transfer the code they will have to start over and rewrite it from scratch. This assumes, of course, they can fully understand the existing stored procedure which is often a challenge in and of itself. One of my biggest pet peeves is seeing stored procedures used for trivial things that could be done with a single SQL statement.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Scott Selikoff wrote:The advantage is that you can do multiple inserts on a single trip to the database since you can create all ids ahead of time. For example, let's say you need to insert a user and 3 addresses (4 records total). By using an application-level key generator you can set the parent-child relationship on all 4 records before ever communicating with the database. If you rely on database sequences, you need to insert the user first then read the generated keys before being able to insert the addresses, requiring multiple trips [alternatively you could use a stored procedure].

Actually, using the CURRVAL sequence pseudocolumn I mentioned you can do exactly the same in Oracle, even without the stored procedure:
You can send all this into the database in a single trip (admittedly using JDBC batching and not Oracle array insert, but that is a different story). You can create several users and their addresses in one go. You don't even have to touch the generated value if you don't need to; however if you do need it, you can use the returning clause. You can grab this code "as is" and put it into the stored procedure or wherever if need be. And the code needed to set this up is:
Definitely less than one or two pages - but, of course, not portable

As to the stored procedures I hinted: when properly documented, it should be straightforward to reimplement them on another system. Of course, if one had to infer the actual logic from existing, undocumented code, that would be something different, especially as it would have to be someone fluent in both database dialects.

The most pressing problem of database independence (as I see it) is that the developer codes against a database he probably does not know very well, if at all. Someone who develops his application on Oracle, where writes do not block reads and then ports it onto some other system, where writes do block reads can be pretty surprised even when all his JDBC code is formally "independent". The whole concept of the application can be ruined. I believe this is what happened to the system I've written about in my last post and that is why they stopped to use transactions whatsoever.

To prevent such nasty surprises, one would have to consider all existing constraints in all potential databases at once, reducing the database to a "persistence store" surrounded by a circle of mid-tier servers.

Scott Selikoff wrote:And because I hear this all the time, "Yes, it is possible to change an applications database in the real world". I've been hired to do it multiple times and its rarely 'fun', definitely not for the faint of heart.

I don't doubt it. Everything is possible with willing customer and flexible budget. We don't know "impossible" in the IT
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31054
    
162

Depends on how complex the stored procedure. While very little is "impossible", some things are prohibitively expensive.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Catching PK violations in JDBC