Spring Data Specification with JPA subquery

Question

I would like to execute subquery from Spring Data Specification. It must be like this SQL statement:

select u from users u where u.name in (select up.username from users_profiles up where up.profile in ('admin'));

I have 3 simple tables:

1. users that stores users data
2. profiles that stores user profile types
3. users_profiles that sotres relation between user and multiple profiles

So, having list of profiles I need to find all users that have those profiles.

public List<User> getUsersByProfileType(List<String> profileTypes) {
    return userRepository.findAll(new Specification<User> () {
        @Override
        public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            final List<Predicate> predicates = new ArrayList<>();

            /*
            userNamesWithMatchedProfiles = select up.username from users_profiles up where up.profile in profileTypes
            */
            List<String> userNamesWithMatchedProfiles = new ArrayList<String>();
            predicates.add(root.get("name").in(userNamesWithMatchedProfiles))
            return cb.and(predicates.toArray(new Predicate[predicates.size()])); 
        }
    });
}

Answer 1

Use Hibernate query (native), on UserRepository as follows, you can also use custom parameters.

public UserRepository extends JpaRepository{

@Query(value = "select u from users u where u.name in (select up.username from users_profiles up where up.profile in ('admin'), native = true )
List<User> findAdminUser();

Answer 2

I found the way to execute subquery:

public List<User> getUsersByProfileType(List<String> profileTypes) {
        return userRepository.findAll(new Specification<User> () {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                final List<Predicate> predicates = new ArrayList<>();

                final Subquery<UserProfile> userProfileSubquery = query.subquery(UserProfile.class);
                final Root<UserProfile> userProfile = userProfileSubquery.from(UserProfile.class);

                // select up.username from users_profiles ...
                userProfileSubquery.select(userProfile.get("user").get("name"));
                // ... where up.profile in ('admin')
                userProfileSubquery.where(cb.trim(userProfile.get("profile").get("id")).in(profileTypes));

                // select u from users u where u.name in ...
                predicates.add(root.get("name").in(userProfileSubquery));

                return cb.and(predicates.toArray(new Predicate[predicates.size()])); 
            }
        });
   }

Answer 3

You should make use of the Java Persistence Query Language (JPQL).

In summary, specify a @NamedQuery to perform your database query...

@Entity
@NamedQuery(name="User.findAdmins", query="SELECT u FROM User u WHERE u.name IN (SELECT up.username FROM UserProfile up WHERE up.profile IN ('admin')") 
public class User extends Serializable... 

... And then execute it within your code:

List<String> userNamesWithMatchedProfiles = 
                    entityManager.createNamedQuery("User.findAdmins").getResultList();

The above should provide roughly what you need. You will notice that the syntax is SQL-like, with only minor differences. Translating SQL into JPQL is very simple.

You could, of course, execute this as a Query in it's own right with no need to store it as a NamedQuery. I've done that here because (in my humble opinion) it's a neat pattern that helps keep our JPA logic organised.

Note also that you can inject parameters into the query. For example:

@NamedQuery(name="User.findUser", query="SELECT u FROM User u WHERE u.name = :name") 
...
List results = entityManager
                  .createNamedQuery("Profile.findUser")
                  .setParameter("name", "Daniel")
                  .getResultList();