CODEWITHSUNDEEP
javamysqlspringhibernatepostgresql
Michael
Michael

Reputation: 321

Proper way to insert record with unique attribute

I am using spring, hibernate and postgreSQL.

Let's say I have a table looking like this:

CREATE TABLE test
(
  id integer NOT NULL
  name character(10)
  CONSTRAINT test_unique UNIQUE (id)
)

So always when I am inserting record the attribute id should be unique

I would like to know what is better way to insert new record (in my spring java app):

1) Check if record with given id exists and if it doesn't insert record, something like this:

if(testDao.find(id) == null) {
    Test test =  new Test(Integer id, String name);
    testeDao.create(test);
}

2) Call straight create method and wait if it will throw DataAccessException...

Test test =  new Test(Integer id, String name);
try{
    testeDao.create(test);
}
catch(DataAccessException e){
    System.out.println("Error inserting record");
}

I consider the 1st way appropriate but it means more processing for DB. What is your opinion?

Thank you in advance for any advice.

Upvotes: 0

Views: 64

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324455

Option (2) is subject to a race condition, where a concurrent session could create the record between checking for it and inserting it. This window is longer than you might expect because the record might be already inserted by another transaction, but not yet committed.

Option (1) is better, but will result in a lot of noise in the PostgreSQL error logs.

The best way is to use PostgreSQL 9.5's INSERT ... ON CONFLICT ... support to do a reliable, race-condition-free insert-if-not-exists operation.

On older versions you can use a loop in plpgsql.

Both those options require use of native queries, of course.

Upvotes: 2

Michel Feldheim
Michel Feldheim

Reputation: 18250

Depends on the source of your ID. If you generate it yourself you can assert uniqueness and rely on catching an exception, e.g. http://docs.oracle.com/javase/1.5.0/docs/api/java/util/UUID.html

Another way would be to let Postgres generate the ID using the SERIAL data type

http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL

If you have to take over from an untrusted source, do the prior check.

Upvotes: 2

Related Questions