CODEWITHSUNDEEP
javapostgresqlhibernatespring-bootspring-data
StillLearningToCode
StillLearningToCode

Reputation: 2461

Javax Unique Contraints behavior When persisting duplicate row

Suppose I have a table in a Postgresql db:

CREATE TABLE FOO (
   ID INT PRIMARY KEY      NOT NULL,
   NAME           CHAR(50) UNIQUE NOT NULL
);

which is created by hibernate in a Spring Boot application

and I want to insert the following:

INSERT INTO FOO (ID, NAME) VALUES (1, "BAR");
INSERT INTO FOO (ID, NAME) VALUES (2, "BAZ");
INSERT INTO FOO (ID, NAME) VALUES (3, "ZIP");

as seed data when the in the following class:

@Component
public class SeedDB implements CommandLineRunner {

  private final FooRepository fooRepository;

  @Autowired
  public SeedDB(FooRepository fooRepository) {
    this.fooRepository = fooRepository;
  }

  @Override
  public void run(String... strings) throws Exception {
    fooRepository.save(new Foo("bar"));
    fooRepository.save(new Foo("baz"));
    fooRepository.save(new Foo("zip"));
  }
}

all of this just to say, this works great as long as the db is empty. but if this is set to run every time the application spins up, then the unique constraint causes the following error:

Caused by: org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [ukjsk1parw92chjvhsj49tl7492]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
...
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "ukjsk1parw92chjvhsj49tl7492"
Detail: Key (name)=(bar) already exists.

which makes sense, I suppose. What I am trying to figure out is if there is a way, without wrapping every line in a if/else or try/catch block, to attempt the insert without regard to the outcome?

the problem is that I can't use the create-drop hibernate approach because there are other tables that cannot be lost in the schema.

Upvotes: 0

Views: 62

Answers (1)

Bhushan Uniyal
Bhushan Uniyal

Reputation: 5703

You can use here try catch block, but i think the in case of unique fields we should check every time in database (field exist or not) before persist our entity so it will return surety of it, And i think it is better way to do it, because in case of try catch if we will handle ConstraintViolationException(Implementation of JDBCException indicating that the requested DML operation resulted in a violation of a defined integrity constraint.), this excetion can occur in any case of DML operation resulted in a violation, So will better to make sure our filed is unique, we need to check it before persist, if there will exist entity we can show user friendly message for user or log.

in your case code will like :

if(fooRepository.countByFiledName("bar")==0) {
 fooRepository.save(new Foo("bar"));
}

Upvotes: 1

Related Questions