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.
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"));
}