-
Notifications
You must be signed in to change notification settings - Fork 10
Returning Results
SQL query always returns a result set. Each result set has 0-N rows with same columns. When we write an SQL expression, we manipulate columns and filter (or combine) rows.
Let's look on the following code:
FluentQuery query = FluentJPA.SQL(() -> {
ProductModel productModel = subQuery((ProductModel model) -> {
SELECT(model);
FROM(model);
});
});
It's clear that subQuery()
above may return multiple rows, but we cast the result to a single ProductModel "instance". This is required to be able to access result set columns in the same way as we would do it in native SQL. Had we wrote this:
FluentQuery query = FluentJPA.SQL(() -> {
ProductModel productModel = subQuery((ProductModel model) -> {
SELECT(model);
FROM(model);
});
// SELECT/FROM a single instance!
SELECT(productModel);
FROM(productModel);
});
//and then a List is returned
List<ProductModel> productModels = query.
createQuery(em, ProductModel.class).getResultList();
Note, we SELECT()
from an instance, but the final result is a List<>
.
Conclusion: inside a query we use a single entity to represent the entire result set.
It's important to say that this approach is invented by JPA and JPQL works this way.
When we decide to not map a Table as @Entity
, or a DTO projection is returned, we need a new type. In most case it will be annotated as @Tuple
, see entities & tuples for best practices and considerations when declaring new types.
Note, DTO projection can be also a result of a sub query. We may need a new type for them!
In case the result is returned to Java, FluentJPA always performs the mapping, keeping the same experience for
@Tuple
and@Entity
annotated types.
Let's recall the IN
operator in SQL:
<column> IN (SELECT <single column> FROM ...)
We just argued that SELECT ... FROM ...
maps to an entity type. To be used in IN
, the SELECT
above must select only a single column. In other words we need a projection of that one column from the entity. And in Java this projection maps very well to Collection
type. FluentJPA offers a ready to use function to create this projection and maps Collection.contains()
to IN
. At the end we should be able get the following:
collect(productModel, productModel.getType()).contains(...);
See step-by-step guide for this technique.
In SQL, if the returned result set has just one column and one row, in scalar context it implicitly converts to a scalar. Like collect, returning projection as a Collection, FluentJPA offers pick, returning projection as a scalar. So we can write code like this:
FluentJPA.SQL((Film film) -> {
SELECT(film);
FROM(film);
WHERE(film.getRentalRate() > pick(film, AVG(film.getRentalRate())));
});
See step-by-step guide for this technique.
Getting Started
- Introduction
- Setup
- Data Types
- Entities & Tuples
- Sub Queries
- JPA Integration
- Java Language Support
- Directives
- Library
- Returning Results
- JPA Repositories
Examples
Basic SQL DML Statements
Advanced SQL DML Statements
- Common Table Expressions (WITH Clause)
- Window Functions (OVER Clause)
- Aggregate Expressions
- MERGE
- Temporal Tables
Advanced Topics