• Overview

• Maven Coordinates

• Annotation Processor

• Usage

• Helidon Config

• SE Application

• Repository Interface

• Pagination

• Dynamic Ordering

• Persistence Session Access

• Transactions

The Helidon SE Data Repository provides a unified API for working with database queries.

Data repository queries are an abstraction over Object–Relational Mapping, or ORM. This enables interfaces with query definitions to be translated into implementation classes at compile time.

The Helidon Data Repository supports Jakarta Persistence and major providers such as EclipseLink and Hibernate.

To enable Data Repository, add the following dependency to your project’s pom.xml (see Managing Dependencies).

<dependency>
    <groupId>io.helidon.data</groupId>
    <artifactId>helidon-data</artifactId>
</dependency>
<dependency>
    <groupId>io.helidon.data.jakarta.persistence</groupId>
    <artifactId>helidon-data-jakarta-persistence</artifactId>
</dependency>

The Jakarta Persistence provider, such as EclipseLink, and the JDBC driver, such as MySQL, are required at runtime:

<dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.jpa</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.core</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

Both the entity model and data repository interfaces require a specific annotation-processor configuration:

<annotationProcessorPaths>
    <path>
        <groupId>io.helidon.bundles</groupId>
        <artifactId>helidon-bundles-apt</artifactId>
        <version>${helidon.version}</version>
    </path>
    <path>
        <groupId>io.helidon.data.jakarta.persistence</groupId>
        <artifactId>helidon-data-jakarta-persistence-codegen</artifactId>
        <version>${helidon.version}</version>
    </path>
</annotationProcessorPaths>

The Data Repository provides an API and tooling for implementing database queries through interface method prototypes.

There are two ways in which such a query can be defined:

• using the method name as the query definition

Optional<Pet> findByName(String name);

• using a method annotated with @Data.Query

@Data.Query("SELECT p FROM Pet p WHERE p.category.name = :categoryName")
List<Pet> selectPetsByCategory(String categoryName);

You must configure the data repository before using it.

In the example below, Helidon Config sets up the data repository using the EclipseLink provider and a MySQL database as a custom connection:

data:
  persistence-units:
    jakarta:
      - connection:
          username: "user"
          password: "password"
          url: "jdbc:mysql://localhost:3306/pets"
          jdbc-driver-class-name: "com.mysql.cj.jdbc.Driver"
          # EclipseLink properties
          properties:
            eclipselink.target-database: "MySQL"
            eclipselink.target-server: "None"
            jakarta.persistence.schema-generation.database.action: "none"

In the next example, Helidon Config sets up the data repository using the Hibernate provider and a MySQL database as a Hikari DataSource:

data:
  sources:
    sql:
      - name: "example"
        provider.hikari:
          username: "user"
          password: "changeit"
          url: "jdbc:mysql://localhost:3306/pets"
          jdbc-driver-class-name: "com.mysql.cj.jdbc.Driver"
  persistence-units:
    jakarta:
      - data-source: "example"
        properties:
          hibernate.dialect: "org.hibernate.dialect.MySQLDialect"
          jakarta.persistence.schema-generation.database.action: "drop-and-create"

DataSource is defined in a separate node, and its name is set to "example". This name is referenced in the corresponding persistence-units configuration node.

The runtime initialization of the data repository is managed by the service registry. You can obtain repository interface instances using the service registry API:

private final KeeperRepository repository = Services.get(KeeperRepository.class);

Data repository interfaces are annotated with @Data.Repository and extend the Data.GenericRepository interface.

The @Data.Repository annotation takes no arguments. The Data.GenericRepository declares no methods but has two generic type parameters, E and ID. E represents the persistence entity type and ID represents the type of the entity’s primary key attribute. Composite primary keys are not supported.

The Data.GenericRepository interface is extended by additional interfaces that add specific features:

Repository Interface Methods

A repository interface may contain three kinds of methods:

• Methods inherited from an ancestor interface

• Methods with a query defined via the @Data.Query annotation

• Methods with a query defined via the method name

The following PetRepository interface contains all of these: inherited methods from CrudRepository, the methods findByName and listNameOrderByName defined by method name, and selectPetsByCategory defined by the @Data.Query annotation:

@Data.Repository
public interface PetRepository extends Data.CrudRepository<Pet, Integer> {

    Optional<Pet> findByName(String name);

    Slice<String> listNameOrderByName(PageRequest request);

    @Data.Query("SELECT p FROM Pet p WHERE p.category.name = :categoryName")
    List<Pet> selectPetsByCategory(String categoryName);

}

content_copy Copied

Method with Query Defined by Method Name

This method type infers the query based on the method name and does not use a specific annotation.

The general method name syntax is illustrated below:

All parts of the pattern are optional except the return type keyword, such as get, find, list, stream, count and exists.

Method Name Prefix and Return Type

A method can have a user-defined prefix. The prefix is a sequence of letters and digits that does not match any return type keyword. This prefix has no influence on the query and can be used to distinguish between methods that have the same query but different return types. If a prefix is used, the following query return type keyword must start with a capital letter.

Optional<Keeper> findByName(String name);
Number countByName(String name);
long longCountByName(String name);

content_copy Copied

The query return type depends on the return type keyword:

Keyword	Return Type	Description
count	Numeric type	Number of rows matching the query criteria
exists	boolean or Boolean	Whether at least one matching row exists
get	Query row type	Single result that throws an exception if there are zero or multiple results
find	Optional<…>	Zero or single result that throws an exception if there are multiple results
list	Collection or List	All matching rows
list	Slice or Page	Pageable result set
stream	Stream	Stream of matching rows

Validation of the keyword–return type mapping is not fully enforced by the code generator, though this may change in future releases.

Projection in Method Name

The projection part is optional and follows directly after the return-type keyword. It consists of expression and property components:

Keyword	Example	Description
Distinct	listDistinctNameByTrainer_Name	Returns only unique values.
First<number>	listFirst10ByAge	Returns up to <number> rows.
Min	getMinPoints	Returns the minimum property value. Requires numeric type.
Max	getMaxPoints	Returns the maximum property value. Requires numeric type.
Sum	getSumPoints	Returns the sum of values. Requires numeric type.
Avg	getAvgPoints	Returns the average value. Requires floating point type.

The property part is the entity property name and it can contain underscores. An underscore is interpreted as a dot, which means navigation to a related entity attribute. For example, Keeper_Name on the Pet entity translates to the JPQL query SELECT p.keeper.name FROM Pet p.

@Entity
public class Pet {
    Keeper keeper;
}

@Entity
public class Keeper {
    String name;
}

@Data.Repository
public interface PetRepository extends Data.GenericRepository<Pet, Integer> {
    List<String> listKeeper_Name();
}

content_copy Copied

Criteria in Method Name

The criteria part of the method name is optional and represents the WHERE clause of the query. It is a logical expression composed of individual conditions joined by the AND and OR operators. A single criteria condition is the property, optionally followed by a set of criteria keywords. For example, NameIgnoreCaseNotEndsWith consists of the entity property name and the keywords IgnoreCase, Not, and EndsWith.

Criteria condition keywords are of two types:

• IgnoreCase and Not modifiers that can appear before the condition keyword

• the condition keyword itself, such as EndsWith

A condition keyword can consume method arguments. Each keyword consumes an exact number of arguments. Method arguments are consumed in the same order as the condition keywords appear in the method name.

Criteria modifiers:

Keyword	Description
Not	Negates the next condition
IgnoreCase	Makes the next condition case-insensitive

Supported condition keywords:

Keyword	Args	Description
After	1	The property value is after the given value. Requires a Comparable property and argument. Intended for date and time. Effectively equivalent to GreaterThan.
Before	1	The property value is before the given value. Requires a Comparable property and argument. Intended for date and time. Effectively equivalent to LessThan.
Contains	1	The property value contains the given value. Requires a String property and argument.
EndsWith	1	The property value ends with the given value. Requires a String property and argument.
StartsWith	1	The property value starts with the given value. Requires a String property and argument.
Equal	1	The property value is equal to the given value.
LessThan	1	The property value is less than the given value. Requires a Comparable property and argument.
LessThanEqual	1	The property value is less or equal to the given value. Requires a Comparable property and argument.
GreaterThan	1	The property value is greater than the given value. Requires a Comparable property and argument.
GreaterThanEqual	1	The property value is greater or equal to the given value. Requires a Comparable property and argument.
Between	2	The property value is between the given values. Requires Comparable properties and arguments.
Like	1	The property value is LIKE the given value. Requires a String property and argument.
In	1	The property value is in the given collection. Requires a Collection argument.
Empty	0	The property value is empty. Requires a Collection property.
Null	0	The property value is NULL.
True	0	The property value is true. Requires boolean or Boolean property.
False	0	The property value is false. Requires boolean or Boolean property.

An example repository method with criteria:

// Returns Keeper entity with keepr.name matching provided name
// or throws an exception when no such entity exists
Keeper getByName(String name);
// Returns list of Keeper entities with keepr.age > provided age value
List<Keeper> listByAgeGreaterThan(int age);
// Checks whether at least one entity with keepr.age between provided
// min and max values exists
boolean existsByAgeBetween(int min, int max);

content_copy Copied

Logical operators:

Keyword	Description
And	Logical AND
Or	Logical OR

An example repository method with criteria and logical operator:

Optional<Keeper> findByNameAndAge(String name, int age);

content_copy Copied

In JPQL, operator precedence places AND above OR as defined in Jakarta Persistence 3.1, section 4.6.6. The same rule applies to SQL.

Ordering in Method Name

The ordering part of the method name is optional and represents the ORDER BY clause of the query. It is a list of ordering rules. A single ordering rule is the property optionally followed by a direction keyword. If more than one ordering rule is present, the rules must be separated by direction keywords, so only the last keyword is optional.

Ordering keywords:

Keyword	Description
Asc	The returned collection is sorted in ascending order.
Desc	The returned collection is sorted in descending order.

The default direction is ascending when the keyword is omitted after the property.

An example repository method with ordering:

List<Keeper> listAllOrderByAgeAscName();

content_copy Copied

Method Name Grammar

The formal grammar for method names is as follows:

        method-name  :: <query> | <delete>

        query        :: <action> [ <projection> ] [ "By" <criteria>  [ "OrderBy" <order> ] ]
                            | <all-action> "All" [ "OrderBy" <order> ]
        delete       :: <del-action> "By" <criteria> ] | <del-action> [ "All" ]

        action       :: <action-l> | <prefix> <action-u>
        all-action   :: <all-action-l> | [ <prefix> ] <all-action-u>
        del-action   :: "delete" | [ <prefix> ] "Delete"

        prefix       :: [a-zA-Z0-9]*
        action-l     :: "count" |  "exists" | "get" | <all-action-l>
        action-u     :: "Count" |  "Exists" | "Get" | <all-action-u>
        all-action-l :: "find" | "list" | "stream"
        all-action-u :: "Find" | "List" | "Stream"

        projection   :: [ <expression> ] [ <property> ]
        expression   :: "First" <number> [ "Distinct" ] | "Distinct"
                            | "Max" | "Min" | "Sum" | "Avg"
        property     :: <identifier> [ "_" <identifier> ]
        identifier   :: [a-zA-Z][a-zA-Z0-9]*
        number       :: [0-9]+

        criteria     :: <condition> { <logical-operator> <condition> }
        condition    :: <property> [ [ "Not" ] [ "IgnoreCase" ] <operator>  ]
        operator     :: "After" | "Before" | Contains" | "EndsWith" | "StartsWith" | "Equal"
                            | "LessThan" | "LessThanEqual" | "GreaterThan" | "GreaterThanEqual"
                            | "Between" | "Like" | "In" | "Empty" | "Null" | "True" | "False"
        logical-operator :: "And" | "Or"

        order        :: <property> [ <direction> [ <order> ] ]
        direction    :: "Asc" | "Desc"

content_copy Copied

Method with Query Defined by @Data.Query Annotation

This method type must be annotated with @Data.Query. The annotation takes a single String value containing the database query. Currently, JPQL is supported. Method arguments must match the query parameters:

• For named parameters, each named parameter in the query must correspond to a method argument with the same name. Order does not matter.

@Data.Query("SELECT p FROM Pet p WHERE p.category.name = :categoryName")
List<Pet> selectPetsByCategory(String categoryName);

content_copy Copied

• For indexed parameters, each argument must appear in the same order as in the query. Indexing starts at 1.

@Data.Query("SELECT p.keeper FROM Pet p WHERE k.name = $1 AND p.category.name = $2")
Optional<Keeper> selectKeeper(String name, String category);

content_copy Copied

Supported return types include:

• the query row type such as an entity class, an entity attribute, or a custom projection

• List, Collection, Stream, or Optional with the query row type as the generic parameter

• Page or Slice with the query row type as the generic parameter

Pagination allows the caller to split a returned data collection into individual pages. When pagination is used, the repository method must have an argument of type PageRequest. The return type of the method is Slice or Page. The PageRequest argument defines the page size and the page index, starting from 0.

Returned page content types:

An example repository method with pagination:

Slice<Keeper> listAll(PageRequest pageRequest);

The ordering part of the method name defines a static ordering rule that cannot be modified at runtime. Dynamic ordering allows the caller to define an additional ordering rule at runtime. Dynamic ordering is triggered by adding an argument of type Sort to the repository method. Both static and dynamic rules can be used together.

An example repository method with dynamic ordering:

List<Keeper> listByAgeBetween(int min, int max, Sort sort);

Static ordering rules from the method name are always applied first, and dynamic rules are added after them:

List<Keeper> listByAgeBetweenOrderByAge(int min, int max, Sort sort);

The caller can access the persistence provider session to implement more complex tasks that the framework does not support directly. This feature is available to a data repository interface that extends the Data.SessionRepository<S> interface.

The generic argument S is the persistence session type, for example EntityManager. The Data.SessionRepository interface provides methods that supply a session managed by the data repository framework, so there is no need to handle the session instance lifecycle.

@Data.Repository
public interface KeeperRepository
        extends Data.GenericRepository<Keeper, Integer>, Data.SessionRepository<EntityManager> {
}

The session instance is available through the Data.SessionRepository<S> interface methods run and call:

public class PetService {

    private final KeeperRepository repository = Services.get(KeeperRepository.class);

    public List<Keeper> keeperQuery(String name) {
        return repository.call(em -> em.createQuery("SELECT k FROM Keeper k WHERE k.name = :name",
                                                    Keeper.class)
                .setParameter("name", name)
                .getResultList());
    }

    public void updateKeeperName(String name, int id) {
        repository.run(em -> em.createQuery("UPDATE Keeper k SET k.name = :name WHERE k.id = :id",
                                            Keeper.class)
                .setParameter("name", name)
                .setParameter("id", id)
                .executeUpdate());
    }

}

Transaction handling is available through Helidon Transaction API.

To enable Helidon Transaction API, add the following dependency to your project’s pom.xml:

<dependency>
    <groupId>jakarta.transaction</groupId>
    <artifactId>jakarta.transaction-api</artifactId>
</dependency>

Helidon JTA Transaction support, such as Narayana, may be provided at runtime to enable JTA transaction type:

<dependency>
    <groupId>io.helidon.transaction</groupId>
    <artifactId>helidon-transaction-narayana</artifactId>
    <scope>runtime</scope>
</dependency>

If JTA transaction support is not provided, Helidon Data runtime will use RESOURCE_LOCAL transaction type.

Transaction Types and Annotations

Transaction Methods

Usage

@Service.Singleton
public class PetService {
    @Tx.Required
    public void doSomething() {
        // Method execution will always be within a transaction
    }

    @Tx.Never
    public void doSomethingElse() {
        // Method execution will never be within a transaction
    }
}

public class KeeperService {
    public void doSomething() {
        Tx.transaction(Tx.Type.REQUIRED,
                       () -> {
                           // Method execution will always be within a transaction
                       });
    }

    public void doSomethingElse() {
        Tx.transaction(Tx.Type.NEVER,
                       () -> {
                           // Method execution will never be within a transaction
                       });
    }
}