devonfw guide

Java Persistence API

For mapping java objects to a relational database we use the Java Persistence API (JPA). As JPA implementation we recommend to use Hibernate. For general documentation about JPA and Hibernate follow the links above as we will not replicate the documentation. Here you will only find guidelines and examples how we recommend to use it properly. The following examples show how to map the data of a database to an entity. As we use JPA we abstract from SQL here. However, you will still need a DDL script for your schema and during maintenance also database migrations. Please follow our SQL guide for such artifacts.

Entity

Entities are part of the persistence layer and contain the actual data. They are POJOs (Plain Old Java Objects) on which the relational data of a database is mapped and vice versa. The mapping is configured via JPA annotations (javax.persistence). Usually an entity class corresponds to a table of a database and a property to a column of that table. A persistent entity instance then represents a row of the database table.

A Simple Entity

The following listing shows a simple example:

@Entity
@Table(name="TEXTMESSAGE")
public class MessageEntity extends ApplicationPersistenceEntity implements Message {

  private String text;

  public String getText() {
    return this.text;
  }

  public void setText(String text) {
    this.text = text;
  }
 }

The @Entity annotation defines that instances of this class will be entities which can be stored in the database. The @Table annotation is optional and can be used to define the name of the corresponding table in the database. If it is not specified, the simple name of the entity class is used instead.

In order to specify how to map the attributes to columns we annotate the corresponding getter methods (technically also private field annotation is also possible but approaches can not be mixed). The @Id annotation specifies that a property should be used as primary key. With the help of the @Column annotation it is possible to define the name of the column that an attribute is mapped to as well as other aspects such as nullable or unique. If no column name is specified, the name of the property is used as default.

Note that every entity class needs a constructor with public or protected visibility that does not have any arguments. Moreover, neither the class nor its getters and setters may be final.

Entities should be simple POJOs and not contain business logic.

Entities and Datatypes

Standard datatypes like Integer, BigDecimal, String, etc. are mapped automatically by JPA. Custom datatypes are mapped as serialized BLOB by default what is typically undesired. In order to map atomic custom datatypes (implementations of`+SimpleDatatype`) we implement an AttributeConverter. Here is a simple example:

@Converter(autoApply = true)
public class MoneyAttributeConverter implements AttributeConverter<Money, BigDecimal> {

  public BigDecimal convertToDatabaseColumn(Money attribute) {
    return attribute.getValue();
  }

  public Money convertToEntityAttribute(BigDecimal dbData) {
    return new Money(dbData);
  }
}

The annotation @Converter is detected by the JPA vendor if the annotated class is in the packages to scan. Further, autoApply = true implies that the converter is automatically used for all properties of the handled datatype. Therefore all entities with properties of that datatype will automatically be mapped properly (in our example Money is mapped as BigDecimal).

In case you have a composite datatype that you need to map to multiple columns the JPA does not offer a real solution. As a workaround you can use a bean instead of a real datatype and declare it as @Embeddable. If you are using Hibernate you can implement CompositeUserType. Via the @TypeDef annotation it can be registered to Hibernate. If you want to annotate the CompositeUserType implementation itself you also need another annotation (e.g. MappedSuperclass tough not technically correct) so it is found by the scan.

Enumerations

By default JPA maps Enums via their ordinal. Therefore the database will only contain the ordinals (0, 1, 2, etc.) . So , inside the database you can not easily understand their meaning. Using @Enumerated with EnumType.STRING allows to map the enum values to their name (Enum.name()). Both approaches are fragile when it comes to code changes and refactoring (if you change the order of the enum values or rename them) after the application is deployed to production. If you want to avoid this and get a robust mapping you can define a dedicated string in each enum value for database representation that you keep untouched. Then you treat the enum just like any other custom datatype.

BLOB

If binary or character large objects (BLOB/CLOB) should be used to store the value of an attribute, e.g. to store an icon, the @Lob annotation should be used as shown in the following listing:

@Lob
public byte[] getIcon() {
  return this.icon;
}

Warning

Using a byte array will cause problems if BLOBs get large because the entire BLOB is loaded into the RAM of the server and has to be processed by the garbage collector. For larger BLOBs the type Blob and streaming should be used.

public Blob getAttachment() {
  return this.attachment;
}

Date and Time

To store date and time related values, the temporal annotation can be used as shown in the listing below:

@Temporal(TemporalType.TIMESTAMP)
public java.util.Date getStart() {
  return start;
}

Until Java8 the java data type java.util.Date (or Jodatime) has to be used. TemporalType defines the granularity. In this case, a precision of nanoseconds is used. If this granularity is not wanted, TemporalType.DATE can be used instead, which only has a granularity of milliseconds. Mixing these two granularities can cause problems when comparing one value to another. This is why we only use TemporalType.TIMESTAMP.

QueryDSL and Custom Types

Using the Aliases API of QueryDSL might result in an InvalidDataAccessApiUsageException when using custom datatypes in entity properties. This can be circumvented in two steps:

Ensure you have the following maven dependencies in your project (core module) to support custom types via the Aliases API:

<dependency>
  <groupId>org.ow2.asm</groupId>
  <artifactId>asm</artifactId>
</dependency>
<dependency>
  <groupId>cglib</groupId>
  <artifactId>cglib</artifactId>
</dependency>

Make sure, that all your custom types used in entities provide a non-argument constructor with at least visibility level protected.

Primary Keys

We only use simple Long values as primary keys (IDs). By default it is auto generated (@GeneratedValue(strategy=GenerationType.AUTO)). This is already provided by the class com.devonfw.<projectName>.general.dataaccess.api.AbstractPersistenceEntity within the classic project structure respectively com.devonfw.<projectName>.general.domain.model.AbstractPersistenceEntity within the modern project structure, that you can extend.

The reason for this recommendation is simply because using a number (Long) is the most efficient representation for the database. You may also consider to use other types like String or UUID or even composite custom datatypes and this is technically possible. However, please consider that the primary key is used to lookup the row from the database table, also in foreign keys and thus in JOINs. Please note that your project sooner or later may reach some complexity where performance really matters. Working on big data and performing JOINs when using types such as String (VARCHAR[2]) as primary and foreign keys will kill your performance. You are still free to make a different choice and devonfw only gives recommendations but does not want to dictate you what to do. However, you have been warned about the concequences. If you are well aware of what you are doing, you can still use differnet types of primary keys. In such case, create your own entity not extending AbstractPersistenceEntity or create your own copy of AbstractPersistenceEntity with a different name and a different type of primary key.

In case you have business oriented keys (often as String), you can define an additional property for it and declare it as unique (@Column(unique=true)). Be sure to include "AUTO_INCREMENT" in your sql table field ID to be able to persist data (or similar for other databases).

Relationships

n:1 and 1:1 Relationships

Entities often do not exist independently but are in some relation to each other. For example, for every period of time one of the StaffMember’s of the restaurant example has worked, which is represented by the class WorkingTime, there is a relationship to this StaffMember.

The following listing shows how this can be modeled using JPA:

...

@Entity
public class WorkingTimeEntity {
   ...

   private StaffMemberEntity staffMember;

   @ManyToOne
   @JoinColumn(name="STAFFMEMBER")
   public StaffMemberEntity getStaffMember() {
      return this.staffMember;
   }

   public void setStaffMember(StaffMemberEntity staffMember) {
      this.staffMember = staffMember;
   }
}

To represent the relationship, an attribute of the type of the corresponding entity class that is referenced has been introduced. The relationship is a n:1 relationship, because every WorkingTime belongs to exactly one StaffMember, but a StaffMember usually worked more often than once.
This is why the @ManyToOne annotation is used here. For 1:1 relationships the @OneToOne annotation can be used which works basically the same way. To be able to save information about the relation in the database, an additional column in the corresponding table of WorkingTime is needed which contains the primary key of the referenced StaffMember. With the name element of the @JoinColumn annotation it is possible to specify the name of this column.

1:n and n:m Relationships

The relationship of the example listed above is currently an unidirectional one, as there is a getter method for retrieving the StaffMember from the WorkingTime object, but not vice versa.

To make it a bidirectional one, the following code has to be added to StaffMember:

  private Set<WorkingTimeEntity> workingTimes;

  @OneToMany(mappedBy="staffMember")
  public Set<WorkingTimeEntity> getWorkingTimes() {
    return this.workingTimes;
  }

  public void setWorkingTimes(Set<WorkingTimeEntity> workingTimes) {
    this.workingTimes = workingTimes;
  }

To make the relationship bidirectional, the tables in the database do not have to be changed. Instead the column that corresponds to the attribute staffMember in class WorkingTime is used, which is specified by the mappedBy element of the @OneToMany annotation. Hibernate will search for corresponding WorkingTime objects automatically when a StaffMember is loaded.

The problem with bidirectional relationships is that if a WorkingTime object is added to the set or list workingTimes in StaffMember, this does not have any effect in the database unless the staffMember attribute of that WorkingTime object is set. That is why the devon4j advices not to use bidirectional relationships but to use queries instead. How to do this is shown here. If a bidirectional relationship should be used nevertheless, appropriate add and remove methods must be used.

For 1:n and n:m relations, the devon4j demands that (unordered) Sets and no other collection types are used, as shown in the listing above. The only exception is whenever an ordering is really needed, (sorted) lists can be used.
For example, if WorkingTime objects should be sorted by their start time, this could be done like this:

  private List<WorkingTimeEntity> workingTimes;

  @OneToMany(mappedBy = "staffMember")
  @OrderBy("startTime asc")
  public List<WorkingTimeEntity> getWorkingTimes() {
    return this.workingTimes;
  }

  public void setWorkingTimes(List<WorkingTimeEntity> workingTimes) {
    this.workingTimes = workingTimes;
  }

The value of the @OrderBy annotation consists of an attribute name of the class followed by asc (ascending) or desc (descending).

To store information about a n:m relationship, a separate table has to be used, as one column cannot store several values (at least if the database schema is in first normal form).
For example if one wanted to extend the example application so that all ingredients of one FoodDrink can be saved and to model the ingredients themselves as entities (e.g. to store additional information about them), this could be modeled as follows (extract of class FoodDrink):

  private Set<IngredientEntity> ingredients;

  @ManyToMany()
  @JoinTable
  public Set<IngredientEntity> getIngredients() {
    return this.ingredients;
  }

  public void setOrders(Set<IngredientEntity> ingredients) {
    this.ingredients = ingredients;
  }

Information about the relation is stored in a table called BILL_ORDER that has to have two columns, one for referencing the Bill, the other one for referencing the Order. Note that the @JoinTable annotation is not needed in this case because a separate table is the default solution here (same for n:m relations) unless there is a mappedBy element specified.

For 1:n relationships this solution has the disadvantage that more joins (in the database system) are needed to get a Bill with all the Orders it refers to. This might have a negative impact on performance so that the solution to store a reference to the Bill row/entity in the Order’s table is probably the better solution in most cases.

Note that bidirectional n:m relationships are not allowed for applications based on devon4j. Instead a third entity has to be introduced, which "represents" the relationship (it has two n:1 relationships).

Eager vs. Lazy Loading

Using JPA it is possible to use either lazy or eager loading. Eager loading means that for entities retrieved from the database, other entities that are referenced by these entities are also retrieved, whereas lazy loading means that this is only done when they are actually needed, i.e. when the corresponding getter method is invoked.

Application based on devon4j are strongly advised to always use lazy loading. The JPA defaults are:

@OneToMany: LAZY
@ManyToMany: LAZY
@ManyToOne: EAGER
@OneToOne: EAGER

So at least for @ManyToOne and @OneToOne you always need to override the default by providing fetch = FetchType.LAZY.

Important

Please read the performance guide.

Cascading Relationships

For relations it is also possible to define whether operations are cascaded (like a recursion) to the related entity. By default, nothing is done in these situations. This can be changed by using the cascade property of the annotation that specifies the relation type (@OneToOne, @ManyToOne, @OneToMany, @ManyToOne). This property accepts a CascadeType that offers the following options:

PERSIST (for EntityManager.persist, relevant to inserted transient entities into DB)
REMOVE (for EntityManager.remove to delete entity from DB)
MERGE (for EntityManager.merge)
REFRESH (for EntityManager.refresh)
DETACH (for EntityManager.detach)
ALL (cascade all of the above operations)

See here for more information.

Typesafe Foreign Keys using IdRef

For simple usage you can use Long for all your foreign keys. However, as an optional pattern for advanced and type-safe usage, we offer IdRef.

Embeddable

An embeddable Object is a way to group properties of an entity into a separate Java (child) object. Unlike with implement relationships the embeddable is not a separate entity and its properties are stored (embedded) in the same table together with the entity. This is helpful to structure and reuse groups of properties.

The following example shows an Address implemented as an embeddable class:

@Embeddable
public class AddressEmbeddable {

  private String street;
  private String number;
  private Integer zipCode;
  private String city;

  @Column(name="STREETNUMBER")
  public String getNumber() {
    return number;
  }

  public void setNumber(String number) {
    this.number = number;
  }

  ...  // other getter and setter methods, equals, hashCode
}

As you can see an embeddable is similar to an entity class, but with an @Embeddable annotation instead of the @Entity annotation and without primary key or modification counter. An Embeddable does not exist on its own but in the context of an entity. As a simplification Embeddables do not require a separate interface and ETO as the bean-mapper will create a copy automatically when converting the owning entity to an ETO. However, in this case the embeddable becomes part of your api module that therefore needs a dependency on the JPA.

In addition to that the methods equals(Object) and hashCode() need to be implemented as this is required by Hibernate (it is not required for entities because they can be unambiguously identified by their primary key). For some hints on how to implement the hashCode() method please have a look here.

Using this AddressEmbeddable inside an entity class can be done like this:

  private AddressEmbeddable address;

  @Embedded
  public AddressEmbeddable getAddress() {
    return this.address;
  }

  public void setAddress(AddressEmbeddable address) {
    this.address = address;
  }
}

The @Embedded annotation needs to be used for embedded attributes. Note that if in all columns of the embeddable (here Address) are null, then the embeddable object itself is also null inside the entity. This has to be considered to avoid NullPointerException’s. Further this causes some issues with primitive types in embeddable classes that can be avoided by only using object types instead.

Inheritance

Just like normal java classes, entity classes can inherit from others. The only difference is that you need to specify how to map a class hierarchy to database tables. Generic abstract super-classes for entities can simply be annotated with @MappedSuperclass.

For all other cases the JPA offers the annotation @Inheritance with the property strategy talking an InheritanceType that has the following options:

SINGLE_TABLE: This strategy uses a single table that contains all columns needed to store all entity-types of the entire inheritance hierarchy. If a column is not needed for an entity because of its type, there is a null value in this column. An additional column is introduced, which denotes the type of the entity (called dtype).
TABLE_PER_CLASS: For each concrete entity class there is a table in the database that can store such an entity with all its attributes. An entity is only saved in the table corresponding to its most concrete type. To get all entities of a super type, joins are needed.
JOINED: In this case there is a table for every entity class including abstract classes, which contains only the columns for the persistent properties of that particular class. Additionally there is a primary key column in every table. To get an entity of a class that is a subclass of another one, joins are needed.

Each of the three approaches has its advantages and drawbacks, which are discussed in detail here. In most cases, the first one should be used, because it is usually the fastest way to do the mapping, as no joins are needed when retrieving, searching or persisting entities. Moreover it is rather simple and easy to understand. One major disadvantage is that the first approach could lead to a table with a lot of null values, which might have a negative impact on the database size.

The inheritance strategy has to be annotated to the top-most entity of the class hierarchy (where @MappedSuperclass classes are not considered) like in the following example:

@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
public abstract class MyParentEntity extends ApplicationPersistenceEntity implements MyParent {
  ...
}

@Entity
public class MyChildEntity extends MyParentEntity implements MyChild {
  ...
}

@Entity
public class MyOtherEntity extends MyParentEntity implements MyChild {
  ...
}

As a best practice we advise you to avoid entity hierarchies at all where possible and otherwise to keep the hierarchy as small as possible. In order to just ensure reuse or establish a common API you can consider a shared interface, a @MappedSuperclass or an @Embeddable instead of an entity hierarchy.

Repositories and DAOs

For each entity a code unit is created that groups all database operations for that entity. We recommend to use spring-data repositories for that as it is most efficient for developers. As an alternative there is still the classic approach using DAOs.

Concurrency Control

The concurrency control defines the way concurrent access to the same data of a database is handled. When several users (or threads of application servers) concurrently access a database, anomalies may happen, e.g. a transaction is able to see changes from another transaction although that one did, not yet commit these changes. Most of these anomalies are automatically prevented by the database system, depending on the isolation level (property hibernate.connection.isolation in the jpa.xml, see here, or quarkus.datasource.jdbc.transaction-isolation-level in the application.properties).

Another anomaly is when two stakeholders concurrently access a record, do some changes and write them back to the database. The JPA addresses this with different locking strategies (see here).

As a best practice we are using optimistic locking for regular end-user services (OLTP) and pessimistic locking for batches.

Optimistic Locking

The class com.devonfw.module.jpa.persistence.api.AbstractPersistenceEntity already provides optimistic locking via a modificationCounter with the @Version annotation. Therefore JPA takes care of optimistic locking for you. When entities are transferred to clients, modified and sent back for update you need to ensure the modificationCounter is part of the game. If you follow our guides about transfer-objects and services this will also work out of the box. You only have to care about two things:

How to deal with optimistic locking in relationships?
Assume an entity A contains a collection of B entities. Should there be a locking conflict if one user modifies an instance of A while another user in parallel modifies an instance of B that is contained in the other instance? To address this , take a look at FeatureForceIncrementModificationCounter.
What should happen in the UI if an OptimisticLockException occurred?
According to KISS our recommendation is that the user gets an error displayed that tells him to do his change again on the recent data. Try to design your system and the work processing in a way to keep such conflicts rare and you are fine.

Pessimistic Locking

For back-end services and especially for batches optimistic locking is not suitable. A human user shall not cause a large batch process to fail because he was editing the same entity. Therefore such use-cases use pessimistic locking what gives them a kind of priority over the human users. In your DAO implementation you can provide methods that do pessimistic locking via EntityManager operations that take a LockModeType. Here is a simple example:

  getEntityManager().lock(entity, LockModeType.READ);

When using the lock(Object, LockModeType) method with LockModeType.READ, Hibernate will issue a SELECT … FOR UPDATE. This means that no one else can update the entity (see here for more information on the statement). If LockModeType.WRITE is specified, Hibernate issues a SELECT … FOR UPDATE NOWAIT instead, which has has the same meaning as the statement above, but if there is already a lock, the program will not wait for this lock to be released. Instead, an exception is raised.
Use one of the types if you want to modify the entity later on, for read only access no lock is required.

As you might have noticed, the behavior of Hibernate deviates from what one would expect by looking at the LockModeType (especially LockModeType.READ should not cause a SELECT … FOR UPDATE to be issued). The framework actually deviates from what is specified in the JPA for unknown reasons.

Database Auditing

See auditing guide.

Testing Data-Access

For testing of Entities and Repositories or DAOs see testing guide.

Principles

We strongly recommend these principles:

Use the JPA where ever possible and use vendor (hibernate) specific features only for situations when JPA does not provide a solution. In the latter case consider first if you really need the feature.
Create your entities as simple POJOs and use JPA to annotate the getters in order to define the mapping.
Keep your entities simple and avoid putting advanced logic into entity methods.

Database Configuration

For details on the configuration of the database connection and database logging of the individual framework, please refer to the respective configuration guide.

For spring see here.

For quarkus see here.

Database Migration

See database migration.

Pooling

You typically want to pool JDBC connections to boost performance by recycling previous connections. There are many libraries available to do connection pooling. We recommend to use HikariCP. For Oracle RDBMS see here.

Security

SQL-Injection

A common security threat is SQL-injection. Never build queries with string concatenation or your code might be vulnerable as in the following example:

  String query = "Select op from OrderPosition op where op.comment = " + userInput;
  return getEntityManager().createQuery(query).getResultList();

Via the parameter userInput an attacker can inject SQL (JPQL) and execute arbitrary statements in the database causing extreme damage.

In order to prevent such injections you have to strictly follow our rules for queries:

Use named queries for static queries.
Use QueryDSL for dynamic queries.
Please also consult the SQL Injection Prevention Cheat Sheet.

Limited Permissions for Application

We suggest that you operate your application with a database user that has limited permissions so he can not modify the SQL schema (e.g. drop tables). For initializing the schema (DDL) or to do schema migrations use a separate user that is not used by the application itself.

Queries

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.

Static Queries

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 <= :maxPrice

Here 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.

Using Queries to Avoid Bidirectional Relationships

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 = :orderId

Dynamic Queries

For 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.

Native Queries

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.

Using Wildcards

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(…)).

Pagination

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:

Listing 6. repository

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);

Paging with Querydsl

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());
  }

Pagination example

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"
        }
    ]
}

Pagingation in devon4j-spring

For spring applications, devon4j also offers its own solution for pagination. You can find an example of this here.

Query Meta-Parameters

Queries can have meta-parameters and that are provided via SearchCriteriaTo. Besides paging (see above) we also get timeout support.

Advanced Queries

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:

Joins
Constructor queries
Order By (Sorting)
Grouping
Having
Unions
Sub-Queries
Aggregation functions like e.g. count/avg/sum
Distinct selections
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.

Spring Data

Spring Data JPA is supported by both Spring and Quarkus. However, in Quarkus this approach still has some limitations. For detailed information, see the official Quarkus Spring Data guide.

Motivation

The benefits of Spring Data are (for examples and explanations see next sections):

All you need is one single repository interface for each entity. No need for a separate implementation or other code artifacts like XML descriptors, NamedQueries class, etc.
You have all information together in one place (the repository interface) that actually belong together (where as in the classic approach you have the static queries in an XML file, constants to them in NamedQueries class and referencing usages in DAO implementation classes).
Static queries are most simple to realize as you do not need to write any method body. This means you can develop faster.
Support for paging is already build-in. Again for static query method the is nothing you have to do except using the paging objects in the signature.
Still you have the freedom to write custom implementations via default methods within the repository interface (e.g. for dynamic queries).

Dependency

In case you want to switch to or add Spring Data support to your Spring or Quarkus application, all you need is to add the respective maven dependency:

Listing 7. spring

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Listing 8. quarkus

<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-spring-data-jpa</artifactId>
</dependency>

Repository

For each entity «Entity»Entity an interface is created with the name «Entity»Repository extending JpaRepository. Such repository is the analogy to a Data-Access-Object (DAO) used in the classic approach or when Spring Data is not an option.

Listing 9. Repository

public interface ProductRepository extends JpaRepository<ProductEntity, Long> {

}

The Spring Data repository provides some basic implementations for accessing data, e.g. returning all instances of a type (findAll) or returning an instance by its ID (findById).

Custom method implementation

In addition, repositories can be enriched with additional functionality, e.g. to add QueryDSL functionality or to override the default implementations, by using so called repository fragments:

Example

The following example shows how to write such a repository:

Listing 10. Repository

public interface ProductRepository extends JpaRepository<ProductEntity, Long>, ProductFragment {

  @Query("SELECT product FROM ProductEntity product" //
      + " WHERE product.title = :title")
  List<ProductEntity> findByTitle(@Param("title") String title);

  @Query("SELECT product FROM ProductEntity product" //
      + " WHERE product.title = :title")
  Page<ProductEntity> findByTitlePaginated(@Param("title") String title, Pageable pageable);
}

Listing 11. Repository fragment

public interface ProductFragment {
  Page<ProductEntity> findByCriteria(ProductSearchCriteriaTo criteria);
}

Listing 12. Fragment implementation

public class ProductFragmentImpl implements ProductFragment {
  @Inject
  EntityManager entityManager;

  public Page<ProductEntity> findByCriteria(ProductSearchCriteriaTo criteria) {
    QProductEntity product = QProductEntity.productEntity;
    JPAQuery<ProductEntity> query = new JPAQuery<ProductEntity>(this.entityManager);
    query.from(product);

    String title = criteria.getTitle();
    if ((title != null) && !title.isEmpty()) {
      query.where(product.title.eq(title));
    }

    List<ProductEntity> products = query.fetch();
    return new PageImpl<>(products, PageRequest.of(criteria.getPageNumber(), criteria.getPageSize()), products.size());
  }
}

This ProductRepository has the following features:

CRUD support from Spring Data (see JavaDoc for details).
Support for QueryDSL integration, paging and more.
A static query method findByTitle to find all ProductEntity instances from DB that have the given title. Please note the @Param annotation that links the method parameter with the variable inside the query (:title).
The same with pagination support via findByTitlePaginated method.
A dynamic query method findByCriteria showing the QueryDSL and paging integration into Spring via a fragment implementation.

You can find an implementation of this ProductRepository in our Quarkus reference application.

Note	In Quarkus, native and named queries via the `@Query` annotation are currently not supported

Integration of Spring Data in devon4j-spring

For Spring applications, devon4j offers a proprietary solution that integrates seamlessly with QueryDSL and uses default methods instead of the fragment approach. A separate guide for this can be found here.

Custom methods without fragment approach

The fragment approach is a bit laborious, as three types (repository interface, fragment interface and fragment implementation) are always needed to implement custom methods. We cannot simply use default methods within the repository because we cannot inject the EntityManager directly into the repository interface.

As a workaround, you can create a GenericRepository interface, as is done in the devon4j jpa-spring-data module.

public interface GenericRepository<E> {

  EntityManager getEntityManager();

  ...
}

public class GenericRepositoryImpl<E> implements GenericRepository<E> {

  @Inject
  EntityManager entityManager;

  @Override
  public EntityManager getEntityManager() {

    return this.entityManager;
  }

  ...
}

Then, all your repository interfaces can extend the GenericRepository and you can implement queries directly in the repository interface using default methods:

public interface ProductRepository extends JpaRepository<ProductEntity, Long>, GenericRepository<ProductEntity> {

  default Page<ProductEntity> findByTitle(Title title) {

    EntityManager entityManager = getEntityManager();
    Query query = entityManager.createNativeQuery("select * from Product where title = :title", ProductEntity.class);
    query.setParameter("title", title);
    List<ProductEntity> products = query.getResultList();
    return new PageImpl<>(products);
  }

  ...
}

Drawbacks

Spring Data also has some drawbacks:

Some kind of magic behind the scenes that are not so easy to understand. So in case you want to extend all your repositories without providing the implementation via a default method in a parent repository interface you need to deep-dive into Spring Data. We assume that you do not need that and hope what Spring Data and devon already provides out-of-the-box is already sufficient.
The Spring Data magic also includes guessing the query from the method name. This is not easy to understand and especially to debug. Our suggestion is not to use this feature at all and either provide a @Query annotation or an implementation via default method.

Limitations in Quarkus

Native and named queries are not supported using @Query annotation. You will receive something like: Build step io.quarkus.spring.data.deployment.SpringDataJPAProcessor#build threw an exception: java.lang.IllegalArgumentException: Attribute nativeQuery of @Query is currently not supported
Customizing the base repository for all repository interfaces in the code base, which is done in Spring Data by registering a class the extends SimpleJpaRepository

Data Access Object

The Data Access Objects (DAOs) are part of the persistence layer. They are responsible for a specific entity and should be named «Entity»Dao and «Entity»DaoImpl. The DAO offers the so called CRUD-functionalities (create, retrieve, update, delete) for the corresponding entity. Additionally a DAO may offer advanced operations such as query or locking methods.

DAO Interface

For each DAO there is an interface named «Entity»Dao that defines the API. For CRUD support and common naming we derive it from the ApplicationDao interface that comes with the devon application template:

public interface MyEntityDao extends ApplicationDao<MyEntity> {
  List<MyEntity> findByCriteria(MyEntitySearchCriteria criteria);
}

All CRUD operations are inherited from ApplicationDao so you only have to declare the additional methods.

DAO Implementation

Implementing a DAO is quite simple. We create a class named «Entity»DaoImpl that extends ApplicationDaoImpl and implements your «Entity»Dao interface:

public class MyEntityDaoImpl extends ApplicationDaoImpl<MyEntity> implements MyEntityDao {

  public List<MyEntity> findByCriteria(MyEntitySearchCriteria criteria) {
    TypedQuery<MyEntity> query = createQuery(criteria, getEntityManager());
    return query.getResultList();
  }
  ...
}

Again you only need to implement the additional non-CRUD methods that you have declared in your «Entity»Dao interface. In the DAO implementation you can use the method getEntityManager() to access the EntityManager from the JPA. You will need the EntityManager to create and execute queries.

Static queries for DAO Implementation

All static queries are declared in the file src\main\resources\META-INF\orm.xml:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="1.0" xmlns="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd">
  <named-query name="find.dish.with.max.price">
    <query><![SELECT dish FROM DishEntity dish WHERE dish.price <= :maxPrice]]></query>
  </named-query>
  ...
</hibernate-mapping>

When your application is started, all these static queries will be created as prepared statements. This allows better performance and also ensures that you get errors for invalid JPQL queries when you start your app rather than later when the query is used.

To avoid redundant occurrences of the query name (get.open.order.positions.for.order) we define a constant for each named query:

public class NamedQueries {
  public static final String FIND_DISH_WITH_MAX_PRICE = "find.dish.with.max.price";
}

Note that changing the name of the java constant (FIND_DISH_WITH_MAX_PRICE) can be done easily with refactoring. Further you can trace where the query is used by searching the references of the constant.

The following listing shows how to use this query:

public List<DishEntity> findDishByMaxPrice(BigDecimal maxPrice) {
  Query query = getEntityManager().createNamedQuery(NamedQueries.FIND_DISH_WITH_MAX_PRICE);
  query.setParameter("maxPrice", maxPrice);
  return query.getResultList();
}

Via EntityManager.createNamedQuery(String) we create an instance of Query for our predefined static query. Next we use setParameter(String, Object) to provide a parameter (maxPrice) to the query. This has to be done for all parameters of the query.

Note that using the createQuery(String) method, which takes the entire query as string (that may already contain the parameter) is not allowed to avoid SQL injection vulnerabilities. When the method getResultList() is invoked, the query is executed and the result is delivered as List. As an alternative, there is a method called getSingleResult(), which returns the entity if the query returned exactly one and throws an exception otherwise.

JPA Performance

When using JPA the developer sometimes does not see or understand where and when statements to the database are triggered.

Establishing expectations Developers shouldn’t expect to sprinkle magic pixie dust on POJOs in hopes they will become persistent.

— Dan Allen
https://epdf.tips/seam-in-action.html

So in case you do not understand what is going on under the hood of JPA, you will easily run into performance issues due to lazy loading and other effects.

N plus 1 Problem

The most prominent phenomena is call the N+1 Problem. We use entities from our MTS demo app as an example to explain the problem. There is a DishEntity that has a @ManyToMany relation to IngredientEntity. Now we assume that we want to iterate all ingredients for a dish like this:

DishEntity dish = dao.findDishById(dishId);
BigDecimal priceWithAllExtras = dish.getPrice();
for (IngredientEntity ingredient : dish.getExtras()) {
  priceWithAllExtras = priceWithAllExtras.add(ingredient.getPrice());
}

Now dish.getExtras() is loaded lazy. Therefore the JPA vendor will provide a list with lazy initialized instances of IngredientEntity that only contain the ID of that entity. Now with every call of ingredient.getPrice() we technically trigger an SQL query statement to load the specific IngredientEntity by its ID from the database. Now findDishById caused 1 initial query statement and for any number N of ingredients we are causing an additional query statement. This makes a total of N+1 statements. As causing statements to the database is an expensive operation with a lot of overhead (creating connection, etc.) this ends in bad performance and is therefore a problem (the N+1 Problem).

Solving N plus 1 Problem

To solve the N+1 Problem you need to change your code to only trigger a single statement instead. This can be archived in various ways. The most universal solution is to use FETCH JOIN in order to pre-load the nested N child entities into the first level cache of the JPA vendor implementation. This will behave very similar as if the @ManyToMany relation to IngredientEntity was having FetchType.EAGER but only for the specific query and not in general. Because changing @ManyToMany to FetchType.EAGER would cause bad performance for other usecases where only the dish but not its extra ingredients are needed. For this reason all relations, including @OneToOne should always be FetchType.LAZY. Back to our example we simply replace dao.findDishById(dishId) with dao.findDishWithExtrasById(dishId) that we implement by the following JPQL query:

SELECT dish FROM DishEntity dish
  LEFT JOIN FETCH dish.extras
  WHERE dish.id = :dishId

The rest of the code does not have to be changed but now dish.getExtras() will get the IngredientEntity from the first level cache where is was fetched by the initial query above.

Please note that if you only need the sum of the prices from the extras you can also create a query using an aggregator function:

SELECT sum(dish.extras.price) FROM DishEntity dish

As you can see you need to understand the concepts in order to get good performance.

There are many advanced topics such as creating database indexes or calculating statistics for the query optimizer to get the best performance. For such advanced topics we recommend to have a database expert in your team that cares about such things. However, understanding the N+1 Problem and its solutions is something that every Java developer in the team needs to understand.

IdRef

IdRef can be used to reference other entities in TOs in order to make them type-safe and semantically more expressive. It is an optional concept in devon4j for more complex applications that make intensive use of relations and foreign keys.

Motivation

Assuming you have a method signature like the following:

Long approve(Long cId, Long cuId);

So what are the paremeters? What is returned?

IdRef is just a wrapper for a Long used as foreign key. This makes our signature much more expressive and self-explanatory:

IdRef<Contract> approve(IdRef<Contract> cId, IdRef<Customer> cuId);

Now we can easily see, that the result and the parameters are foreign-keys and which entity they are referring to via their generic type. We can read the javadoc of these entities from the generic type and understand the context. Finally, when passing IdRef objects to such methods, we get compile errors in case we accidentally place parameters in the wrong order.

IdRef and Mapping

In order to easily map relations from entities to transfer-objects and back, we can easily also put according getters and setters into our entities:

public class ContractEntity extends ApplicationPersistenceEntity implements Contract {

  private CustomerEntity customer;

  ...

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "CUSTOMER_ID")
  public CustomerEntity getCustomer() {
    return this.customer;
  }

  public void setCustomer(CustomerEntity customer) {
    this.customer = customer;
  }

  @Transient
  public IdRef<Customer> getCustomerId() {
    return IdRef.of(this.customer);
  }

  public void setCustomerId(IdRef<Customer> customerId) {
    this.customer = JpaHelper.asEntity(customerId, CustomerEntity.class);
  }
}

Now, ensure that you have the same getters and setters for customerId in your Eto:

public class ContractEto extends AbstractEto implements Contract {

  private IdRef<Customer> customerId;

  ...

  public IdRef<Customer> getCustomerId() {
    return this.customerId;
  }

  public void setCustomerId(IdRef<Customer> customerId) {
    this.customerId = customerId;
  }
}

This way the bean-mapper can automatically map from your entity (ContractEntity) to your Eto (ContractEto) and vice-versa.

JpaHelper and EntityManager access

In the above example we used JpaHelper.asEntity to convert the foreign key (IdRef<Customer>) to the according entity (CustomerEntity). This will internally use EntityManager.getReference to properly create a JPA entity. The alternative "solution" that may be used with Long instead of IdRef is typically:

  public void setCustomerId(IdRef<Customer> customerId) {
    Long id = null;
    if (customerId != null) {
      id = customerId.getId();
    }
    if (id == null) {
      this.customer = null;
    } else {
      this.customer = new CustomerEntity();
      this.customer.setId(id);
    }
  }

While this "solution" works is most cases, we discovered some more complex cases, where it fails with very strange hibernate exceptions. When cleanly creating the entity via EntityManager.getReference instead it is working in all cases. So how can JpaHelper.asEntity as a static method access the EntityManager? Therefore we need to initialize this as otherwise you may see this exception:

java.lang.IllegalStateException: EntityManager has not yet been initialized!
	at com.devonfw.module.jpa.dataaccess.api.JpaEntityManagerAccess.getEntityManager(JpaEntityManagerAccess.java:38)
	at com.devonfw.module.jpa.dataaccess.api.JpaHelper.asEntity(JpaHelper.java:49)

For main usage in your application we assume that there is only one instance of EntityManager. Therefore we can initialize this instance during the spring boot setup. This is what we provide for you in JpaInitializer for you when creating a devon4j app.

JpaHelper and spring-test

Further, you also want your code to work in integration tests. Spring-test provides a lot of magic under the hood to make integration testing easy for you. To boost the performance when running multiple tests, spring is smart and avoids creating the same spring-context multiple times. Therefore it stores these contexts so that if a test-case is executed with a specific spring-configuration that has already been setup before, the same spring-context can be reused instead of creating it again. However, your tests may have multiple spring configurations leading to multiple spring-contexts. Even worse these tests can run in any order leading to switching between spring-contexts forth and back. Therefore, a static initializer during the spring boot setup can lead to strange errors as you can get the wrong EntityManager instance. In order to fix such problems, we provide a solution pattern via DbTest ensuring for every test, that the proper instance of EntityManager is initialized. Therefore you should derive directly or indirectly (e.g. via ComponentDbTest and SubsystemDbTest) from DbTesT or adopt your own way to apply this pattern to your tests, when using JpaHelper. This already happens if you are extending ApplicationComponentTest or ApplicationSubsystemTest.

Transaction Handling

For transaction handling we AOP to add transaction control via annotations as aspect. This is done by annotating your code with the @Transactional annotation. You can either annotate your container bean at class level to make all methods transactional or your can annotate individual methods to make them transactional:

  @Transactional
  public Output getData(Input input) {
    ...
  }

JTA Imports

Here are the import statements for transaction support:

import javax.transaction.Transactional;

Caution

Use the above import statement to follow JEE and avoid using org.springframework.transaction.annotation.Transactional.

JTA Dependencies

Please note that with Jakarta EE the dependencies have changed. When you want to start with Jakarta EE you should use these dependencies to get the annoations for dependency injection:

<!-- Java Transaction API (JTA) -->
<dependency>
  <groupId>jakarta.transaction</groupId>
  <artifactId>jakarta.transaction-api</artifactId>
</dependency>

Please note that with quarkus you will get them as transitive dependencies out of the box. The above Jakarate EE dependencies replace these JEE depdencies:

<!-- Java Transaction API (JTA) -->
<dependency>
  <groupId>javax.transaction</groupId>
  <artifactId>javax.transaction-api</artifactId>
</dependency>

Handling constraint violations

Using @Transactional magically wraps transaction handling around your code. As constraints are checked by the database at the end when the transaction gets committed, a constraint violation will be thrown by this aspect outside your code. In case you have to handle constraint violations manually, you have to do that in code outside the logic that is annotated with @Transactional. This may be done in a service operation by catching a ConstraintViolationException (org.hibernate.exception.ConstraintViolationException for hibernate). As a generic approach you can solve this via REST execption handling.

Batches

Transaction control for batches is a lot more complicated and is described in the batch layer.