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 the JPA module for Querydsl. Querydsl also supports other modules such as MongoDB, and Apache Lucene. 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.
To use Querydsl in your Maven project, add the following dependencies:
<dependencies>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
</dependency>
</dependencies>Next, configure the annotation processing tool (APT) plugin:
<project>
<build>
<plugins>
...
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
...
</plugins>
</build>
</project>Here is an example from our sample application:
public List<DishEntity> findDishes(DishSearchCriteriaTo criteria) {
QDishEntity dish = QDishEntity.dishEntity;
JPAQuery<DishEntity> query = new JPAQuery<OrderEntity>(getEntityManager());
query.from(dish);
Range<BigDecimal> priceRange = criteria.getPriceRange();
if (priceRange != null) {
BigDecimal min = priceRange.getMin();
if (min != null) {
query.where(dish.price.goe(min));
}
BigDecimal max = priceRange.getMax();
if (max != null) {
query.where(dish.price.loe(max));
}
}
String name = criteria.getName();
if ((name != null) && (!name.isEmpty())) {
query.where(dish.name.eq(name));
}
query.orderBy(dish.price.asc(), dish.name.asc());
return query.fetch();
}In this example, we use the so called Q-types (QDishEntity). These are classes generated at build time by the Querydsl annotation processor from entity classes. The Q-type classes can be used as static types representative of the original entity class.
The query.from(dish) method call defines the query source, in this case the dish table. The where method defines a filter. For example, The first call uses the goe operator to filter out any dishes that are not greater or equal to the minimal price. Further operators can be found here.
The orderBy method is used to sort the query results according to certain criteria. Here, we sort the results first by their price and then by their name, both in ascending order. To sort in descending order, use .desc(). To partition query results into groups of rows, see the groupBy method.
For spring, devon4j provides another approach that you can use for your Spring applications to implement Querydsl logic without having to use these metaclasses. An example can be found here.
Spring Data supports the use of native queries. Native queries use simple native SQL syntax that is not parsed in JPQL. This allows you to use all the features that your database supports. The downside to this is that database portability is lost due to the absence of an abstraction layer. Therefore, the queries may not work with another database because it may use a different syntax.
You can implement a native query using @Query annotation with the nativeQuery attribute set to true:
@Query(value="...", nativeQuery=true)|
Note
|
This will not work with Quarkus because Quarkus does not support native queries by using the @Query annotation (see here).
|
You can also implement native queries directly using the EntityManager API and the createNativeQuery method.
This approach also works with Quarkus.
Query query = entityManager.createNativeQuery("SELECT * FROM Product", ProductEntity.class);
List<ProductEntity> products = query.getResultList();|
Note
|
Be sure to use the name of the table when using native queries, while you must use the entity name when implementing queries with JPQL. |
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 differently. Therefore, a mapping is required. devonfw provides this on a lower level with LikePatternSyntax and on a higher level with QueryUtil (see QueryHelper.newStringClause(…)).
When dealing with large amounts of data, an efficient method of retrieving the data is required. Fetching the entire data set each time would be too time consuming. Instead, Paging is used to process only small subsets of the entire data set.
If you are using Spring Data repositories you will get pagination support out of the box by providing the interfaces Page and Pageable:
Page<DishEntity> findAll(Pageable pageable);Then you can create a Pageable object and pass it to the method call as follows:
int page = criteria.getPageNumber();
int size = criteria.getPageSize();
Pageable pageable = PageRequest.of(page, size);
Page<DishEntity> dishes = dishRepository.findAll(pageable);Pagination is also supported for dynamic queries with Querydsl:
public Page<DishEntity> findDishes(DishSearchCriteriaTo criteria) {
QDishEntity dish = QDishEntity.dishEntity;
JPAQuery<DishEntity> query = new JPAQuery<OrderEntity>(getEntityManager());
query.from(dish);
// conditions
int page = criteria.getPageNumber();
int size = criteria.getPageSize();
Pageable pageable = PageRequest.of(page, size);
query.offset(pageable.getOffset());
query.limit(pageable.getPageSize());
List<DishEntity> dishes = query.fetch();
return new PageImpl<>(dishes, pageable, dishes.size());
}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"
}
]
}For spring applications, devon4j also offers its own solution for pagination. You can find an example of this here.
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.