I am working on a Spring Boot project using Spring Data JPA and Hibernate mapping. I have the following doubt about how can I implement the following query.
I have an User entity class like this:
@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 Integer 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;
@ManyToOne(fetch = FetchType.EAGER)
@JsonProperty("subagent")
private User parent;
public User() {
super();
}
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;
}
}
The instances of this class represents users of my system. An user can have a single specific parent (the concept is similar to that of a referral: an user can bring another user in the system). This is handled by this ManyToOne recursive relationship:
@ManyToOne(fetch = FetchType.EAGER)
@JsonProperty("subagent")
private User parent;
Basically an user contains is parent (who bring him\her into the platform). It works fine. So retrieving an user I can easily retrieve the information of who is its parent (it is contained into the retrieved User object).
Now I need to implement the inverse behavior: I have to define a "query" that starting from a parent retrieve all its children.
The previous User entity class maps the following DB table:
The highlighter parent_id contains the FK that define this recursive relationship. So it contains the PK of another user that is the parent.
I have this UserRepository repository interface (it extents the JpaRepository interface)
public interface UsersRepository extends JpaRepository<User, Integer> {
User findByemail(String email);
List<User> findByUserTypes_TypeName(String typeName);
}
As you can see I am using a "query by method" style. Is it possiblem implement a behavior like this using "query by method" style? (in case also JPQL could be fine)
You can do this
List<User> findByParent_Id(Integer id);
Or you can do this
@Query("SELECT u FROM User u WHERE u.id = ?1")
List<User> getReferredUsers(Integer id);