Win a copy of Spark in Action this week in the Open Source Projects forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

[Postgresql-JDBC]Verify if a table yet contains a value before an insert avoiding concurrent issues

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I have a java application connected to postgresql using JDBC.

I'm creating a simple method to insert a row (a new user) into a table (the table USER), it should work like this:  
If the table USER yet contains an user with the id of the user that is being inserted then a message like "Impossible to proceed user id already taken" should be shown, otherwise the new user should be inserted.
What is the best practice to do it?

Option 1:
I can simply use one INSERT, if an user with the given id already exists in the table an exception is thrown as the unique constraint is not respected and I can manage this exception to show the message dialog.
The problem here is that the catch could become difficult to manage, I have to be sure that the exception caught is exactly the one that occur when the unique constraint is not satisfied on that specific column.

Option 2:
I can do a SELECT for the user with the given id, if the select returns a row so the user already exists and I can show the error dialog, otherwise I can proceed with the insert.
The problem here is that if somone else do the same insert between my SELECT and my INSERT will fail throwing an exception

Option 3:
I can lock the USER table, do the SELECT as for option 2, INSERT the new user, unlock the table
But it is really needed to lock the entire table just to check if the user exists before the INSERT?

So what do you suggest me?
What is the strategy normally adopted in this very common case?

I guess if there is some special kind of lock that prevent an insert of a row if this contains a given id

Thanks in advance, best regards
 
Rancher
Posts: 4603
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Option 1 is the standard way of doing it.
You will get a SQLIntegrityConstraintViolationException (or should) if the id already exists, so you can handle that exception differently than other ones.
 
Goshfil Thomausen
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Option 1 is the standard way of doing it.
You will get a SQLIntegrityConstraintViolationException (or should) if the id already exists, so you can handle that exception differently than other ones.



Ok Dave, I will proceed to this way so, thanks a lot for your quick answer.
 
when your children are suffering from your punishment, tell your them it will help them write good poetry when they are older. Like this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic