In the last tutorial we saw simple use of Querydsl web support. In this tutorial we will see how to access collection properties (annotated with @CollectionElement or with the relationship like @ManyToMany).

By default followings query string bindings are supported (equivalent JPQL expression are shown in brackets)

• Single property values e.g. /employee?name=Rona&dept=IT [WHERE e.name = 'Rona' AND e.dept='IT']
• Multiple property values e.g. /employoee?dept=IT&dept=Admin [WHERE e.dept IN ('IT', 'Admin')]
• Single property values of simple collection e.g. /employee?phones=111-111-111 [JOIN e.phones p WHERE p='111-111-111']
• Multiple property values of simple collection e.g. /employee?phones=111-111-111&phones=222-222-222 [WHERE '111-111-111' MEMBER OF e.phones AND '222-222-222' MEMBER OF e.phones]
• Single property values of collection involving relationships e.g. employees?tasks.name=Coding [JOIN e.tasks t WHERE t.name='Coding']
• Multiple property values of collection involving relationships e.g. /employees?tasks.name=Coding&tasks.name=Designing [JOIN e.tasks t WHERE t.name IN ('Coding', 'Designing')]

Also check our different ways to use collections in JPQL WHERE clause.

In the last example we have seen the first two above bindings. In following example we will focus on remaining four (collection related) bindings.

Example

Entities

@Entity
public class Employee {
  private @Id
  @GeneratedValue
  Long id;
  private String name;
  private String dept;
  @ElementCollection(fetch = FetchType.EAGER)
  private Set<String> phones;
  @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
  private List<Task> tasks;
    .............
}

@Entity
public class Task {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private String supervisor;
    .............
}

Repository

public interface EmployeeRepository extends PagingAndSortingRepository<Employee, Long>,
      QuerydslPredicateExecutor<Employee> {
}

MVC controller

@RestController
public class EmployeeController {

  @Autowired
  private EmployeeRepository repository;

  @GetMapping("/employees")
  public List<Employee> getEmployees(Predicate predicate) {
      Iterable<Employee> iterable = repository.findAll(predicate);
      List<Employee> employees = StreamSupport.stream(iterable.spliterator(), false)
                                              .collect(Collectors.toList());
      return employees;
  }
}

Running

mvn tomcat7:run-war

Output

localhost:8080/employees (without any query string)

In above output we are using json-formatter (a chrome extension to format JSON)

/employees?phones=588989

employees?tasks.name=Coding

employees?tasks.name=Documentation&tasks.name=Testing

Example Project

Dependencies and Technologies Used:

• spring-data-jpa 2.0.7.RELEASE: Spring Data module for JPA repositories.
  Uses org.springframework:spring-context version 5.0.6.RELEASE
• spring-webmvc 5.0.6.RELEASE: Spring Web MVC.
• querydsl-apt 4.2.1: APT based Source code generation for Querydsl.
• querydsl-jpa 4.2.1: JPA support for Querydsl.
• jackson-databind 2.9.5: General data-binding functionality for Jackson: works on core streaming API.
• javax.servlet-api 3.0.1 Java Servlet API
• hibernate-core 5.3.1.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• h2 1.4.197: H2 Database Engine.
• JDK 1.8
• Maven 3.3.9