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.

This feature is free to use in small projects with up to 10 entities and roles. In a larger project, it requires a RAD or Enterprise subscription.

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.

    jpql designer launch 1

  • Through the Jmix UI inspector panel, accessing the query property.

    jpql designer launch 2

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.

jpql designer

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

jpql designer 1

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

jpql designer 2

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 designer provides several ways to specify query conditions, allowing you to filter and sort the data you retrieve. This section will detail the usage of WHERE, HAVING, and ORDER BY clauses within the JPQL Designer.

Filtering Results with the WHERE Clause

The WHERE clause is used to filter rows based on one or more specified conditions in JPQL queries.

In the JPQL Designer, you can define WHERE conditions by interacting with the visual elements that represent entity attributes and related values. This includes:

  • Selecting an entity alias, an attribute, or nested attribute path from the list of available attributes.

  • Choosing a comparison operator (for example, =, <>, >, <, LIKE, IN).

  • Specifying a value, another attribute, or parameter for the comparison.

The JPQL query designer provides the following capabilities:

  • Sub-Condition Construction:

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

  • Combining Conditions:

    • AND: Both conditions must be true.

    • OR: At least one condition must be true.

  • Parameterized Conditions:

    It’s also possible to create parameterized conditions. This is useful when you need to build dynamic queries. You define parameters within the designer and pass their values when executing the query.

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

jpql designer 3

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

jpql designer 4

To specify a query parameter, click the …​ button in the Parameter column. This will open a dialog where you can either enter the parameter name directly, select an entity attribute, or utilize a function.

jpql designer 6

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

jpql designer 5

Sorting Results with the ORDER BY Clause

The ORDER BY clause allows you to sort your query results based on one or more attributes in ascending (ASC) or descending (DESC) order.

You can specify a single or 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).

Filtering Aggregated Data with the HAVING Clause

The HAVING clause is used specifically with aggregated data. It filters groups based on results from aggregate functions (for example, COUNT, SUM, AVG, MIN, MAX).

The HAVING tab is displayed if the user chooses the Tuple result type and applies an aggregate function to an entity attribute.

jpql designer 7

The GROUP BY functionality is activated if the user selects an entity attribute in the Select block without specifying an aggregate function to it (leaving that the Aggregate field blank).