spring-data-jpa 3-way-intersection table

Question

I'll try to illustrate what I'm trying to achieve shortly... Let's suppose I have a users table:

USER_INFO
  USER_ID [PK]
  USER_NAME
  PASSWORD

an intersection table to define connections for each user (N:M - ManyToMany)

CONNECTION_INFO
  CONNECTION_ID [PK]
  USER_A_ID [FK - references USER_INFO(USER_ID)]
  USER_B_ID [FK - references USER_INFO(USER_ID)]
  CONNECTION_TYPE_ID [FK - references CONNECTION_TYPE(CONNECTION_TYPE_ID)]

The CONNECTION_TYPE is simple as:

CONNECTION_TYPE
  CONNECTION_TYPE_ID [PK]
  CONNECTION_TYPE_NAME [CHECK allowed values are: FRIEND, FAMILY, ...]

On Spring side I defined my User entity as:

@Entity
@Table(name = "USER_INFO")
public class User implements Serializable {
  @Id
  @NotNull
  @Column(name = "USER_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer userId;

  @Column(name = "USER_NAME)
  private String userName;

  @Column(name = "PASSWORD)
  private char[] password;

  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "CONNECTION_INFO",
             joinColumns = { @JoinColumn(name = "USER_A_ID") },
             inverseJoinColumns = { @JoinColumn(name = "USER_B_ID") })
  private List<User> connections;

  // ctor, getters, setters, toString, ...
}

I have a UserRepository interface that extends JpaRepository etc etc. Now, this works perfectly and I can retrieve all connections be it FRIEND, FAMILY, MOST_HATED_PERSONS, BLOCKED, DEMON, etc...

I tried to integrate the ConnectionType too in the picture however...

@Entity
@Table(name = "CONNECTION_TYPE")
public class Connection implements Serializable {
  public static enum Types {
    FRIEND, FAMILY, BLOCKED, ...
  }

  @Id
  @NotNull
  @Column(name = "CONNECTION_TYPE_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer connectionTypeId;

  @Column(name = "CONNECTION_TYPE_NAME")
  private ConnectionType connectionType;

  // ctor, getters, setter, etc
}

Now, my question is, how can I get only specific connections for a given user, based on Connection.Types? For example I want to find only FRIENDs, or only FAMILY I think you get my point. This 3 way intersection table gives me one of a headache.

@Clarification: What I want is a @ManyToMany relation defined on my User entity that happen to have extra column. I know in that case there are proposed solutions like LINK. In my case this extra column is a foreign key to a third table (USER_INFO(Holds the users), CONNECTION_INFO(Holds the connections between users N:M + an info on the type of connection), CONNECTION_TYPE. If I can model it with spring-data-jpa from what I understand I only need a magic named method under UserRepository, something like (totally incorrect):

public interface UserRepository extends JpaRepository<User, Integer> {
    List<User> findUserFriendsByConnectionType(User userWhoseFriendsWeAreSearching, String connectionTypeFromTheThirdTable);
}

That's all I want. I know it's simple with a normal extra column by creating an entity for the intersection table too and break the ManyToMany to OneToMany and ManyToOne, it just happens I have a third table and a possibly ManyToOne (1 connection can have 1 associated type, while a type can be linked to any number of connections) on the intersection entity with the connection_type table.

I hope it clears everything up. The above are just a sample I never imagined we'd hang up on an enum because I wanted to make it look simple I possibly made it way too simple perhaps :).

Answer 1

I managed to solve the problem but I'm not sure if this is the right way to do it. Anyway here's my solution. Consider the following 3 tables:

create table USER_INFO (
  USER_ID int not null primary key,
  USER_NAME varchar(16),
  PASSWORD varchar(64)
);

create table CONNECTION_TYPE (
  CONNECTION_TYPE_ID int not null primary key,
  CONNECTION_TYPE_NAME varchar(16) not null,
  CONNECTION_TYPE_DESCRIPTION varchar(128),
  unique (CONNECTION_TYPE_NAME)
);

create table CONNECTION (
  CONNECTION_ID int not null primary key,
  CONNECTION_TYPE_ID int,
  RELATED_USER_ID int,
  RELATING_USER_ID int,
  foreign key (CONNECTION_TYPE_ID) references CONNECTION_TYPE(CONNECTION_TYPE_ID),
  foreign key (RELATED_USER_ID) references USER_INFO(USER_ID),
  foreign key (RELATING_USER_ID) references USER_INFO(USER_ID)

With the above 3 tables, I want to provide a functionality to get connections for any given user based on the connection's type. For this I created 3 entities as follows:

@Entity
@Table(name = "CONNECTION_TYPE")
public class ConnectionType implements Serializable {
  @Id
  @NotNull
  @Column(name = "CONNECTION_TYPE_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer connectionTypeId;

  @NotNull
  @Column(name = "CONNECTION_TYPE_NAME", unique = true)
  private String connectionTypeName;

  @Column(name = "CONNECTION_TYPE_DESCRIPTION")
  private String connectionTypeDescription;

  ...
}

Nothing particularly interesting in here, I omitted the constructor, getters, setters etc and from the ConnectionType I don't want to have a mapping for all connections for this type so that direction is not present.

@Entity
@Table(name = "CONNECTION")
public class Connection implements Serializable {
  @Id
  @NotNull
  @Column(name = "CONNECTION_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer connectionId;

  @NotNull
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "CONNECTION_TYPE_ID", referencedColumnName = "CONNECTION_TYPE_ID")
  private ConnectionType connectionType;

  @NotNull
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "RELATED_USER_ID", referencedColumnName = "USER_ID")
  private User relatedUser;

  @NotNull
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "RELATING_USER_ID", referencedColumnName = "USER_ID")
  private User relatingUser;

  ...
}

This one is more interesting if for noone else at least for me. This would be my intersection table entity. There's the uni-directional mapping for the used ConnectionType with ManyToOne as one Connection can have exactly one ConnectionType while the same ConnectionType can be reused for an arbitrary number of Connections. The other 2 User mappings I'm sure I've messed up but before that here's the User entity:

@Entity
@Table(name = "USER_INFO")
public class User implements Serializable {
  @Id
  @NotNull
  @Column(name = "USER_ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer userId;

  @NotNull
  @Column(name = "USER_NAME")
  private String userName;

  @NotNull
  @Column(name = "PASSWORD")
  private char[] password;

  @OneToMany(fetch = FetchType.LAZY, mappedBy = "relatedUser", cascade = CascadeType.ALL, orphanRemoval = true)
  private List<Connection> connections;
}

Now here I'm even more sure I completely messed up, but I'll show the actual error. My repository is simple as a brick:

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
}

And I have a UserService with a simplified findAllConnectionsForUserById function:

@Service
public interface UserService {
    List<User> findAllConnectionsForUserById(Integer userId);
}

The method implementation is simple enough:

@Override
@Transactional
public List<User> findAllConnectionsForUserById(Integer userId) {
    Optional<User> _user = userRepository.findById(userId);
    // omitted exception handling...
    User user = _user.get();
    List<Connection> connections = user.getConnections();

    return connections.strea.map(Connection::getRelatingUser).collect(Collectors.toList());

This way it seem to work fine for the simple case and in case I take the ConnectionType too:

connections.stream().filter(c -> c.getConnectionType().getConnectionTypeName().equals("FRIEND")).map(Connection::getRelatingUser).collect(Collectors.toList());

it seem to work as well. Again, not sure if this is the right way but at least it does the job.