Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Design Decision: Database User List  RSS feed

 
Alex Yip
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Everyone!

I'm a fairly recent college grad entering the industry. I am in need of an expert opinion.

The situation: You have a list of records in a database which contains primary keys. You need to add a new record to the database but you need to check if the record exists. A user list is an example of the situation. (You have a list of users and you need to check if the username exists before adding)

The question: What I have been doing so far is to query the database first before adding. Is this a good way of doing taking care of the situation? I mean there is a select query first before an insert statement.

The second way I was thinking of this is let a catch handle it. But I don't know how do I check what type of error the database returns from SQLException.

PreparedStatement st;
try {
st = dbConn.prepareStatement("INSERT INTO USERS (USERID, USERNAME) VALUES (1, 'asdf')");
st.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
}

Any ideas or advice?
 
Alex Yip
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, I hope I didn't confuse anyone.

Alittle more information. I'm using Oracle 10g as my database. I've tried getting the error code from SQLException and it returns "1".
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQLException has some useful methods called getErrorCode and getSQLState.

getErrorCode returns the vendor specific error code (int) for the exception. Probably if you looked it up it would say it is a key or index violation of some sort.

getSQLState returns a String which is the XOPEN error code essentially for the exception. This is supposedly a generic code that you could use for detecting errors independent of the actual database vendor. There is such a code for key violations so it might help you.

In theory using getSQLState is better but in practice due to lazy vendor and or driver implementations the getErrorCode may return more helpful information though you would be coding to your specific database at that point.

To answer your question of whether to do this has an exception handling or check first I would say it depends a bit. I kind of prefer the exception route myself because (a) it makes sure that the database is setup to not allow invalid data, (b) it uses less database resources then doing that SELECT each time and (c) if I changed my key or whatever on the database side of things I am not having to change my code as well.

But I am sure there are people with other opinions on this topic. Many will say that you should not use exceptions for logic flow and generally I would agree but with JDBC and SQL exceptions I think there is an exception (ha ha) to this rule.
 
Alex Yip
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your reply Maximilian! I completely understand the point about not using exceptions as part of logic flow. Somehow, using a SELECT statement first and then an INSERT or UPDATE (updating a record depending on whether or not it exists) does not seem as elegant.
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Alex Yip:
Somehow, using a SELECT statement first and then an INSERT or UPDATE (updating a record depending on whether or not it exists) does not seem as elegant.


And I would agree with you.
 
Mattias Arthursson
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Alex Yip:
Somehow, using a SELECT statement first and then an INSERT or UPDATE (updating a record depending on whether or not it exists) does not seem as elegant.

IMO you should in most situations think less about what's elegant and more about what's logical, i.e. easier to read, understand and maintain. 'Premature optimization is the root of all evil'. (D. Knuth)
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you're big into "coding by intention" and making the code say what you mean, you might write:

Is that better or worse than

Idonno.

We have exactly this dilemma in some code right now. I wish I knew whether we had duplicates 90% of the time or 1% of the time to help decide. Guess I only have to count duplicate keys to find out.

To get way geeky, we just measured various cursor stability options on the query and found huge differences. YMMV.
[ April 12, 2006: Message edited by: Stan James ]
 
Mattias Arthursson
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Stan James:
We have exactly this dilemma in some code right now. I wish I knew whether we had duplicates 90% of the time or 1% of the time to help decide. Guess I only have to count duplicate keys to find out.

The point is, it really doesn't matter. The additional query is very unlikely to have any significant performance impact in the big picture. And if it does, then the optimization can be made when the impact becomes a problem.

That said, both code snippets look quite all right to me (although I would lean towards the first). Looking at the SQL error code to determine the return value in insertNewRecord() however wouldn't strike me as very appealing (if you're not using Spring JDBC and get the SQLException translation done for you that is).
 
Scott Dunbar
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This type of contstruct (select and update or insert) requires some careful design and, I'd argue, is not worth it. What if between your select and insert something else inserted? Short of locking the table you can't be sure that that won't happen. If you lock the table then you are throwing scalability out the window.

So, since the insert may fail even with the test I'd vote for doing the insert and catching the exception. But even with that you need to be very careful in terms of how well this scales. If you're getting, say, more than 25% insert failures and you're doing 100's per second then I would advise some very different design ideas.
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
With another day to think on this, I recall why we can't try insert and catch the exception ... we generate new artificial pkeys for inserts so we would never collide with an existing record. We are forced by this key scheme to query and then insert or update. I guess this takes us back to the eternal meaningful business keys vs surrogate keys argument ... a firestorm I don't really want to unleash on the ranch.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!