Spring Data JPA: Join tables without parent entity

For a project I’m currently working on, we are using an external system for user authentication. Authorization however, is being handled by application specific database tables. To maintain the data of these authorization tables, we wanted to set up JPA entities and Spring Data JPA repositories. However, there was 1 issue. We weren’t able to set up the “principal” entity, because it wasn’t a database table.

In the above image, you can see the systems and entities that we have:

• An external authentication system, which contains all kind of principal/user data.
• Our application specific database. This contains all roles that can be granted to a principal. We also have a join table so we can make many-to-many relations between principals and roles.

The relation between the “principal_roles” and “role” table, can just be achieved with a foreign key. However, because the “principal” is not a database table, we can’t specify a foreign key relation here. Still, the “principal_name” column should refer to an existing principal in the external system.

The goal was to have support for (at least) the following CRUD operations:

• Create a “principal_roles”relation;
• Read all roles for a specific principal;
            a list of all principals with their connected roles;
• Update “principal_roles” relations;
• Delete “principal_roles” relations.

Also use the join table as entity

The first attempt was to use the “principal_roles” table both as entity and as join table within the entity. Below you can find an example of what the entity looked like.

package nl.mikeheeren.joinwithoutparententity.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import java.util.List;

@Entity
@Table(name = "principal_roles")
public class Principal {

    @Id
    @Column(name = "principal_name")
    private String principalName;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "principal_roles",
            joinColumns = {@JoinColumn(name = "principal_name")},
            inverseJoinColumns = {@JoinColumn(name = "role_id")}
    )
    private List<Role> roles;

    public String getPrincipalName() {
        return principalName;
    }

    public void setPrincipalName(String principalName) {
        this.principalName = principalName;
    }

    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

}

Even though some things were (partially) working, this was not a satisfying solution:

• Create a “principal_roles” relation
  Doesn’t work. JPA first tries to insert the parent entity (@Id), and afterwards it will insert the @ManyToMany items in (a) separate insert(s). However, because both the “principal_name” and “role_id” column are mandatory in the database, the first insert already fails.
• Read all roles for a specific principal
  Works if you use the findAllById(List.of(“******”)) operation on the repository.
  
  The findById(“******”) method only works if only a single role is connected to a principal. If multiple roles are connected to the principal, an exception will be thrown:

org.hibernate.HibernateException: More than one row with the given identifier was found: ******, for class: nl.mikeheeren.joinwithoutparententity.model.Principal

• Read a list of all principals with their connected roles
  Works, the only remark is that if a principal is connected to multiple roles, the principal will be returned duplicate by the default findAll() method. Even though this is not ideal, this could easily be resolved by adding a “SELECT DISTINCT” operation to the repository.
• Update “principal_roles” relations
  Works only if the “existing” (so a relation exists in the “principal_roles” table) principal is only connected to a single role. If the principal is connected to more than 1 role, it fails with the same exception as the findById(“******”) method.
• Delete “principal_roles” relations
  None of the delete operations in the repository work.

Using “join table” entities and customizing repository

 Now that we found out that the previous solution doesn’t satisfy all our needs, we decided to go for another approach. This time we just created a simple entity object for the “principal_roles” table. Besides this entity, we also created a simple Principal DTO. In the JpaRepository (which by default provides operations for the PrincipalRoles entity) we then specify a couple of additional methods, where we will bundle/transform PrincipalRoles into a Principal and vice versa.

We started by setting up the PrincipalRoles entity object. This will have 2 fields that represent the role relation. The first one is just the ID. This one is required because we need to use this field in the composite ID. The other one also actually fetches the role details from the database. We can’t combine these into a single field, because @Id can’t be applied in combination with the @ManyToOne annotation.

package nl.mikeheeren.joinwithoutparententity.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import java.io.Serializable;

@Entity
@Table(name = "principal_roles")
@IdClass(PrincipalRoles.PrimaryKey.class)
public class PrincipalRoles {

    @Id
    @Column(name = "principal_name")
    private String principalName;

    @Id
    @Column(name = "role_id")
    private int roleId;

    @ManyToOne
    @JoinColumn(name = "role_id", insertable = false, updatable = false)
    private Role role;

    public String getPrincipalName() {
        return principalName;
    }

    public void setPrincipalName(String principalName) {
        this.principalName = principalName;
    }

    public Role getRole() {
        return role;
    }

    public void setRole(Role role) {
        this.role = role;
        this.roleId = role == null ? 0 : role.getId();
    }

    static class PrimaryKey implements Serializable {

        private String principalName;
        private int roleId;

    }

}

Now that the entity object is in place, it’s time to see if we can satisfy all of our goals in the JpaRepository:

• Create / Update “principal_roles” relation(s)
  Because the JpaRepository is configured with PrincipalRoles as type, we already have a save(PrincipalRoles)method available. However, because this entity only represents a single relation between a principal and a role, we decided to add an overloading method that accepts the Principal DTO object. This object represents a principal with all of its role relations.
  
  This method will convert the Principal into a List of PrincipalRoles objects. These can easily be inserted using the saveAll method. However, before we insert the records into the database table, we remove the existing relations from the principal. We do this, so you are also able to remove roles when updating existing principals. Removing and (re)inserting the database entries will be done in a single transaction because of the @Transactional annotation:

@Transactional

default Principal save(Principal principal) {
    String principalName = principal.getPrincipalName();
    List<Role> roles = principal.getRoles();
    if (roles == null || roles.isEmpty()) {
        throw new DataIntegrityViolationException("A principal should always contain at least 1 role.");
    }
    List<PrincipalRoles> principalRoles = roles.stream()
            .map(role -> {
                var pr = new PrincipalRoles();
                pr.setPrincipalName(principalName);
                pr.setRole(role);
                return pr;
            })
            .collect(Collectors.toList());
    // Delete existing records
    deleteAllByPrincipalName(principalName);
    // (Re)insert roles
    List<PrincipalRoles> savedPrincipalRoles = saveAll(principalRoles);
    return toPrincipal(savedPrincipalRoles);
}

Finally, we convert the (successfully) stored PrincipalRoles List back into the Principal DTO using the below method:

private static Principal toPrincipal(List<PrincipalRoles> principalRoles) {
    String principalName = principalRoles.get(0).getPrincipalName();
    List<Role> roles = principalRoles.stream().map(PrincipalRoles::getRole).collect(Collectors.toList());
    return new Principal(principalName, roles);
}

• Read all roles for a specific principal
  For fetching a specific principal from the database, we use a JPQL query to fetch all PrincipalRoles for the given principal name. We do this case insensitive because there is not a real foreign key on the database table, therefor you don’t have the guarantee that the case is always an exact match.
  
  Besides this JPQL query, we also specify a custom method where we call this method, and map the results into the Principal DTO:

default Optional<Principal> findPrincipalById(String principalName) {
    List<PrincipalRoles> principalRoles = findAllByPrincipalName(principalName);
    if (principalRoles.isEmpty()) {
        return Optional.empty();
    }
    return Optional.of(toPrincipal(principalRoles));
}

@Query("SELECT principalRoles FROM PrincipalRoles principalRoles WHERE LOWER(principalRoles.principalName) = LOWER(:principalName)")
List<PrincipalRoles> findAllByPrincipalName(@Param("principalName") String principalName);

• Read a list of all principals with their connected roles
  For retrieving all principals, we use a similar approach. But now we rely on the default findAll() method. Afterwards, we use groupingBy collector to bundle all roles (case insensitive again) per principal name:

default List<Principal> findAllPrincipals() {
    Map<String, List<PrincipalRoles>> caseInsensitiveBundle = findAll().stream()
            .collect(Collectors.groupingBy(principalRoles -> principalRoles.getPrincipalName().toLowerCase(), Collectors.toList()));
    return caseInsensitiveBundle.values().stream()
            .map(PrincipalRepository::toPrincipal)
            .collect(Collectors.toList());
}

• Delete “principal_roles” relations
  Deleting of the principals can easily be achieved by another JPQL query:

@Modifying
@Transactional
@Query("DELETE FROM PrincipalRoles principalRoles WHERE LOWER(principalRoles.principalName) = LOWER(:principalName)")
void deleteAllByPrincipalName(@Param("principalName") String principalName);

Conclusion

We have tried 2 ways of creating a join table with a parent entity in Spring Data JPA. The first attempt was to use the join table both as the entity and the join table. Even though it didn’t satisfy all our needs, this way can probably still be used when you want to set up a read-only join table without a parent entity  in JPA.

However, when you also want to be able to create, update and delete these records, going for the approach where you specify the join table as entity only, and adding some custom code to the repository, seems like the way to go.

The full example code can be found in Bitbucket.