Check whether exist the database object with the specified field values

Question

I am new in Spring Data, and I need to establish the impossibility of creating a new entity in DB if an entity already exists with the same field values.

Comparison condition: if "closeType" field and "id" agreement field of a new entity equal to database entity fields, I can't add this entity to DB. How do it?

My entity:

@Entity
@Table(name = "contract")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Contract implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "contractGenerator")
    @SequenceGenerator(name = "contractGenerator", sequenceName = "contract_sequence")
    private Long id;

    @Column(name = "start_date")
    private LocalDate startDate;

    @Column(name = "end_date")
    private LocalDate endDate;

    @Column(name = "first_pay_date")
    private LocalDate firstPayDate;

    @Column(name = "next_pay_date")
    private LocalDate nextPayDate;

    //Here is thу first field for comparison   
    @Enumerated(EnumType.STRING)
    @Column(name = "close_type")
    private CloseType closeType;

    @ManyToOne
    @JsonIgnoreProperties("")
    private Mentor mentor;

    //Here is second ID agreement field for comparison
    @ManyToOne
    @JsonIgnoreProperties("")
    private Agreement agreement;
     ...............
   //getters and setters

I have to block possibility to create several active contracts("closeType") in one agreement ("id")

you could use UniqueConstraint https://stackoverflow.com/questions/2772470/how-to-introduce-multi-column-constraint-with-jpa-annotations — Mehran Mastcheshmi, Oct 17 '18 at 20:28
Thanks, maybe you could hint me how to set constraint for "closeType" if, for example, only closeType fields with Null value will be uniqe? But other values of closeType wont be uniqe. — lutsik, Oct 18 '18 at 00:12

Mehran Mastcheshmi · Accepted Answer · 2018-10-19T17:21:23.877

I have to block possibility to create several active contracts("closeType") in one agreement ("id")

you could use UniqueConstraint How to introduce multi-column constraint with JPA annotations?

...
@Table(uniqueConstraints={
    @UniqueConstraint(columnNames = {"close_type", "agreement"})
}) 
Contract implements Serializable  {
    ...
}

Thanks, maybe you could hint me how to set constraint for "closeType" if, for example, only closeType fields with Null value will be uniqe? But other values of closeType wont be uniqe

How to annotate unique constraint with WHERE clause in JPA says:

creating partial indexes (CREATE INDEX ... ON ... WHERE) using JPA aren't specified by JPA. You cannot use a unique constraint for this purpose because unique partial indexes are not unique constraints. Some JPA providers offer extension annotations specific to that JPA provider that add features for running native DDL scripts, defining indexes with annoations, etc. Since you haven't mentioned which JPA provider you are using I can't tell you more. Here's the documentation for EclipseLink index DDL;

I suggest you to have a look at the How to annotate unique constraint with WHERE clause in JPA

Check whether exist the database object with the specified field values

1 Answers1