3

When im loading my initial tests data i retrieve integrity constraint violation exception. It only occurs, when im adding more then 2 records, using addShift method. Im using h2, in memory database.

Loading:

public void load() {
    shiftService.addShift(1, LocalDate.now(), ShiftType.DAY, 1);
    shiftService.addShift(2, LocalDate.now().plusDays(1), ShiftType.NIGHT, 1);
    shiftService.addShift(2, LocalDate.now().plusDays(5), ShiftType.NIGHT, 2);
}

Shift Service:

@Transactional
public void addShift(long userId, LocalDate shiftDate, ShiftType shiftType, long groupId) {
    Optional<User> user = userService.findOne(userId);
    if (user.isPresent()) {
        Optional<Group> group = groupService.getOne(groupId);
        if (group.isPresent()) {
            Shift shift = new Shift(shiftDate, shiftType, group.get());
            user.get().addShift(shift);
        }
    }
}

Caused by: 

org.h2.jdbc.JdbcSQLException: Referential integrity constraint 
violation: "FKG5R8KJRL3GG3EKESMHT77GO7L: PUBLIC.SHIFT FOREIGN KEY(ID) 
REFERENCES PUBLIC.GROUPS(ID) (3)"; SQL statement:
insert into shift (id, group_id, shift_date, shift_type) values (null, ?, ?, 
?) [23506-196]

Model:

Shift:

@Entity
public class Shift {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private LocalDate shiftDate;
    private ShiftType shiftType;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "user_shifts", joinColumns = @JoinColumn(name = "shift_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
    private Set<User> users = new HashSet<>();

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "group_id")
    private Group group;

Group:

@Entity
@Table(name = "groups") // Group is reserved name in SQL
public class Group {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    private String name;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "user_group", joinColumns = @JoinColumn(name = "group_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
    private Set<User> users = new HashSet<>();

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "moderator_group", joinColumns = @JoinColumn(name = "group_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
    private Set<User> moderators = new HashSet<>();

    @OneToMany(mappedBy = "id", cascade = CascadeType.ALL)
    private Set<Shift> shifts;

User:

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String email;
    private String password;
    private String firstName;
    private String lastName;
    private String phone;

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(
            name = "user_authority",
            joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "authority_name", referencedColumnName = "name")}
    )
    private Set<Authority> authorities = new HashSet<>();

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(
            name = "user_group",
            joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "group_id", referencedColumnName = "id")}
    )
    private Set<Group> usingGroups = new HashSet<>();

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(
            name = "moderator_group",
            joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "group_id", referencedColumnName = "id")}
    )
    private Set<Group> moderatingGroups = new HashSet<>();

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinTable(
            name = "user_shifts",
            joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "shift_id", referencedColumnName = "id")}
    )
    private Set<Shift> shifts = new HashSet<>();

Do you have any ideas why i got this exception?

Dawid Kunert
  • 183
  • 2
  • 11
  • A group with id=2 exists in the database? – lzagkaretos Dec 02 '17 at 08:58
  • Yes it exists, otherwise it wouldn't be saved (optional check in if). I test it also with adding only shifts to group 1. Result is the same. – Dawid Kunert Dec 02 '17 at 09:00
  • @DawidKunert i may be wrong, but i don't think you need `@JoinTable`/`@JoinTable` from both sides of the relation. – varren Dec 02 '17 at 09:16
  • @varren as far as I know, that is recommended solution, but I don't think that this is relevant there, because exception is saying that problem is with ManyToOne relation between Shift And Group – Dawid Kunert Dec 02 '17 at 09:36
  • @DawidKunert yeah, but i still would check it, had similar problems with completely unrelated exceptions from double sided Joins and btw https://stackoverflow.com/questions/7869450/hibernate-and-h2-referential-integrity-constraint-violation-for-onetomany-bidi Also take a look at https://docs.oracle.com/javaee/7/api/javax/persistence/JoinTable.html `It is applied to the owning side of an association.` – varren Dec 02 '17 at 09:46
  • @varren I checked it now. After leaving JoinTable only on one side, there is still the same exception – Dawid Kunert Dec 02 '17 at 09:57

1 Answers1

5

Your mapping in Group is wrong.

@OneToMany(mappedBy = "id", cascade = CascadeType.ALL)
private Set<Shift> shifts;

This should be rather:

@OneToMany(mappedBy = "group", cascade = CascadeType.ALL)
private Set<Shift> shifts;

Explanation:

Hibernate is adding a foreign key constraint between id in shift and id in group. You are probably adding 2 groups, and after you try to add more than 2 shifts, the constraint isn't fulfilled, because a group with id 3 doesn't exist.

Marcin Kunert
  • 5,596
  • 5
  • 26
  • 52