My team recently had a requirement to build a persistence mechanism that had a unique constraint on a combination of columns. It needed to behave in an idempotent manner. If the same request was received to created an entity multiple times it would simply respond that the items existed. I spent a considerable amount of effort researching a solution and wound up with something immeasurably simpler than some of my early stabs at it. So in the hope of saving somebody else some time…

Background

The tech stack for the implementation is Spring Boot with Hibernate. We are using the ORM annotations and liquibase to handle the database migrations.

The database layer is PostgreSQL 11.5. PostgreSQL provides a feature in the INSERT command to decide how to react to CONFLICT events, such as in our case a (unique) CONSTRAINT conflict. The challenge was how to configure Spring Boot to include the extra command in the INSERT ON CONFLICT ON CONSTRAINT (unique_name_constraint) DO NOTHING.

I will use a BondMovie entity example to illustrate.

Here is the entity with the unique constraint.

@Entity
@Table(name = "bond_movies", schema = "public", uniqueConstraints={
  @UniqueConstraint(columnNames = {"title", "movieType", "releaseYear"})
})
public class BondMovie {
  private String title;
  private String movieType;
  private String releaseYear;
}

@Repository
public interface BondMovieRepository implements JPARepository <BondMovie, Long> {
}

@Service
public class BondMovieService {
  @Autowired
  private BondMovieRepository bondMovieRepository;
  public BondMovie create(BondMovie bondMovie) {
    bondMovieRepository.save(bondMovie);
  }
}

Some of the solutions I investigated

Extend the Repository implementation and use entityManager and a native query

This solution is based on the following article: https://www.baeldung.com/jpa-insert. One of the first issues I ran into with this solution is that we are using a generated sequence to retrieve the next record’s id. Once I broke the standard Hibernate persistence lifecycle this value was no longer populated for me. I would have had to find another method to do get the next id from the sequence. The same is true for the created date timestamp annotated with @CreationTimestamp.

Extend the Repository to include an implementation and create a new method

This method was based on a solution that I observed in another project from the same company. It involved creating an implementation of the Repository class with a custom persist method directly leveraging the jdbcTemplate class to perform the persist with a custom SQL statement. This had similar issues to the previously discussed solution.

Hook into Hibernate lifecycle with either an interceptor or event and directly modify the prepared statement

This was more an exploration than a complete solution. The plan was to find some way to hook into an event near the end of the process and directly modify the PreparedStatement to be sent to the database server to include the desired SQL code. I really did not want to pursue this solution.

https://www.baeldung.com/hibernate-interceptor

https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#events

So, what did I end up doing

In order to modify the SQL statement sent to the service we simply modified the Entity definition to the following:

...
@UniqueConstraint(...)
@SQLInsert("INSERT INTO ... VALUES (...) ON CONFLICT ON CONSTRAINT (unique_name_constraint) DO NOTHING")
public class BondMovie {
...

I am pleased I did not have to pursue the original solutions outlined above. Hopefully this might save somebody else equal grief.