JPQL Designer

JPQL Designer is a visual tool designed to simplify the creation and modification of Java Persistence Query Language (JPQL) queries within your Jmix applications. It aims to abstract away some of the complexity of writing JPQL by hand, making it easier for developers of all experience levels to work with data access.

The JPQL Designer provides a visual interface for constructing JPQL queries. You can define the target tables, data selection conditions, sorting, and other parameters without directly writing JPQL code. The tool outputs a properly formatted JPQL query string.

Эта функция свободно доступна для использования в небольших проектах с количеством сущностей и ролей до 10. В более крупных проектах она требует RAD или Enterprise подписки подписку.

The key functionalities include:

Visual Query Design: A graphical user interface allows for the construction of JPQL queries without direct JPQL syntax input.
Intelligent Auto-Completion: Context-aware suggestions for attributes and entities minimize manual typing and errors.
Query Syntax Validation: Real-time checks for correct syntax and common errors ensure query integrity.

Launching JPQL Designer

You can start the JPQL Designer in the following ways:

Through the Code Snippets palette of common user actions when creating data loading operations (entity or entity list loads).
Through a line marker that appears alongside an existing JPQL query, facilitating its modification.
Through the Jmix UI inspector panel, accessing the query property.

JPQL Designer Dialog

The JPQL designer provides a dialog where the query’s structure is displayed, and allows for users to edit and add to the query’s functionality.

The JPQL designer displays the generated JPQL query in a text area for review.

The JPQL designer performs validation before saving to ensure that the query structure is valid and that the various parts of the query are consistent with one another. Specifically, the validation checks:

That any entity attribute used for sorting is also present in the query’s result set.
That the data types of inline parameters used in conditions are compatible with the data types of the entity attributes they are compared against.

When saving a JPQL query structure, the designer generates a JPQL string and stores it in the original Java code or XML file from where the designer was invoked.

If the designer is opened to edit an existing query, it parses the JPQL structure upon loading. However, if the existing query structure uses constructs not supported by the designer (such as nested subqueries), the designer will not open and instead displays a message informing the user that it cannot be launched.

Root Entity Selection

When creating a new query, the designer prompts the user to select the query’s root entity (the "from E" clause).

The Entity class field provides a list of options, allowing quick searches by class name or entity name.
The Entity alias field requires the user to enter an alias for the entity, which is then validated to ensure compliance with JPQL grammar.

When editing an existing query, the designer does not allow changing the root entity without completely clearing the existing query structure.

JPQL Query Results

The Result type field is used to specify the output parameters of the query.

The output parameter can be:

The root entity itself as the output object.
An attribute of the entity, or a nested path to an entity attribute (for example, student.group.number).
Aggregate functions applied to the root entity or entity attributes: AVG, COUNT, MIN, MAX, SUM.

Multiple output parameters can be specified. Additionally, a Distinct option can be selected.

Specifying Query Conditions

The JPQL query designer allows users to construct complex query conditions (the where clause) using a visual interface, providing the following capabilities:

Sub-Condition Construction:
- Selection of an entity alias, an attribute, or nested attribute path for each side of the condition.
- Selection of a logical operator: =, <>, >, <, >=, <=, IN, NOT IN, LIKE, NOT LIKE, IS NULL, IS NOT NULL.
- Modifier support (%, _) for LIKE patterns.
- Support for string functions: LOWER, UPPER, TRIM, and others.
- Support for date/time functions: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, and others.
Support for named parameters within conditions.
Logical grouping of sub-conditions with AND, and OR.

You can specify conditions for your query by clicking either the Add query condition link or the Add (+) button.

Then, select an entity attribute from the Choose Attribute dialog and choose an operator from the available list:

Query conditions can be grouped using and and or operators. The final query might resemble the example below:

Additionally, you have the ability to specify sorting for the query results. You can specify multiple sorting criteria in the ORDER tab. A sort criterion can be either:

An attribute of the root entity.
A nested attribute path (for example, student.group.number).

For each sort criterion you define, you must also select the sort order: ASC (ascending) or DESC (descending).