How to use @ElementCollection for Set<Integer> mapping?

Question

I have 2 tables. report_id is PK. animals_in_area doesn't have a PK key.

I need to check is an animal_id in the animal_report by given report_id and report_date. It seems like a simple task, but can I somehow describe a field Set<Integer> adimalIds in the animal_report class, mapped to the requested area? I tried @ElementCollection + @CollectionTable, but it automatically maps report_id to area_id, because of @Id annotation.

@ElementCollection
@CollectionTable(name = "animals_in_area", joinColumns = @JoinColumn(name = "area_id"))
@Column(name = "animal_id")
private Set<Integer> adimalIds = new HashSet<>();

I want to use criteriaBuilder.isMember(animalIdRequest, adimalIds) in DTO.

What is the classic way? Join and get Set ?

               animal_report                                 animals_in_area
    ---------------------------------                      -------------------
    report_id | report_date | area_id                      area_id | animal_id  
    ---------------------------------                      -------------------
        1     |  01.01.2020 |  100                           100   |   1001
        2     |  01.01.2020 |  101                           100   |   1002
                                                             100   |   1003
                   .....                                     101   |   1002
                                                             101   |   1004

@Entity
@Table(name = "animal_report")
public class AnimalReport implements Serializable {

  @Id
  @Column(name = "report_id")
  private Long reportId;

  @Column(name = "report_date")
  private LocalDate reportDate;

  @Column(name = "area_id")
  private Integer areaId;
  ...
}

Answer 1

You should correct your mapping in the following way:

@Entity
@Table(name = "animal_report")
public class AnimalReport implements Serializable {

  @ElementCollection
  @CollectionTable(name = "animals_in_area", joinColumns = @JoinColumn(name = "area_id", referencedColumnName = "area_id"))
  @Column(name = "animal_id")
  private Set<Integer> animalIds = new HashSet<>();

  // ...
}

You have to use referencedColumnName because the animals_in_area.area_id column refered to not primary key column of the animal_report table.