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
To try examples, run embedded tomcat (configured in pom.xml of example project below):
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 ProjectDependencies 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
|