• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

jdbc question

 
jeff mutonho
Ranch Hand
Posts: 271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using JDBC , what would be a more elegant way of determining whether or not a record already exists in a DB table before inserting it?

Jeff
Registered Linux user number 366042
 
pascal betz
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi

it would be elegant if you would know before you connect to your db :-). E.g. in my DB related projects objects are considerd unsaved if the ID property is null. after a object was inserted the ID property is set and the object is only updated.

or you first need to check if it exists (select * from tablename where id = ?) and then decide for yourself.

i dont think this is related to JDBC, this i a "restriction" of SQL and the databases (if you try to insert a row twice you violate the unique constraint of your primary key)

pascal
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeff,
Often it turns into a tradeoff. If you think the record is likely to be there, use the select. If you think the record is unlikely to be there, insert and check for a duplicate key violation.

Pascal is correct that this isn't a limitation of JDBC. However this is the forum for SQL too, so you are in the right place!
 
Anandh Ramesh
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi

i'll give you a suggestion on this. you can use the query

select count(*) from <table> where <fields>=<values>

if this returns a positive number, don't insert. if this returns a zero, go ahead and insert the record.

i suggested this alternative because select * will return so many unnecessary values, which can go with the conditions. the count function is going to return only one integer that can be easily compared. this reduces the comparison overhead needed for the application.

happy databasing...
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, you don't need to fetch any data, from the database, in order to determine whether a row exists, or not.

However, I am assuming that you want to locate a single, particular row in a single database table (or view). In that case the WHERE clause of your (SQL) query needs to target the specific row. Here is an (untested and uncompiled [pseudo]) example:

Note that, in the SQL of the example, <key> means the primary key of the table.

By using "select null", we are saying that we don't wish to return any data from the table, we just want to verify whether there is a row in the table with the given primary key value.

Good Luck,
Avi.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic