The Java Persistence API (JPA) defines its own query language, the java persistence query language (JPQL) (see also JPQL tutorial), which is similar to SQL but operates on entities and their attributes instead of tables and columns.
The simplest CRUD-Queries (e.g. find an entity by its ID) are already build in the devonfw CRUD functionality (via Repository or DAO). For other cases you need to write your own query. We distinguish between static and dynamic queries. Static queries have a fixed JPQL query string that may only use parameters to customize the query at runtime. Instead, dynamic queries can change their clauses (WHERE, ORDER BY, JOIN, etc.) at runtime depending on the given search criteria.
E.g. to find all DishEntries (from MTS sample app) that have a price not exceeding a given maxPrice we write the following JPQL query:
SELECT dish FROM DishEntity dish WHERE dish.price <= :maxPriceHere dish is used as alias (variable name) for our selected DishEntity (what refers to the simple name of the Java entity class). With dish.price we are referring to the Java property price (getPrice()/setPrice(…)) in DishEntity. A named variable provided from outside (the search criteria at runtime) is specified with a colon (:) as prefix. Here with :maxPrice we reference to a variable that needs to be set via query.setParameter("maxPrice", maxPriceValue). JPQL also supports indexed parameters (?) but they are discouraged because they easily cause confusion and mistakes.
With the usage of queries it is possible to avoid exposing relationships or modelling bidirectional relationships, which have some disadvantages (see relationships). This is especially desired for relationships between entities of different business components.
So for example to get all OrderLineEntities for a specific OrderEntity without using the orderLines relation from OrderEntity the following query could be used:
SELECT line FROM OrderLineEntity line WHERE line.order.id = :orderIdFor dynamic queries we use QueryDSL. It allows to implement queries in a powerful but readable and type-safe way (unlike Criteria API). If you already know JPQL you will quickly be able to read and write QueryDSL code. It feels like JPQL but implemented in Java instead of plain text.
Please be aware that code-generation can be painful especially with large teams. We therefore recommend to use QueryDSL without code-generation. Here is an example from our sample application:
public List<DishEntity> findOrders(DishSearchCriteriaTo criteria) {
DishEntity dish = Alias.alias(DishEntity.class);
JPAQuery<OrderEntity> query = newDslQuery(alias); // new JPAQuery<>(getEntityManager()).from(Alias.$(dish));
Range<BigDecimal> priceRange = criteria.getPriceRange();
if (priceRange != null) {
BigDecimal min = priceRange.getMin();
if (min != null) {
query.where(Alias.$(order.getPrice()).ge(min));
}
BigDecimal max = priceRange.getMax();
if (max != null) {
query.where(Alias.$(order.getPrice()).le(max));
}
}
String name = criteria.getName();
if ((name != null) && (!name.isEmpty())) {
// query.where(Alias.$(alias.getName()).eq(name));
QueryUtil.get().whereString(query, Alias.$(alias.getName()), name, criteria.getNameOption());
}
return query.fetch();
}For flexible queries it is often required to allow wildcards (especially in dynamic queries). While users intuitively expect glob syntax the SQL and JPQL standards work different. Therefore a mapping is required. devonfw provides this on a lower level by LikePatternSyntax and on a high level by QueryUtil (see QueryHelper.newStringClause(…)).
devonfw provides pagination support. If you are using spring-data repositories you will get that directly from spring for static queries. Otherwise for dynamic or generally handwritten queries we provide this via QueryUtil.findPaginated(…):
boolean determineTotalHitCount = ...;
return QueryUtil.get().findPaginated(criteria.getPageable(), query, determineTotalHitCount);For the table entity we can make a search request by accessing the REST endpoint with pagination support like in the following examples:
POST mythaistar/services/rest/tablemanagement/v1/table/search
{
"pagination": {
"size":2,
"total":true
}
}
//Response
{
"pagination": {
"size": 2,
"page": 1,
"total": 11
},
"result": [
{
"id": 101,
"modificationCounter": 1,
"revision": null,
"waiterId": null,
"number": 1,
"state": "OCCUPIED"
},
{
"id": 102,
"modificationCounter": 1,
"revision": null,
"waiterId": null,
"number": 2,
"state": "FREE"
}
]
}|
Note
|
As we are requesting with the total property set to true the server responds with the total count of rows for the query.
|
For retrieving a concrete page, we provide the page attribute with the desired value. Here we also left out the total property so the server doesn’t incur on the effort to calculate it:
POST mythaistar/services/rest/tablemanagement/v1/table/search
{
"pagination": {
"size":2,
"page":2
}
}
//Response
{
"pagination": {
"size": 2,
"page": 2,
"total": null
},
"result": [
{
"id": 103,
"modificationCounter": 1,
"revision": null,
"waiterId": null,
"number": 3,
"state": "FREE"
},
{
"id": 104,
"modificationCounter": 1,
"revision": null,
"waiterId": null,
"number": 4,
"state": "FREE"
}
]
}Queries can have meta-parameters and that are provided via SearchCriteriaTo. Besides paging (see above) we also get timeout support.
Writing queries can sometimes get rather complex. The current examples given above only showed very simple basics. Within this topic a lot of advanced features need to be considered like:
-
Order By (Sorting)
-
Aggregation functions like e.g. count/avg/sum
-
SQL Hints (see e.g. Oracle hints or SQL-Server hints) - only when required for ultimate performance tuning
This list is just containing the most important aspects. As we can not cover all these topics here, they are linked to external documentation that can help and guide you.