omickron omickron - 1 year ago 169
Java Question

How to get distinct entity fields using Spring Data JPA?

The model:

class User {String firstName, String lastName};
class Job {String name, List<User> users, List<User> checkers};

Job has name, users - that work at this job, and checkers - users that check the work results.

Here's the Spring Data repository:

interface JobDao extends PagingAndSortingRepository<Job>{
List<Job> findByUsers(Collection<User> users); //this return list of Jobs.
List<User> findCheckersByUsers(Collection<User> users); //I'd like to get list of users that check work for provided users. But it doesn't work this way.

Here's the plain SQL that I want to get:

SELECT DISTINCT checker FROM job WHERE job.user in (?1) -- ?1 - here are users provided

How to do this?

P. S.

  1. I've read about Projections, but looks it's not the same that I want. I don't want to create any additional interfaces, as I already have the required.

  2. Sure, I can use findByUsers method and then use Java stream API,;
    but it doesn't look pretty.

Answer Source

First, I would put this method in UserRepository, since it's used to find users. But anyway, you just need

@Query("select distinct checker from Job job
      + " join job.checkers checker"
      + " join job.users user
      + " where user in :checkedUsers")
List<User> findCheckersOf(@Param("checkedUsers") Set<User> users)