Spring Data JPA Specification Using CriteriaBuilder with One-to-Many Relationship

Question

I have a User entity, a UserToApplication entity UserToApplication and an Application entity.

One User can have access to several Application . And one Application can be used by more than one User .

Here is the essence of User .

 @Entity @Table(name = "USER", schema = "UDB") public class User { private Long userId; private Collection<Application> applications; private String firstNm; private String lastNm; private String email; @SequenceGenerator(name = "generator", sequenceName = "UDB.USER_SEQ", initialValue = 1, allocationSize = 1) @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "generator") @Column(name = "USER_ID", unique = true, nullable = false) public Long getUserId() { return userId; } public void setUserId(Long userId) { this.userId = userId; } @OneToMany(mappedBy = "user", fetch = FetchType.LAZY) public Collection<Application> getApplications() { return applications; } public void setApplications(Collection<Application> applications) { this.applications = applications; } /* Other getters and setters omitted for brevity */ } 

Here is the essence of UserToApplication .

 @Entity @Table(name = "USER_TO_APPLICATION", schema = "UDB") public class Application { private Long userToApplicationId; private User user; private Application application; @SequenceGenerator(name = "generator", sequenceName = "UDB.USER_TO_APP_SEQ", initialValue = 0, allocationSize = 1) @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "generator") @Column(name = "USER_TO_APPLICATION_ID", unique = true, nullable = false) public Long getUserToApplicationId() { return userToApplicationId; } public void setUserToApplicationId(Long userToApplicationId) { this.userToApplicationId = userToApplicationId; } @ManyToOne @JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID", nullable = false) public User getUser() { return user; } public void setUser(User user) { this.user = user; } @ManyToOne @JoinColumn(name = "APPLICATION_ID", nullable = false) public Application getApplication() { return application; } } 

And here is the essence of Application .

 @Entity @Table(name = "APPLICATION", schema = "UDB") public class Application { private Long applicationId; private String name; private String code; /* Getters and setters omitted for brevity */ } 

I have the following Specification that I use to search for User by firstNm , lastNm and email .

 public class UserSpecification { public static Specification<User> findByFirstNmLastNmEmail(String firstNm, String lastNm, String email) { return new Specification<User>() { @Override public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) { final Predicate firstNmPredicate = null; final Predicate lastNmPredicate = null; final Predicate emailPredicate = null; if (!StringUtils.isEmpty(firstNm)) { firstNmPredicate = cb.like(cb.lower(root.get(User_.firstNm), firstNm)); } if (!StringUtils.isEmpty(lastNm)) { lastNmPredicate = cb.like(cb.lower(root.get(User_.lastNm), lastNm)); } if (!StringUtils.isEmpty(email)) { emailPredicate = cb.like(cb.lower(root.get(User_.email), email)); } return cb.and(firstNmPredicate, lastNmPredicate, emailPredicate); } }; } } 

And here is the User_ metamodel that I still have.

 @StaticMetamodel(User.class) public class User_ { public static volatile SingularAttribute<User, String> firstNm; public static volatile SingularAttribute<User, String> lastNm; public static volatile SingularAttribute<User, String> email; } 

Now I would also like to pass the list of application identifiers to Specification so that its method signature is:

 public static Specification<User> findByFirstNmLastNmEmailApp(String firstNm, String lastNm, String email, Collection<Long> appIds) 

So my question is, if I add @OneToMany mappings to the User_ metamodel for the Collection<Application> applications fields of my User object, then how would I refer to it in Specification ?

My current Specification will look like the following SQL query:

 select * from user u where lower(first_nm) like '%firstNm%' and lower(last_nm) like '%lastNm%' and lower(email) like '%email%'; 

And what I would like to achieve in the new Specification will be something like this:

 select * from user u join user_to_application uta on uta.user_id = u.user_id where lower(u.first_nm) like '%firstNm%' and lower(u.last_nm) like '%lastNm%' and lower(u.email) like '%email%' and uta.application_id in (appIds); 

Is it possible to make such a mapping in a metamodel, and how can I achieve this result in my Specification ?

Answer 1

I have found a solution. To map an attribute from one to many, in the metamodel I added the following:

 public static volatile CollectionAttribute<User, Application> applications; 

I also needed to add a metamodel for the Application object.

 @StaticMetamodel(Application.class) public class Application_ { public static volatile SingularAttribute<Application, Long> applicationId; } 

Then, in my Specification I could access applications for the user using the .join() method on the Root<User> instance. Here is the Predicate that I formed.

 final Predicate appPredicate = root.join(User_.applications).get(Application_.applicationId).in(appIds); 

In addition, it is worth noting that my Specification , as it is written in the question, will not work if any of the input values ​​is empty. The null Predicate passed to the .and() CriteriaBuilder method will result in a NullPointerException . So, I created an ArrayList type Predicate , and then added each Predicate to the list if the corresponding parameter was non-empty. Finally, I convert the ArrayList to an array to pass it to the .and() CriteriaBuilder functions. Here is the final Specification :

 public class UserSpecification { public static Specification<User> findByFirstNmLastNmEmailApp(String firstNm, String lastNm, String email, Collection<Long> appIds) { return new Specification<User>() { @Override public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) { final Collection<Predicate> predicates = new ArrayList<>(); if (!StringUtils.isEmpty(firstNm)) { final Predicate firstNmPredicate = cb.like(cb.lower(root.get(User_.firstNm), firstNm)); predicates.add(firstNmPredicate); } if (!StringUtils.isEmpty(lastNm)) { final Predicate lastNmPredicate = cb.like(cb.lower(root.get(User_.lastNm), lastNm)); predicates.add(lastNmPredicate); } if (!StringUtils.isEmpty(email)) { final Predicate emailPredicate = cb.like(cb.lower(root.get(User_.email), email)); predicates.add(emailPredicate); } if (!appIds.isEmpty()) { final Predicate appPredicate = root.join(User_.applications).get(Application_.applicationId).in(appIds); predicates.add(appPredicate); } return cb.and(predicates.toArray(new Predicate[predicates.size()])); } }; } }