Java – conditional insertion using spring JPA / Hibernate

I am developing a project running in a cluster environment with many nodes and a database The project uses spring data JPA (1.9.0) and Hibernate (5.0.1) I can't solve the problem of how to prevent duplicate lines

For example, this is a simple table

@Entity
@Table(name = "scheduled_updates")
public class ScheduledUpdateData {
    public enum UpdateType {
        TYPE_A,TYPE_B
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private UUID id;

    @Column(name = "type",nullable = false)
    @Enumerated(EnumType.STRING)
    private UpdateType type;

    @Column(name = "source",nullable = false)
    private UUID source;
}

It is important to have a unique (type, source) constraint

Of course, match the sample Repository:

@Repository
public class ScheduledUpdateRepository implements JpaRepository<ScheduledUpdateData,UUID> {
    ScheduledUpdateData findOneByTypeAndSource(final UpdateType type,final UUID source);

    //...
}

The idea of this example is that some parts of the system can insert rows to schedule regularly running events any number of times between the runs No matter what actually runs, it doesn't have to worry about operating the same thing twice

How do I write a service method that conditionally inserts this table? Some of the things I tried that didn't work were:

>Find > act – the service method uses the repository to see if an entry already exists, and then updates the found entry or saves a new entry as needed It doesn't work. > Attempt to Insert > update if failed – the service method will attempt to insert, catch exceptions due to unique constraints, and then perform the update This does not work because the transaction is already in the rollback state and no further operations can be performed in it. > Native query using "insert into... Where not exists..." * – the repository has a new Native Query:

@Repository
public class ScheduledUpdateRepository implements JpaRepository<ScheduledUpdateData,UUID> {
    // ...

    @Modifying
    @Query(nativeQuery = true,value = "INSERT INTO scheduled_updates (type,source)" +
                                       " SELECT :type,:src" +
                                       " WHERE NOT EXISTS (SELECT * FROM scheduled_updates WHERE type = :type AND source = :src)")
    void insertUniquely(@Param("type") final String type,@Param("src") final UUID source);
}

Unfortunately, this also doesn't work, because hibernate seems to execute the select used by the where clause first - which means that multiple inserts will eventually be attempted, resulting in a unique constraint violation

I absolutely don't know many details of JTA, JPA or hibernate Any suggestions on how to insert tables with unique constraints (beyond the primary key) across multiple JVMs?

Edit 2016-02-02

Using Postgres (2.3) as the database, try to use the isolation level serializable – unfortunately, this still leads to constraint violation exceptions

Solution

Hibernate and its query language provide support for insert statements Therefore, you can actually write the query using HQL Browse here for more information http://docs.jboss.org/hibernate/orm/5.0/userguide/html_single/Hibernate_User_Guide.html#_hql_syntax_for_insert