Check for unique key violation

Hey guys,


I am writing a program where I have mapped a generalization into two tables (actually there are more, but I will leave them out because the principle is the same): Person and Employee. Person stores the general information such as social number (unique) and Employee stores information specific to employees, such as username/password. Both tables have an ID. These two IDs will match, so that an employee with ID 7 in the Employee table will have its general person information in the Person table with ID 7 as well.

Before inserting a new employee, I want to check if a person with a given social number is already registered in the table. Given that the social number column is unique, it throws an SQLException in my face. I can catch that without a problem, but what I want is to display what happened to the user. I can't really do that by catching the SQLException because it's so general.

I figure I could go ahead and use the IF (NOT EXISTS (my_insert_query)), but that would still leave me without a message to display. I could go ahead and execute a select query before my insert where I check for a row with a matching social number, but it seems inefficient to me.

The code below is not exactly what I have, but I will just provide an example query:

Code:

---

PreparedStatement prep = myConnection.prepareStatement("INSERT INTO Person (id, firstName, lastName, socialNumber) VALUES (?, ?, ?, ?)");

prep.setInt(123);
prep.setString("first name");
prep.setString("last name");
prep.setString("social number"); // Not sure of the English formats for this :)

prep.executeUpdate(); // Couldn't get it to work with executeQuery()

---

How would I go about inserting values in a table if no row with a given social number exists - and displaying a message to the user if it does? From DBLayer to ControlLayer to GUI. I hope you guys understand my problem.

Thanks in advance!
Andy

I know it seems inefficient, but I think that checking with a select before trying to save is the best way to do this.

Yep.
And it's a single select, on an indexed table, and you're binding your parameters...so it'll be pretty quick.

You could make it a stored procedure that does both the check and the insert, returning some parameter to indicate whether the entry already exists. That would reduce traffic by a single call. Not convinced it would be terribly noticeable though.

Doesn't your driver give you a more specific ConstraintViolationException wrapping the SQLException for which you can then create an ApplicationException that has the correct message information and throws that if causeContains the ConstraintViolationException?

Selecting first does not appeal to me because most likely you will not need it (more clean inserts than those that fail) and you will need to handle the exception anyway even if you do a select first because guaranteeing that your synchronization is correct will add even more overhead to your application.

Hello again and thank you for your replies.

I came up with a solution to avoid the extra select query. I am not sure if it is a great one, but it works (see the bottom of this post)! :)

Quote:

---

Originally Posted by r035198x

Doesn't your driver give you a more specific ConstraintViolationException wrapping the SQLException for which you can then create an ApplicationException that has the correct message information and throws that if causeContains the ConstraintViolationException?

---

ODBC gave me something like the following (I do not have the code with me to check):

SQLException: Violation of UNIQUE KEY constraint............

Unfortunately, I am no exception hero, so I was not sure whether or not I could catch a more specific exception for this matter. As a result, I "cheated" a little bit:

Code:

---

try {
      // Execute my query here
}

catch (Exception e) {
      // I believe it was something similar to this (again, I don't have the code here)
      if (e.contains("UNIQUE KEY"))
              throw new AlreadyExistsException("This person already exists."); // Custom exception
}

---

I have a bonus question, though (aren't you guys just totally excited now?). I made use of PreparedStatements to prevent SQL injection, because I did some research and I read that is how you do it (I don't really feel like writing an escape method). Is it true that when you do myPreparedStatement.setString(i, foo), it does all the escaping work for me so I do not have to worry about SQL injection?

I also read that PreparedStatements are more efficient than regular statements. I can see why this is in regards to executing the same query multiple times, but is that the only reason? If this is the main reason, I would appreciate if someone would take a minute to explain how to best make use of this.

Thank you in advance!

Can I just say *eek*...I can't believe I wrote that.
:)

I was stuck on Upserts (hence the SP suggestion) and performance, while completely ignoring the whole data integrity aspect. Just goes to show, you can't trust anything anyone posts on the internet...:)

Anyway, re: PreparedStatements. You've covered SQL Injection, which is quite a biggie (and it does handle all the escaping for you).

They are more efficient than a Statement in most situations. This largely comes down to what the db does with bound queries (that is queries in which the parameters are variable). A db holds execution plans of queries it has received in the past, and creates a new execution each time a sees a new query string. Think of this execution pool as a hashmap with the query string as the key.

So, when you use a Statement with concatenated parameters you will get a new execution plan for every query that has a different set of parameters. With a PreparedStatement you will only have the one, which gets reused.

There are other gains on the db side, but it does vary depending on the db.

Oh yes, these days as well the prepared statement itself can be cached on the Java side of things, which helps on the speed front...

Cool, thank you Tolls. :)