Constructing a JPA Specification for many-to-many relationship-CodePudding

I've tried to follow every JPA Specification example that I can find related to many-to-many relationships, but I just can't get my head around how to adapt them to my specific use-case.

I have entities defined like so...

@Entity
data class Employee(

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long? = null,

    val fullName: String = "",

    @ManyToMany
    @JoinTable(
        name = "employee_team",
        joinColumns = [JoinColumn(name = "employee_id", referencedColumnName = "id")],
        inverseJoinColumns = [JoinColumn(name = "team_id", referencedColumnName = "id")]
    )
    var teams: List<Team> = listOf(),

    ...
)

@Entity
data class Team(

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long? = null,

    val name : String = "",

    ...
)

An employee can be in many teams, and a team can obviously include many employees.

I have a JPA Repository defined like so...

interface EmployeeRepository : JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {

    ...

}

I'm creating a search form which will allow people to search for employees from 1 or more teams.

The search function looks like this...

fun searchEmployees(
        @RequestParam("keyword") keyword: String,
        @RequestParam("teamIdList") teamIdList: List<Long>?,
        ...
    ) {

    val pageable = PageRequest.of(0, 10)
    
    var spec: Specification<Employee> = Specification.where(null)

    if (keyword != "") {
        spec = spec.and(Employee::fullName.like("%$keyword%"))
    }
    if (teamIdList != null) {
        ????
        //Here I need to match all employees which belong to any of the teams with id in teamIdList, but I can't figure out how to do this.
    }
    val employeeList = employeeRepository.findAll(spec,pageable)
    ....
}

It seems like it should be an easy enough thing to do. I've found examples, which match against a single value, but not where it matches against a list. What I'm essentially wanting is to find any employee where their team list intersects with the search list.

At the moment my function is just dealing with lists of ID's, but I can change it to map to a List<Team> instead, if that's simpler.

Any suggestions would be greatly appreciated.

CodePudding user response：

I don't think there is a simple operator for this, so you'll have to construct a somewhat more complex query for this.

Basically you would iterate through all the team ids. and check if it is in the list of all team ids referenced by the employee.

    for (teamId : teamIdList) {
         spec = spec.or(createSingleIn(id));
    }

private Specification<Employee> createSingleIn(Long id) {
    return (root, query, cb) -> {
        query.distinct(true); // using an ugly side effect to avoid the ugly side effect that where clauses cause duplicates in JPA.
        return cb.literal(id).in(root.join("teams").get("id"));
    };
}

Of course if you apply other criteria, you'll have to make sure that you control the precedence properly.

I created a complete example here: https://github.com/schauder/stackoverflow/tree/main/jpa/any-in-spec