QueryStream
QueryStream allows you to perform JPA queries using a Stream-like API.
Just like a Java 8 Stream, a QueryStream is built up in a pipeline, using methods like map(), flatMap(), filter(), etc.
Each step in a QueryStream pipeline modifies the construction of an internal JPA Criteria query.
When you're ready to execute the pipeline:
- Invoke
QueryStream.toCriteriaQuery()to extract the CriteriaQuery; or - Invoke
QueryStream.toQuery()to do #1 and also create a TypedQuery; or - Invoke
QueryStream.getResultList()orQueryStream.getResultStream()to do #1 and #2 execute the query
Example
Payroll costs are getting out of control. You need a list of all managers whose direct reports have an average salary above $50,000, ordered from highest to lowest.
Here's how you'd build a Criteria query the usual way:
public List<Employee> getHighPayrollManagers(EntityManager entityManager) {
// Construct query
final CriteriaQuery criteriaQuery = cb.createQuery();
final Root<Employee> manager = criteriaQuery.from(Employee.class);
final SetJoin<Employee, Employee> directReports = manager.join(Employee_.directReports);
final Expression<Double> avgSalary = cb.avg(directReports.get(Employee_.salary));
criteriaQuery.where(cb.greaterThan(avgSalary, 50000.0))
criteriaQuery.groupBy(manager);
criteriaQuery.orderBy(avgSalary);
// Execute query
final TypedQuery<Employee> typedQuery = entityManager.createQuery(criteriaQuery);
return typedQuery.getResultList();
}
@PersistenceContext
private EntityManager entityManager;
private CriteriaBuilder cb;
@PostConstruct
private void setupCriteriaBuilder() {
this.cb = this.entityManager.getCriteriaBuilder();
}
With QueryStream, your code becomes more succint and visually intuitive:
public List<Employee> getHighPayrollManagers() {
// Create a couple of references
final RootRef<Employee> manager = new RootRef<>();
final ExprRef<Double> avgSalary = new ExprRef<>();
// Build and execute query
return qb.stream(Employee.class)
.bind(manager)
.flatMap(Employee_.directReports)
.mapToDouble(Employee_.salary)
.average()
.filter(v -> qb.greaterThan(v, 50000))
.bind(avgSalary)
.groupBy(manager)
.orderBy(avgSalary, false)
.getResultList();
}
@PersistenceContext
private EntityManager entityManager;
private QueryStream.Builder qb;
@PostConstruct
private void setupBuilders() {
this.qb = QueryStream.newBuilder(this.entityManager);
}
See below for more information about references.
Bulk Updates and Deletes
Bulk deletes and updates are also supported.
The QueryStream interface has three subinterfaces for searches, bulk deletes, and bulk updates; these are SearchStream, DeleteStream, and UpdateStream.
- A SearchStream builds an internal CriteriaQuery instance
- A DeleteStream builds an internal CriteriaDelete instance
- An UpdateStream builds an internal CriteriaUpdate instance
Single Values
Some queries are known to return a single value. The SearchValue and its subinterfaces represent streams for which it is known that at most one result will be found. These interfaces have a value() method, which executes the query and returns the single value:
public double getAverageSalary(Employee manager) {
return qb.stream(Employee.class)
.filter(e -> qb.equal(e, manager))
.flatMap(Employee_.directReports)
.mapToDouble(Employee_.salary)
.average()
.value();
}
Other similar methods are min(), max(), sum(), and findFirst().
Value queries can be converted to Optionals and have several related convenience methods like orElse(), isPresent(), etc.
References
Ref objects give you a way to refer to items in the stream pipeline at a later step, by bind()'ing the reference at an earlier step.
References also help code clarity, because they provide a way to give meaningful names to important expressions.
See the getHighPayrollManagers() example above for how it works. The main thing to remember is that the bind() must occur prior to the use of the reference in the pipeline.
Subqueries
QueryStream makes using subqueries easier. A stream can be used as a subquery via asSubquery() or exists().
To find all managers for whom there exists a direct report making over $100,000:
public List<Employee> findManagersWithSixFigureDirectReport() {
return qb.stream(Employee.class)
.filter(manager -> qb.stream(Employee.class)
.filter(report -> qb.and(
qb.equal(report.get(Employee_.manager), manager),
qb.greaterThan(report.get(Employee_.salary), 100000.0)))
.exists());
}
Note the subquery correlation was done "manually" using CriteriaBuilder.equal(); you can clean this up a bit with an explicit correlation using substream():
public List<Employee> findManagersWithSixFigureDirectReport() {
return qb.stream(Employee.class)
.filter(manager -> qb.substream(manager)
.flatMap(Employee_.directReports)
.filter(report -> qb.greaterThan(report.get(Employee_.salary), 100000.0))
.exists());
}
To find all employees with salary greater than the average of their manager's direct reports:
public List<Employee> findEmployeesWithAboveAverageSalaries() {
return qb.stream(Employee.class)
.filter(employee -> qb.greaterThan(
employee.get(Employee_.salary),
qb.stream(Employee.class)
.filter(coworker ->
qb.equal(
coworker.get(Employee_.manager),
employee.get(Employee_.manager)))
.mapToDouble(Employee_.salary)
.average()
.asSubquery()))
.getResultList();
}
Hmmm, that's a lot of nesting. You could make the code clearer by building the subquery separately, and using a reference for the correlation:
public DoubleValue avgCoworkerSalary(RootRef<Employee> employee) {
return qb.stream(Employee.class)
.filter(coworker -> qb.equal(coworker.get(Employee_.manager), employee.get().get(Employee_.manager)))
.mapToDouble(Employee_.salary)
.average();
}
public List<Employee> findEmployeesWithAboveAverageSalaries() {
final RootRef<Employee> employee = new RootRef<>();
return qb.stream(Employee.class)
.bind(employee)
.filter(e -> qb.greaterThan(e.get(Employee_.salary), this.avgCoworkerSalary(employee).asSubquery()))
.getResultList();
}
That's really just regular Java refactoring.
Multiselect and Grouping
To select multiple items, or construct a Java instance, use mapToSelection().
For grouping, use groupBy() and having().
Here's an example that finds all managers paired with the average salary of their direct reports, where that average salary is at least $50,000, sorted by average salary descending:
public List<Object[]> getHighPayrollManagers2() {
// Create references
final RootRef<Employee> manager = new RootRef<>();
final ExprRef<Double> avgSalary = new ExprRef<>();
// Build and execute stream
return qb.stream(Employee.class)
.bind(manager)
.flatMap(Employee_.directReports)
.mapToDouble(Employee_.salary)
.average()
.bind(avgSalary)
.groupBy(manager)
.mapToSelection(Object[].class, e -> qb.array(manager.get(), avgSalary.get()))
.orderBy(avgSalary, false);
.having(avgSalary -> qb.gt(avgSalary, 50000.0))
.getResultList();
}
Offset and Limit
Use skip() and limit() to set the row offset and the maximum number of results.
CriteriaQuery vs TypedQuery Operations
Normally with JPA you first configure a CriteriaQuery, then use it to create a TypedQuery.
Both the CriteriaQuery and the TypedQuery have their own configuration.
For example, where() is a CriteriaQuery method, but setLockMode() is a TypedQuery method.
So at the end of the day, we must apply configuration to the appropriate object for that configuration.
QueryStream pipelines allow you to configure both CriteriaQuery and TypedQuery information, but you should be aware of these caveats:
- Any
TypedQueryconfiguration must come at the end of the pipeline (after any subqueries or joins) - If you invoke
QueryStream.toCriteriaQuery(), the returned object does not capture anyTypedQueryconfiguration - To capture the
TypedQueryconfiguration as well, useQueryStream.toQuery().
The QueryStream methods that configure the TypedQuery are:
QueryStream.skip()QueryStream.limit()QueryStream.withFlushMode()QueryStream.withLockMode()QueryStream.withFetchGraph()QueryStream.withLoadGraph()QueryStream.withHint()andQueryStream.withHints()
Unsupported Operations
In some cases, limitations in the JPA Criteria API impose certain restrictions on what you can do.
For example, skip() and limit() must be at the end of your pipeline, because the JPA Criteria API doesn't allow setting row offset or the maximum results on a subquery or prior to a join.
For another example, you can't sort in a subquery.
Installation
QueryStream is available from Maven Central:
<dependency>
<groupId>org.dellroad</groupId>
<artifactId>querystream-jpa</artifactId>
</dependency>
API Javadocs
Located here.
142 Dec 13, 2022
74 Dec 26, 2022
18 Oct 23, 2022
16 Dec 10, 2022
1 Feb 10, 2022
17 Dec 28, 2022
7 Nov 29, 2022
5 Feb 7, 2022
43 Dec 22, 2022
13 Jul 14, 2022
10 Dec 15, 2022
2 Mar 23, 2022
15 Dec 22, 2022
5.9k Jan 5, 2023
2.1k Jan 2, 2023
411 Jan 3, 2023
1 Jul 22, 2022
8 Nov 24, 2022
2 Feb 4, 2022