I am working on a Spring Boot project using Spring Data JPA and Hibernate and I have the following problem.
I have a database like this:
The portal_user table will contain the user of an application. A user can have one or multipe type represented by the user_type typological table and by the portal_user_user_type MANY TO MANY relationship table.
For semplicity at the moment assume that an user have a single user type. following two possible user types:
- CLIENT: it is a client that use services of my application.
- SUB-AGENT: it is an user that bring CLIENT user into the platform. You can consider this use as a financial broker having its own client portfolio.
To handle this situation I have the subagent_to_client relation table between that is a "recursive" table. Basically it is a MANY TO MANY relationship table that link together a SUB-AGENT user with its CLIENT* users. This relation table define the SUB-AGENT user clients portfolio.
NOTE: it will be the application that ensure the data correctness (basically the business logic will allow only to set a clients list for a SUB-AGENT user but not to other user's types).
This is my entity class mapping my portal_user database table:
@Entity
@Table(name = "portal_user")
@Getter
@Setter
public class User implements Serializable {
private static final long serialVersionUID = 5062673109048808267L;
@Id
@Column(name = "id")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int id;
@Column(name = "first_name")
@NotNull(message = "{NotNull.User.firstName.Validation}")
private String firstName;
@Column(name = "middle_name")
private String middleName;
@Column(name = "surname")
@NotNull(message = "{NotNull.User.surname.Validation}")
private String surname;
@Column(name = "sex")
@NotNull(message = "{NotNull.User.sex.Validation}")
private char sex;
@Column(name = "birthdate")
@NotNull(message = "{NotNull.User.birthdate.Validation}")
private Date birthdate;
@Column(name = "tax_code")
@NotNull(message = "{NotNull.User.taxCode.Validation}")
private String taxCode;
@Column(name = "e_mail")
@NotNull(message = "{NotNull.User.email.Validation}")
private String email;
@Column(name = "pswd")
@NotNull(message = "{NotNull.User.pswd.Validation}")
private String pswd;
@Column(name = "contact_number")
@NotNull(message = "{NotNull.User.contactNumber.Validation}")
private String contactNumber;
@Temporal(TemporalType.DATE)
@Column(name = "created_at")
private Date createdAt;
@Column(name = "is_active")
private boolean is_active;
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "user", orphanRemoval = true)
@JsonManagedReference
private Set<Address> addressesList = new HashSet<>();
@ManyToMany(cascade = { CascadeType.MERGE })
@JoinTable(
name = "portal_user_user_type",
joinColumns = { @JoinColumn(name = "portal_user_id_fk") },
inverseJoinColumns = { @JoinColumn(name = "user_type_id_fk") }
)
Set<UserType> userTypes;
@ManyToMany(cascade = { CascadeType.MERGE })
@JoinTable(
name = "subagent_to_client",
joinColumns = { @JoinColumn(name = "fk_subagent_user_id") },
inverseJoinColumns = { @JoinColumn(name = "fk_client_user_id") }
)
Set<User> clientsListOfASubagent;
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(String firstName, String middleName, String surname, char sex, Date birthdate, String taxCode,
String email, String pswd, String contactNumber, Date createdAt, boolean is_active) {
super();
this.firstName = firstName;
this.middleName = middleName;
this.surname = surname;
this.sex = sex;
this.birthdate = birthdate;
this.taxCode = taxCode;
this.email = email;
this.pswd = pswd;
this.contactNumber = contactNumber;
this.createdAt = createdAt;
this.is_active = is_active;
}
}
As you can see in the previous entity class I put this field:
@ManyToMany(cascade = { CascadeType.MERGE })
@JoinTable(
name = "subagent_to_client",
joinColumns = { @JoinColumn(name = "fk_subagent_user_id") },
inverseJoinColumns = { @JoinColumn(name = "fk_client_user_id") }
)
Set<User> clientsListOfASubagent;
to handle the list of clients of a specific SUB-AGENT user.
I have this unit test method:
@Test
@Order(7)
@Transactional
public void retrieveSubagentUserAndCheckClientsListTest() {
User user = this.userRepository.findByemail("luca.bianchi@gmail.com");
assertNotNull(user, "Retrieved user is not null");
assertTrue(user.getEmail().equals("luca.bianchi@gmail.com"), "E-mail luca.bianchi@gmail.com");
assertTrue(user.getFirstName().equals("Luca"));
assertTrue(user.getSurname().equals("Bianchi"));
assertTrue(subAgentUser.getClientsListOfASubagent().size() == 2);
}
This method simply retrieve a **SUB-AGENT user and check that its clients list contain two clients. And it works perfectly fine...infact this list contains the two client of this SUB-AGENT user (set into my subagent_to_client database table).
Ok, now I need to implement a similar behavior for the CLIENT user, the precise behavior for a CLIENT user have to be: RETRIEVE THE SINGLE SUBAGENT OF THIS CLIENT USER (this information is in the same subagent_to_client DB table).
In a first time I thought that the previous portal_user_user_type field will contain this information also for the CLIENT user so I implemented this new test method:
/**
* Retrieve a specific "CLIENT" user by its eMail and check its single "SUB-AGENT" user
*/
@Test
@Order(8)
@Transactional
public void retrieveClientUserAndCheckSingleSubAgentTest() {
User clientUser = this.userRepository.findByemail("mauro.colombo@gmail.com");
assertNotNull(clientUser, "Retrieved user is not null");
assertTrue(clientUser.getEmail().equals("mauro.colombo@gmail.com"), "E-mail mauro.colombo@gmail.com");
assertTrue(clientUser.getFirstName().equals("Mauro"));
assertTrue(clientUser.getSurname().equals("Colombo"));
assertTrue(clientUser.getClientsListOfASubagent().size() == 1);
}
But in this case it is not working as I expected. This because my clientUser.getClientsListOfASubagent() retrieve an empty list (also if the information is into my subagent_to_client DB table. Infact this table contain this record:
id fk_subagent_user_id fk_client_user_id
------------------------------------------
..........................................
3 53 59
..........................................
..........................................
..........................................
representing the relationship between myCLIENT user (having e-mail: mauro.colombo@gmail.com) and the related SUB-AGENT user.
It seems that this field:
@ManyToMany(cascade = { CascadeType.MERGE })
@JoinTable(
name = "subagent_to_client",
joinColumns = { @JoinColumn(name = "fk_subagent_user_id") },
inverseJoinColumns = { @JoinColumn(name = "fk_client_user_id") }
)
Set<User> clientsListOfASubagent;
is correctly working in the direction RETRIEVE THE CLIENTS OF A SUBAGENT but not in the opposite direction needed by this use case **RETRIEVE THE LIST COMPOSED BY A SINGLE RECORD REPRESENTING THE SUB-AGENT OF A SPECIFIC CLIENT).
Why? What is wrong? What am I missing? How can I solve this issue? I was trying to add an additional field named subagentOfAClient inverting the joinColumns and the inverseJoinColumns but I don't know if it can be a correct solution.
