SQl error - Referential integrity constraint violation when persisting several entities at once

Question

Problem I'm trying to solve

I'm trying to model a @ManyToMany relation between a User and Role, such that a user can have n roles, and one role is referenced by several users. A role can be persisted even if it's not referenced by any user (detached), and a user with no roles is allowed too.

The same kind of relation must be built between Role and ResourcePermission.

To give you an idea about how each entity looks like:

• Both ResourcePermission and Role have a finite set of values. For example, if Patient happens to be a resource, then one resource permission could be "PATIENT:READ" or "PATIENT:WRITE", and the role DOCTOR has several of these permissions. I hope it's clear sofar how my data model looks like.

What I'm using

• Currently, I'm using spring-data-jpa version 2.4.2 to model my entities, and to create my CRUD repos. Except for base path and media type, I don't have any specific configuration (all is set to default).
• Hibernate is my persistence provider atm .
• Concerning my datasource, I'm using in-memory H2 for my development environment, and again no specific configuration there either.

How I'm solving it

Here's how my entities look like

User.java

@Table
@Entity
@Data
public class User implements Serializable {

    private static final long serialVersionUID = 1123146940559321847L;

    @Id
    @GeneratedValue(generator = "user-id-generator")
    @GenericGenerator(name = "user-id-generator",
            strategy = "....security.entity.UserIdGenerator",
            parameters = @Parameter(name = "prefix", value = "USER-")
    )
    @Column(unique = true, nullable = false)
    private String id;


    @Column
    private int age;
    @Column(unique = true, nullable = false)
    private String username;
    @Column(unique = false, nullable = false)
    private String password;

    @ManyToMany(
            fetch = FetchType.LAZY,
            cascade = CascadeType.MERGE
    )
    @JoinTable(
            name = "user_role",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private List<Role> roles = Collections.emptyList();

    public User withId(final String id) {
        this.id = id;
        return this;
    }

    public User withAge(final int age) {
        this.age = age;
        return this;
    }

    public User withUsername(final String username) {
        this.username = username;
        return this;
    }

    public User withPassword(final String password) {
        this.password = password;
        return this;
    }

    public User withRoles(final Role... roles) {
        return withRoles(Arrays.stream(roles).collect(Collectors.toList()));
    }

    public User withRoles(final List<Role> roles) {
        this.roles = roles;
        return this;
    }
}

Role.java

@Data
@NoArgsConstructor
@Table
@Entity
public class Role implements Serializable {

    private static final long serialVersionUID = 812344454009121807L;

    @Id
    private String roleName;

    @ManyToMany(
            fetch = FetchType.LAZY,
            cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.DETACH }
    )
    @JoinTable(
            name = "role_resource_permission",
            joinColumns = @JoinColumn(name = "role_id"),
            inverseJoinColumns = @JoinColumn(name = "resource_permission_id")
    )
    private Set<ResourcePermission> resourcePermissions = Collections.emptySet();

    @ManyToMany(
            mappedBy = "roles",
            fetch = FetchType.LAZY,
            cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.DETACH }
    )
    private List<User> users = Collections.emptyList();


    public Role(final String roleName) {
        setRoleName(roleName);
    }

    public void setRoleName(final String roleName) {
        final RoleType roleType = RoleType.of(roleName);
        this.roleName = roleType.getRoleName();
        final Set<ResourcePermission> resourcePermissions = roleType.getResourcePermissions().stream()
                .map(ResourcePermissionType::getPermissionName)
                .map(ResourcePermission::new)
                .collect(Collectors.toSet());
        setResourcePermissions(resourcePermissions);
    }

    public void setResourcePermissions(final Set<ResourcePermission> resourcePermissions) {
        if (this.resourcePermissions.isEmpty()) {
            this.resourcePermissions = resourcePermissions;
        }
    }

}

ResourcePermission.java

@NoArgsConstructor
@Data
@Table
@Entity
public class ResourcePermission implements Serializable {

    private static final long serialVersionUID = 883231454000721867L;

    @Id
    private String permissionName;

    public ResourcePermission(final String permissionName) {
        setPermissionName(permissionName);
    }

    @ManyToMany(
            mappedBy = "resourcePermissions",
            fetch = FetchType.LAZY,
            cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.DETACH }
    )
    private Set<Role> roles = Collections.emptySet();

    public void setPermissionName(String permissionName) {
        final ResourcePermissionType permissionType = ResourcePermissionType.of(permissionName);
        this.permissionName = permissionType.getPermissionName();
    }
}

RoleType.java

@AllArgsConstructor(access = AccessLevel.PRIVATE)
public enum RoleType {

    DOCTOR("DOCTOR", doctorsPermissions()),
    TECHNICIAN("TECHNICIAN", technicianPermission()),
    ADMIN("ADMIN", adminPermissions());

    @Getter
    private String roleName;
    @Getter
    private final List<ResourcePermissionType> resourcePermissions;

     public static RoleType of(final String roleName) {
        return Arrays.stream(values())
                .filter(roleType -> roleType.getRoleName().equals(roleName.toUpperCase()))
                .findFirst()
                .orElseThrow(IllegalArgumentException::new);
     }

    private static List<ResourcePermissionType> doctorsPermissions() {
        return Arrays.asList(
                ENCOUNTER_READ, ENCOUNTER_WRITE,
                PATIENT_READ, PATIENT_WRITE
        );
    }

    private static List<ResourcePermissionType> adminPermissions() {
        return Arrays.asList(
                ENCOUNTER_READ, ENCOUNTER_WRITE,
                BUILDING_UNIT_READ, BUILDING_UNIT_WRITE,
                ORG_UNIT_READ, ORG_UNIT_WRITE
        );
    }

    private static List<ResourcePermissionType> technicianPermission() {
        return Arrays.asList(
                ENCOUNTER_READ, ENCOUNTER_WRITE,
                BUILDING_UNIT_READ, BUILDING_UNIT_WRITE
        );
    }

}

ResourcePermissoinType.java

@AllArgsConstructor(access = AccessLevel.PRIVATE)
public enum ResourcePermissionType implements Serializable {

    PATIENT_READ("PATIENT:READ"), PATIENT_WRITE("PATIENT:WRITE"),
    ENCOUNTER_READ("ENCOUNTER:READ"), ENCOUNTER_WRITE("ENCOUNTER:WRITE"),
    BUILDING_UNIT_READ("BUILDING_UNIT:READ"), BUILDING_UNIT_WRITE("BUILDING_UNIT:WRITE"),
    ORG_UNIT_READ("ORG_UNIT:READ"), ORG_UNIT_WRITE("ORG_UNIT:WRITE");

    @Getter
    private String permissionName;

    public static ResourcePermissionType of(final String permissionName) {
        return Arrays.stream(values())
                .filter(v -> v.getPermissionName().equals((permissionName.toUpperCase())))
                .findFirst()
                .orElseThrow(IllegalArgumentException::new);
    }

}

Unfortunately, the javax persistence API does not accept enums as entities. I tried using @Embeddable and @IdClass too, but that didn't work out for me either. I was not able to generate the schema that I had in mind. On the other hand, the schema was successfully generated using this model.

At the moment, both the Role repository as well as the Resource Permission repository are not exported (@RepositoryRestResource(..., exported = false)), so in order for you to persist those two entities, you'd have to provide that data in User. Keep that in mind, because that's also a part of the discussion that I want to talk about.

Now let's examine this integration test for the UserCrudRepository that will attempt to add a new user after a successful authentication.

@TestMethodOrder(OrderAnnotation.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@AutoConfigureMockMvc
class UserCrudRepositoryApiITest {

private final List<User> testUsers = Arrays.asList(
                new User().withUsername("dummy_username_01").withPassword("dummy_password_01").withAge(35)
                        .withRoles(new Role("ADMIN")),
                new User().withUsername("dummy_username_02").withPassword("dummy_password_02").withAge(40)
                        .withRoles(new Role("DOCTOR")),
                new User().withUsername("dummy_username_03").withPassword("dummy_password_03").withAge(45)
        );
.
.
    @Order(1)
    @Test
    public void afterAuthenticationAddNewUser() throws Exception {
        final String generatedToken = login();
        // serialize the user
        final String requestJson = objectMapper.writeValueAsString(testUsers.get(0));
        final RequestBuilder request = MockMvcRequestBuilders.post(USER_CRUD_BASE_URL)
                .header(HttpHeaders.AUTHORIZATION, generatedToken)
                .contentType(MediaType.APPLICATION_JSON)
                .content(requestJson);
        final String serializedContent = mvc.perform(request)
                .andExpect(status().isCreated())
                .andReturn()
                .getResponse()
                .getContentAsString();
        final User storedUser = objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false)
                .readValue(serializedContent, User.class);

        assertThat(storedUser).isEqualTo(testUsers.get(0));
    }
.
.
}

In here, I'm getting a status code conflict 409, and not able to persist all entities at once.

Unfortunately, SO allows only 30000 character, so please navigate to this repo if you would like to take a look at the log.

My Questions

1. I couldn't for the life of me understand where that referential integrity constraint violation is occurring. Any idea?
2. Any suggestions on how to model these relations in a better way are welcome!
3. Another problem I'm having with JPA repos is that the only way to persist roles and resource permissions is by providing that data in the user's body. I would like those entities to be managed independently of the user (each with its own separate repository), so I tried exporting their repositories. However, the problem then is that you no longer can pass Role data in the body of a User, but rather A reference to that entity. Is there a way to get the best of both worlds.

I hope I made my problem clear, if not, I'd be happy to elaborate more.

Answer 1

I guess when a User is persisted, it also does the insert for the user_role table, but the role wasn't persisted yet. You could try to persist the Role first or use PERSIST cascading at the User#roles association.