Developer Guide

This section covers the data model, database schema, configuration reference, execution engine, connectors and extension points available to developers integrating Data Query into an application.

Data Model

Data Query persists sixteen tables around one aggregate. A query definition is the root: it names a connector, holds the query body, and owns its parameters, its result columns, its dependencies and its visualization. A result column may own a column link, and a visualization may own a chart drill-down; both carry a child table mapping values onto the parameters of their target. A query category groups definitions without owning them. A report template holds the printed layout of one definition, deliberately kept outside the aggregate because the design document is large and the management views never need it. A dashboard owns its widgets, and each widget references a query definition and carries the parameter values it supplies.

---
config:
  layout: elk
---
erDiagram
    dq_query_category {
        integer id                PK
        varchar category_key      UK
        varchar display_name
        varchar description
        varchar icon
        integer display_order
        boolean internationalized
    }
    dq_query_definition {
        integer   id                    PK
        varchar   query_key             UK
        varchar   display_name
        varchar   description
        integer   category_id           FK
        varchar   connector_type
        text      query_body
        boolean   stores_in_temp_table
        varchar   temp_table_name
        boolean   enabled
        boolean   internationalized
        timestamp created_at
        timestamp updated_at
        varchar   created_by
        varchar   updated_by
    }
    dq_query_parameter {
        integer id                  PK
        integer query_definition_id FK
        varchar param_key
        varchar label
        varchar description
        varchar data_type
        varchar format
        boolean required
        varchar default_value
        varchar value_generator_key
        boolean multiselect
        varchar enum_values
        integer display_order
    }
    dq_query_result_column {
        integer id                  PK
        integer query_definition_id FK
        varchar column_key
        varchar label
        varchar data_type
        varchar format
        boolean sortable
        boolean filterable
        boolean visible
        integer display_order
        varchar width
    }
    dq_column_link {
        integer id                   PK
        integer column_definition_id FK
        varchar link_type
        varchar target_query_key
        varchar url_pattern
        varchar link_label
        boolean open_in_new_tab
    }
    dq_column_link_param {
        integer id                   PK
        integer column_link_id       FK
        varchar target_parameter_key
        varchar source_column_key
    }
    dq_query_dependency {
        integer id                  PK
        integer query_definition_id FK
        integer depends_on_id       FK
        integer execution_order
    }
    dq_dependency_param {
        integer id                   PK
        integer query_dependency_id  FK
        varchar source_parameter_key
        varchar target_parameter_key
    }
    dq_query_visualization {
        integer id                  PK
        integer query_definition_id FK
        varchar visualization_type
        varchar title
        varchar subtitle
        boolean show_legend
        boolean stacked
        varchar color_scheme
    }
    dq_chart_axis_mapping {
        integer id               PK
        integer visualization_id FK
        varchar axis_role
        varchar column_key
        varchar label
    }
    dq_chart_drill_down {
        integer id               PK
        integer visualization_id FK
        varchar drill_down_type
        varchar target_query_key
        varchar url_pattern
    }
    dq_chart_dd_param {
        integer id                   PK
        integer drill_down_id        FK
        varchar target_parameter_key
        varchar source_axis_role
        varchar source_column_key
    }
    dq_report_template {
        integer   id                  PK
        integer   query_definition_id FK,UK
        text      content
        boolean   enabled
        timestamp created_at
        timestamp updated_at
        varchar   created_by
        varchar   updated_by
    }
    dq_dashboard {
        integer   id                PK
        varchar   dashboard_key     UK
        varchar   slug              UK
        varchar   title
        varchar   description
        boolean   enabled
        boolean   internationalized
        integer   grid_columns
        timestamp created_at
        timestamp updated_at
        varchar   created_by
        varchar   updated_by
    }
    dq_dashboard_widget {
        integer id                  PK
        integer dashboard_id        FK
        integer query_definition_id FK
        varchar title
        varchar display_mode
        integer pos_x
        integer pos_y
        integer width
        integer height
        integer min_width
        integer min_height
        integer display_order
    }
    dq_dashboard_widget_param {
        integer id                  PK
        integer widget_id           FK
        varchar parameter_key
        varchar parameter_value
        varchar value_generator_key
    }

    dq_query_category       ||--o{ dq_query_definition       : groups
    dq_query_definition     ||--o{ dq_query_parameter        : "declares"
    dq_query_definition     ||--o{ dq_query_result_column    : "declares"
    dq_query_definition     ||--o{ dq_query_dependency       : "depends on"
    dq_query_dependency     }o--|| dq_query_definition       : "target"
    dq_query_dependency     ||--o{ dq_dependency_param       : "forwards"
    dq_query_result_column  ||--o| dq_column_link            : "links via"
    dq_column_link          ||--o{ dq_column_link_param      : "maps"
    dq_query_definition     ||--o| dq_query_visualization    : "drawn as"
    dq_query_visualization  ||--o{ dq_chart_axis_mapping     : "maps axes"
    dq_query_visualization  ||--o| dq_chart_drill_down       : "drills into"
    dq_chart_drill_down     ||--o{ dq_chart_dd_param         : "maps"
    dq_query_definition     ||--o| dq_report_template        : "printed as"
    dq_dashboard            ||--o{ dq_dashboard_widget       : contains
    dq_dashboard_widget     }o--|| dq_query_definition       : renders
    dq_dashboard_widget     ||--o{ dq_dashboard_widget_param : "supplies"

Hold "Alt" / "Option" to enable pan & zoom

Two references are deliberately by key rather than by foreign key. A column link and a chart drill-down store the target_query_key of the query they navigate to, and a widget's parameters name a parameter_key, because both are configuration that must survive the target being edited and are resolved when the report is rendered rather than when it is saved.

Cascade behaviour

Deleting a query definition deletes its parameters, result columns (with their links and link parameter mappings), dependencies (with their parameter mappings), visualization (with its axis mappings and drill-down) and its report template.
Deleting a query category leaves the queries that referenced it in place, with no category.
Deleting a dashboard deletes its widgets and their parameter values. The query definitions the widgets referenced are untouched.

Database Schema

All tables use the dq_ prefix, held in Constants.TABLE_PREFIX. Identifiers are generated with GenerationType.AUTO, so Hibernate allocates them from a per-table sequence.

CREATE SEQUENCE dq_query_category_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_query_definition_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_query_parameter_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_query_result_column_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_column_link_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_column_link_param_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_query_dependency_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_dependency_param_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_query_visualization_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_chart_axis_mapping_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_chart_drill_down_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_chart_dd_param_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_report_template_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_dashboard_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_dashboard_widget_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE dq_dashboard_widget_param_seq START WITH 1 INCREMENT BY 50;

CREATE TABLE dq_query_category (
    id                INTEGER      NOT NULL PRIMARY KEY,
    category_key      VARCHAR(255) NOT NULL,
    display_name      VARCHAR(500) NOT NULL,
    description       VARCHAR(2000),
    icon              VARCHAR(255),
    display_order     INTEGER,
    internationalized BOOLEAN,
    CONSTRAINT uk_dq_qc_key UNIQUE (category_key)
);

CREATE TABLE dq_query_definition (
    id                   INTEGER      NOT NULL PRIMARY KEY,
    query_key            VARCHAR(255) NOT NULL,
    display_name         VARCHAR(500) NOT NULL,
    description          VARCHAR(2000),
    category_id          INTEGER,
    connector_type       VARCHAR(50)  NOT NULL,
    query_body           TEXT         NOT NULL,
    stores_in_temp_table BOOLEAN,
    temp_table_name      VARCHAR(255),
    enabled              BOOLEAN      NOT NULL,
    internationalized    BOOLEAN,
    created_at           TIMESTAMP,
    updated_at           TIMESTAMP,
    created_by           VARCHAR(255),
    updated_by           VARCHAR(255),
    CONSTRAINT uk_dq_qd_key UNIQUE (query_key),
    CONSTRAINT fk_dq_qd_category FOREIGN KEY (category_id) REFERENCES dq_query_category (id)
);

CREATE INDEX idx_dq_qd_key      ON dq_query_definition (query_key);
CREATE INDEX idx_dq_qd_category ON dq_query_definition (category_id);
CREATE INDEX idx_dq_qd_enabled  ON dq_query_definition (enabled);

CREATE TABLE dq_query_parameter (
    id                  INTEGER      NOT NULL PRIMARY KEY,
    query_definition_id INTEGER      NOT NULL,
    param_key           VARCHAR(255) NOT NULL,
    label               VARCHAR(500),
    description         VARCHAR(2000),
    data_type           VARCHAR(50)  NOT NULL,
    format              VARCHAR(255),
    required            BOOLEAN,
    default_value       VARCHAR(1000),
    value_generator_key VARCHAR(255),
    multiselect         BOOLEAN,
    enum_values         VARCHAR(2000),
    display_order       INTEGER,
    CONSTRAINT fk_dq_qp_query FOREIGN KEY (query_definition_id) REFERENCES dq_query_definition (id)
);

CREATE INDEX idx_dq_qp_query ON dq_query_parameter (query_definition_id);

CREATE TABLE dq_query_result_column (
    id                  INTEGER      NOT NULL PRIMARY KEY,
    query_definition_id INTEGER      NOT NULL,
    column_key          VARCHAR(255) NOT NULL,
    label               VARCHAR(500),
    data_type           VARCHAR(50)  NOT NULL,
    format              VARCHAR(255),
    sortable            BOOLEAN,
    filterable          BOOLEAN,
    visible             BOOLEAN,
    display_order       INTEGER,
    width               VARCHAR(50),
    CONSTRAINT fk_dq_qrc_query FOREIGN KEY (query_definition_id) REFERENCES dq_query_definition (id)
);

CREATE INDEX idx_dq_qrc_query ON dq_query_result_column (query_definition_id);

CREATE TABLE dq_column_link (
    id                   INTEGER      NOT NULL PRIMARY KEY,
    column_definition_id INTEGER      NOT NULL,
    link_type            VARCHAR(50)  NOT NULL,
    target_query_key     VARCHAR(255),
    url_pattern          VARCHAR(2000),
    link_label           VARCHAR(500),
    open_in_new_tab      BOOLEAN,
    CONSTRAINT fk_dq_cl_column FOREIGN KEY (column_definition_id) REFERENCES dq_query_result_column (id)
);

CREATE INDEX idx_dq_cl_column ON dq_column_link (column_definition_id);

CREATE TABLE dq_column_link_param (
    id                   INTEGER      NOT NULL PRIMARY KEY,
    column_link_id       INTEGER      NOT NULL,
    target_parameter_key VARCHAR(255) NOT NULL,
    source_column_key    VARCHAR(255) NOT NULL,
    CONSTRAINT fk_dq_clp_link FOREIGN KEY (column_link_id) REFERENCES dq_column_link (id)
);

CREATE INDEX idx_dq_clp_link ON dq_column_link_param (column_link_id);

CREATE TABLE dq_query_dependency (
    id                  INTEGER NOT NULL PRIMARY KEY,
    query_definition_id INTEGER NOT NULL,
    depends_on_id       INTEGER NOT NULL,
    execution_order     INTEGER,
    CONSTRAINT fk_dq_dep_query      FOREIGN KEY (query_definition_id) REFERENCES dq_query_definition (id),
    CONSTRAINT fk_dq_dep_depends_on FOREIGN KEY (depends_on_id)       REFERENCES dq_query_definition (id)
);

CREATE INDEX idx_dq_dep_query      ON dq_query_dependency (query_definition_id);
CREATE INDEX idx_dq_dep_depends_on ON dq_query_dependency (depends_on_id);

CREATE TABLE dq_dependency_param (
    id                   INTEGER      NOT NULL PRIMARY KEY,
    query_dependency_id  INTEGER      NOT NULL,
    source_parameter_key VARCHAR(255) NOT NULL,
    target_parameter_key VARCHAR(255) NOT NULL,
    CONSTRAINT fk_dq_dpm_dependency FOREIGN KEY (query_dependency_id) REFERENCES dq_query_dependency (id)
);

CREATE INDEX idx_dq_dpm_dependency ON dq_dependency_param (query_dependency_id);

CREATE TABLE dq_query_visualization (
    id                  INTEGER     NOT NULL PRIMARY KEY,
    query_definition_id INTEGER     NOT NULL,
    visualization_type  VARCHAR(50) NOT NULL,
    title               VARCHAR(500),
    subtitle            VARCHAR(500),
    show_legend         BOOLEAN,
    stacked             BOOLEAN,
    color_scheme        VARCHAR(2000),
    CONSTRAINT fk_dq_qv_query FOREIGN KEY (query_definition_id) REFERENCES dq_query_definition (id)
);

CREATE TABLE dq_chart_axis_mapping (
    id               INTEGER      NOT NULL PRIMARY KEY,
    visualization_id INTEGER      NOT NULL,
    axis_role        VARCHAR(50)  NOT NULL,
    column_key       VARCHAR(255) NOT NULL,
    label            VARCHAR(500),
    CONSTRAINT fk_dq_cam_visualization FOREIGN KEY (visualization_id) REFERENCES dq_query_visualization (id)
);

CREATE INDEX idx_dq_cam_visualization ON dq_chart_axis_mapping (visualization_id);

CREATE TABLE dq_chart_drill_down (
    id               INTEGER     NOT NULL PRIMARY KEY,
    visualization_id INTEGER     NOT NULL,
    drill_down_type  VARCHAR(50) NOT NULL,
    target_query_key VARCHAR(255),
    url_pattern      VARCHAR(2000),
    CONSTRAINT fk_dq_cdd_visualization FOREIGN KEY (visualization_id) REFERENCES dq_query_visualization (id)
);

CREATE INDEX idx_dq_cdd_visualization ON dq_chart_drill_down (visualization_id);

CREATE TABLE dq_chart_dd_param (
    id                   INTEGER      NOT NULL PRIMARY KEY,
    drill_down_id        INTEGER      NOT NULL,
    target_parameter_key VARCHAR(255) NOT NULL,
    source_axis_role     VARCHAR(50),
    source_column_key    VARCHAR(255),
    CONSTRAINT fk_dq_cddp_drilldown FOREIGN KEY (drill_down_id) REFERENCES dq_chart_drill_down (id)
);

CREATE INDEX idx_dq_cddp_drilldown ON dq_chart_dd_param (drill_down_id);

CREATE TABLE dq_report_template (
    id                  INTEGER   NOT NULL PRIMARY KEY,
    query_definition_id INTEGER   NOT NULL,
    content             TEXT      NOT NULL,
    enabled             BOOLEAN   NOT NULL,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    created_by          VARCHAR(255),
    updated_by          VARCHAR(255),
    CONSTRAINT uk_dq_rt_query UNIQUE (query_definition_id),
    CONSTRAINT fk_dq_rt_query FOREIGN KEY (query_definition_id) REFERENCES dq_query_definition (id)
);

CREATE TABLE dq_dashboard (
    id                INTEGER      NOT NULL PRIMARY KEY,
    dashboard_key     VARCHAR(255) NOT NULL,
    slug              VARCHAR(255) NOT NULL,
    title             VARCHAR(500) NOT NULL,
    description       VARCHAR(2000),
    enabled           BOOLEAN      NOT NULL,
    internationalized BOOLEAN,
    grid_columns      INTEGER,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    created_by        VARCHAR(255),
    updated_by        VARCHAR(255),
    CONSTRAINT uk_dq_dash_key  UNIQUE (dashboard_key),
    CONSTRAINT uk_dq_dash_slug UNIQUE (slug)
);

CREATE INDEX idx_dq_dash_slug    ON dq_dashboard (slug);
CREATE INDEX idx_dq_dash_enabled ON dq_dashboard (enabled);

CREATE TABLE dq_dashboard_widget (
    id                  INTEGER      NOT NULL PRIMARY KEY,
    dashboard_id        INTEGER      NOT NULL,
    query_definition_id INTEGER      NOT NULL,
    title               VARCHAR(500),
    display_mode        VARCHAR(50)  NOT NULL,
    pos_x               INTEGER      NOT NULL,
    pos_y               INTEGER      NOT NULL,
    width               INTEGER      NOT NULL,
    height              INTEGER      NOT NULL,
    min_width           INTEGER,
    min_height          INTEGER,
    display_order       INTEGER,
    CONSTRAINT fk_dq_dw_dashboard FOREIGN KEY (dashboard_id)        REFERENCES dq_dashboard (id),
    CONSTRAINT fk_dq_dw_query     FOREIGN KEY (query_definition_id) REFERENCES dq_query_definition (id)
);

CREATE INDEX idx_dq_dw_dashboard ON dq_dashboard_widget (dashboard_id);
CREATE INDEX idx_dq_dw_query     ON dq_dashboard_widget (query_definition_id);

CREATE TABLE dq_dashboard_widget_param (
    id                  INTEGER      NOT NULL PRIMARY KEY,
    widget_id           INTEGER      NOT NULL,
    parameter_key       VARCHAR(255) NOT NULL,
    parameter_value     VARCHAR(2000),
    value_generator_key VARCHAR(255),
    CONSTRAINT fk_dq_dwp_widget FOREIGN KEY (widget_id) REFERENCES dq_dashboard_widget (id)
);

CREATE INDEX idx_dq_dwp_widget ON dq_dashboard_widget_param (widget_id);

The temporary tables a query chain creates are not part of this schema. They are created and dropped at execution time under generated names; see Query Chaining and Temporary Tables.

Module Overview

Module	artifactId	Description
Model	`appjars-data-query-model`	DTOs, enums, filter and sort objects, the report design model and its JRXML reader/writer, auto-configuration
Business	`appjars-data-query-business`	Service interfaces, the `DataSourceConnector` SPI and the `ValueGenerator` extension point
Business implementation	`appjars-data-query-business-impl`	Service implementations, the execution engine, the SQL, HQL and REST connectors, the built-in value generators, the PDF export pipeline, licence enforcement
Data	`appjars-data-query-data`	DAO interfaces
Data implementation	`appjars-data-query-data-impl`	JPA entities, DAO implementations, DTO converters
Flow	`appjars-data-query-flow`	Vaadin views: query and category management, the dynamic report view, the report designer, the dashboard editor and runtime

The integration tests and the runnable demo live in separate repositories and are not part of the release.

Spring Auto-Configuration

The entry point is com.appjars.dataquery.DataQueryAutoConfiguration, declared in META-INF/spring/org.springframework.boot.autoconfigure.AutoConfiguration.imports in the model module:

com.appjars.dataquery.DataQueryAutoConfiguration

@AutoConfiguration
@EntityScan(basePackageClasses = DataQueryModule.class)
@ComponentScan(basePackageClasses = {DataQueryModule.class})
public class DataQueryAutoConfiguration {}

Both scans are anchored on the marker class com.appjars.dataquery.DataQueryModule, so the whole com.appjars.dataquery package tree is picked up: the JPA entities, the DAO implementations, the services, the connectors, the value generators and the Vaadin views. Placing the appjar on the classpath is therefore all that is required to register them.

There is no startup data seeding and no scheduled task. The only notable startup behaviour is DataQueryRouteConfigurer, a VaadinServiceInitListener that registers the appjar's routes when the Vaadin service initialises, and HqlMetamodelServiceImpl, which derives the editor completion schema from the persistence metamodel once, since the metamodel does not change while the application runs.

Configuration Properties

View URLs

Property	Default	Description
`com.appjars.dataquery.url.admin.queries`	`data-query/admin/queries`	Route of the query definition list view
`com.appjars.dataquery.url.admin.queries-create`	`data-query/admin/queries/create`	Route of the query definition form when creating
`com.appjars.dataquery.url.admin.queries-edit`	`data-query/admin/queries/edit`	Route of the query definition form when editing
`com.appjars.dataquery.url.admin.categories`	`data-query/admin/categories`	Route of the category list view
`com.appjars.dataquery.url.admin.dashboards`	`data-query/admin/dashboards`	Route of the dashboard list view
`com.appjars.dataquery.url.admin.dashboards-create`	`data-query/admin/dashboards/create`	Route of the dashboard editor when creating
`com.appjars.dataquery.url.admin.dashboards-edit`	`data-query/admin/dashboards/edit`	Route of the dashboard editor when editing
`com.appjars.dataquery.url.report`	`data-query/report`	Base route of the report view; the query key is appended as a route parameter
`com.appjars.dataquery.url.report-designer`	`data-query/report-designer`	Base route of the report designer; the query key is appended as a route parameter
`com.appjars.dataquery.url.dashboard`	`data-query/dashboards`	Base route of the runtime dashboard view; the dashboard slug is appended as a route parameter

HQL connector

Property	Default	Description
`com.appjars.dataquery.hql.allowed-entities`	(empty)	Comma-separated list of entity names an HQL query body may reach, matched case-insensitively. When empty, every mapped entity is allowed

REST connector

Property	Default	Description
`com.appjars.dataquery.rest.allowed-hosts`	(empty)	Comma-separated list of host patterns a REST query may call, for example `api.example.com` or `.internal.example.com`. When empty, no REST query executes*
`com.appjars.dataquery.rest.max-rows`	`10000`	Largest number of rows a REST response may hold; more fails the execution rather than truncating
`com.appjars.dataquery.rest.connect-timeout`	`5s`	Connect timeout of a REST call
`com.appjars.dataquery.rest.read-timeout`	`30s`	Read timeout of a REST call
`com.appjars.dataquery.rest.max-response-bytes`	`8388608`	Largest REST response body accepted, checked while the stream is read

Restricting Access to the Management Views

Data Query requires no interface to be implemented by the host application. It reads the authenticated user from Spring Security's SecurityContextHolder when the currentUser value generator resolves, and needs nothing else.

What it does require is a security decision. Every view the appjar provides is annotated @PermitAll, so out of the box any authenticated user can reach the query management views — and a query body is executed against the application's data source. Restricting the management routes is therefore part of the integration, expressed in the application's own SecurityFilterChain rather than in the appjar:

@Bean
SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
    http.authorizeHttpRequests(requests -> requests
        .requestMatchers("/data-query/admin/**", "/data-query/report-designer/**")
            .hasRole("DATA_QUERY_ADMIN"));
    // ... the rest of the application's configuration
    return http.build();
}

Keeping this in one place is deliberate. The appjar stores no roles on its records, so an application that has to restrict individual reports or dashboards matches their routes in the same configuration instead of duplicating an authorisation model:

.requestMatchers("/data-query/report/salary-report").hasRole("HR")
.requestMatchers("/data-query/dashboards/finance-overview").hasRole("FINANCE")

Adjust the matchers to whatever the view URL properties above are set to.

Query Execution Engine

QueryExecutionService is the single entry point for running a query, used by the report view, the dashboard widgets, the exporters and the PDF pipeline alike.

flowchart TD
    A[execute queryKey, parameters, page, sort, filters] --> B[Load definition with parameters,<br/>columns, dependencies, visualization]
    B --> C[Validate and coerce parameters<br/>against their declared types]
    C --> D[Create execution context<br/>with a unique execution id]
    D --> E{Has dependencies?}
    E -- yes --> F[For each, in execution order:<br/>forward mapped parameters]
    F --> G[Run the dependency<br/>through its own connector]
    G --> H{Stores in a<br/>temporary table?}
    H -- yes --> I[Write rows to the<br/>chain's temporary table]
    H -- no --> J[Discard rows;<br/>only parameters flowed on]
    I --> E
    J --> E
    E -- no more --> K[Run the main query through its connector,<br/>with sort, filters and the requested page]
    K --> L[Attach the declared result columns<br/>to the result]
    L --> M[Drop the temporary tables]
    M --> N[Return QueryExecutionResult]

Hold "Alt" / "Option" to enable pan & zoom

The temporary tables are dropped whether the execution succeeded or failed, so a failing chain leaves nothing behind. If a dependency fails, the whole chain is abandoned and the error names the step that failed.

Dependencies are resolved transitively: a dependency may have dependencies of its own. Cycles are rejected when a definition is saved rather than when it is executed, so a stored definition cannot loop.

Three methods serve different consumers. execute returns one page and is what the grid's lazy loading calls, together with count for the total. executeAll returns every row and is what a chart, an export and the PDF pipeline call, since all three need the complete result.

Data Source Connectors

A connector interprets the query body for one kind of data source. DataSourceConnectorRegistry selects one per query by its ConnectorType, and all of them return the same QueryExecutionResult — rows as maps keyed by lowercase column key — which is why the grid, the charts, the exporters, the report templates and the dashboard widgets need no knowledge of where the data came from.

Connector	Query body	Notes
`SQL`	An SQL statement with `:parameter` references and logical temporary table names	Runs against the application's JDBC `DataSource`. The statement is wrapped so that sorting, column filters and paging are applied by the database
`HQL`	A single aliased HQL `select` statement with `:parameter` references	Runs through the application's `EntityManager`, so it sees the same entities and transaction the application does
`REST_API`	A JSON request definition: method, URL, headers, body template and records path	Issues one HTTP request per execution and pages, sorts and filters the response in memory

SQL

Parameters are bound as prepared statement parameters and never concatenated into the statement, so a parameter value cannot change the shape of the query. Sorting and column filters are applied by wrapping the authored statement in an outer query, which is why they work on any statement without the author writing anything for them.

HQL

Two rules on the select list make the result tabular and addressable, and both are enforced when a definition is saved: every selected expression must carry an as alias, unique within the statement, and only individual values may be selected — an entity or an association is rejected. The aliases become the result column keys.

Because HQL has no portable equivalent of wrapping a statement, sorting and filtering are applied to the statement itself: a sort becomes an order by on the expression behind the alias, and a column filter becomes a bound predicate in the where clause, or in having when the alias is an aggregate. Counting a grouped or distinct statement cannot be done by substituting count(*), which would count rows rather than groups, so those shapes are counted by running the statement and counting what comes back.

Validation is layered. The structural rules are checked first, then the statement is handed to the persistence provider's own parser, which is what catches a misspelled attribute. The statement is never executed for validation.

The editor's completion schema is derived from the persistence metamodel: each allowed entity with its singular attributes, associations included by name so an alias followed by a dot completes, and one further level for embeddables. It travels over the existing Vaadin server-to-client channel and is not exposed as an HTTP endpoint.

REST API

The request definition is stored as JSON in the existing query_body column, so the connector needs no schema of its own:

{
  "method": "GET",
  "url": "https://sales.internal.example.com/api/orders?region={region}&since={since}",
  "headers": { "Accept": "application/json", "Authorization": "Bearer ${sales.api.token}" },
  "recordsPath": "data.items"
}

Two substitutions run over the URL, the header values and the body, in this order. ${property} is resolved from the application's Environment, which is how a credential stays in the application's configuration rather than in the query record. {parameterKey} is then replaced with a resolved parameter value — indistinguishably whether the user typed it, a value generator produced it, or a dependency forwarded it.

Escaping follows the position and is not optional: a value in a path segment is percent-encoded with / encoded, a value in a query string is form-encoded, a header value containing a carriage return or line feed is rejected, and a value in the JSON body is JSON-escaped, written unquoted when the placeholder occupies a whole JSON value.

The response must be JSON and must reduce to an array of flat objects. The keys of the objects become the column keys, lowercased, and the union of keys across objects is used, so an endpoint that omits null fields is tolerated. A value that is itself an object or an array fails the execution naming the offending key, which is what keeps the result tabular. Types come from the document rather than from the declared column: a JSON integer becomes a Long, a decimal a BigDecimal, and a string shaped like an ISO-8601 date or date-time becomes a date. A date in any other format arrives as text, so such a column should be declared as Text or the endpoint should emit ISO.

The connector issues the request once per execution and caches the rows on the execution context, keyed by the resolved request. Paging, sorting and column filtering are then applied to that cached list, giving one predictable behaviour for every endpoint rather than depending on what each one supports.

Query Chaining and Temporary Tables

Temporary table handling lives in TemporaryTableStore, not in any one connector, so all three connectors can write one. A store writes the rows of a dependency's result under the logical name the definition gives, records the logical-to-actual mapping on the execution context, and drops the tables when the execution ends. Actual names follow the pattern:

dq_tmp_{executionId}_{logicalTableName}

The execution id is unique per execution, which is what keeps concurrent users from colliding. Logical names are validated to alphanumerics and underscores before they reach a statement. In the query body an author writes the logical name, and the engine substitutes the actual one before execution.

Only an SQL query can read a temporary table, because such a table has no JPA mapping and no HTTP endpoint. The supported directions are therefore:

Direction	Supported
Any connector → temporary table → SQL query	Yes
Any connector → temporary table → HQL or REST query	No
Any connector → forwarded parameters → any connector	Yes

The unsupported case is rejected when the definition is saved, with a message pointing at parameter forwarding as the alternative.

Report Export Pipeline

When a query definition has an enabled report template, ReportExportService produces the PDF:

flowchart TD
    A[exportPdf queryKey, parameters, filters] --> B[Load the enabled template<br/>for the query; fail fast if absent]
    B --> C[executeAll with the same parameters<br/>and column filters the user entered]
    C --> D[Parse the design and pre-sort the rows<br/>by each simple group expression]
    D --> E[Normalise row values to the<br/>declared field classes]
    E --> F[Compile the design<br/>cached by template and timestamp]
    F --> G[Fill with the rows and the<br/>parameter values, plus the locale]
    G --> H[Write PDF to the output stream]

Hold "Alt" / "Option" to enable pan & zoom

Two steps deserve a note. The rows are pre-sorted because report groups are detected by a change of value between consecutive rows and do not sort anything themselves; for every group whose expression is a plain $F{columnKey} reference the rows are stable-sorted by that column, outermost group first. Any other expression is left alone, and ordering the rows is then the query author's responsibility. The values are normalised to the classes the design declares because drivers vary in what they return for the same column — an aggregate over an integer column may arrive as a decimal — and the fill would otherwise fail on the mismatch.

Result columns and parameters map onto the design's field and parameter classes as follows:

Result column type	Field class
Text	`java.lang.String`
Integer	`java.lang.Integer`
Long	`java.lang.Long`
Decimal	`java.lang.Number`
Boolean	`java.lang.Boolean`
Date	`java.time.LocalDate`
Date and Time	`java.time.LocalDateTime`

Parameter type	Parameter class
Text, Enumeration	`java.lang.String`
Integer	`java.lang.Integer`
Long	`java.lang.Long`
Decimal	`java.lang.Double`
Boolean	`java.lang.Boolean`
Date	`java.time.LocalDate`
Date and Time	`java.time.LocalDateTime`
any multi-select parameter	`java.util.Collection`

The design document is stored as JRXML and rendered by the JasperReports engine. That is an implementation detail with no presence in the user interface, the routes, the message keys or the service API, all of which speak of a report, a report template and the report designer.

Internationalised Entity Texts

A query category, a query definition and a dashboard each carry an internationalized flag. When it is set, the texts those records hold are not literal any more: they are translation keys, resolved for the locale of the current user.

Entity	Texts treated as keys
Query category	display name, description
Query definition	display name, description, and the title and subtitle of its chart
Dashboard	title, description, and the per-widget title overrides

Three properties of the arrangement matter when integrating:

The appjar ships no translations for this data. The keys are resolved through whatever I18NProvider the application registers, so where the translations live is the application's decision. An application that also installs the I18N Manager appjar gets a provider that answers from the database first and falls back to classpath bundles, which makes these keys manageable from that appjar's UI. There is no compile-time dependency between the two.

Resolution happens only when a text is rendered. The services, the execution engine and the export pipeline keep working with the stored value, and a resolved text is never written back into the record. A single helper, EntityTextResolver in com.appjars.dataquery.flow.util, concentrates the rule so no view repeats it.

A missing translation falls back to something readable. The shared provider cascades from the requested locale to the language without its country, then the root bundle, then the configured default locale, so a key translated in English but not in Spanish shows its English text to a Spanish user. Only when no bundle holds the key at all does resolution give up, and the resolver then renders the stored key rather than the provider's !key! marker.

Sorting and filtering in the list views happen in the database, which holds keys, so an internationalised record sorts by its key rather than by its translated text.

Service API

The business interfaces live in com.appjars.dataquery.service. Ordinary CRUD comes from CrudService and is omitted here.

QueryDefinitionService

// Lazy listing for the management grid
Stream<QueryDefinitionDto> list(int offset, int limit, QueryDefinitionFilter filter,
    List<QueryDefinitionSort> sortOrders);
Long count(QueryDefinitionFilter filter);

// Number of queries a user can open, i.e. excluding those that only feed a chain
long countVisualQueries();

// Lookup by business key; findByKeyWithDetails also loads parameters,
// result columns, dependencies and the visualization
Optional<QueryDefinitionDto> findByKey(String key);
Optional<QueryDefinitionDto> findByKeyWithDetails(String key);
List<QueryDefinitionDto> findByCategoryId(Integer categoryId);
List<QueryDefinitionDto> findAllEnabled();

// Rejects a key already in use, and a dependency graph containing a cycle
void validateQueryKey(String key, Integer excludeId);
void validateDependencyCycle(Integer queryDefinitionId, List<QueryDependencyDto> dependencies);

QueryExecutionService

// One page of results, with sorting and column filters applied by the connector
QueryExecutionResult execute(String queryKey, Map<String, Object> parameters, int offset,
    int limit, List<QueryDefinitionSort> sortOrders, List<QueryColumnFilter> columnFilters);

// Total row count for the same parameters and filters, for the grid's scrollbar
Long count(String queryKey, Map<String, Object> parameters,
    List<QueryColumnFilter> columnFilters);

// Every row, for charts, exports and the PDF pipeline
QueryExecutionResult executeAll(String queryKey, Map<String, Object> parameters);
QueryExecutionResult executeAll(String queryKey, Map<String, Object> parameters,
    List<QueryColumnFilter> columnFilters);

// Type and required-value validation, without executing anything
void validateParameters(String queryKey, Map<String, Object> parameters);

QueryCategoryService

Stream<QueryCategoryDto> list(int offset, int limit, QueryCategoryFilter filter,
    List<QueryCategorySort> sortOrders);
Long count(QueryCategoryFilter filter);
Optional<QueryCategoryDto> findByKey(String key);
void validateCategoryKey(String key, Integer excludeId);

DashboardService

Stream<DashboardDto> list(int offset, int limit, DashboardFilter filter,
    List<DashboardSort> sortOrders);
Long count(DashboardFilter filter);
Optional<DashboardDto> findBySlug(String slug);
Optional<DashboardDto> findByKeyWithWidgets(String key);

// What the runtime view resolves a slug with: absent when missing or disabled
Optional<DashboardDto> findEnabledBySlug(String slug);

ReportTemplateService and ReportExportService

Optional<ReportTemplateDto> findByQueryDefinitionId(Integer queryDefinitionId);

// The template of a query, only when one exists and is enabled
Optional<ReportTemplateDto> findEnabledByQueryKey(String queryKey);

// Executes the query and writes the filled template as PDF to out
void exportPdf(String queryKey, Map<String, Object> parameters,
    List<QueryColumnFilter> columnFilters, OutputStream out);

ValueGeneratorService and HqlMetamodelService

// Resolves a generator key to a default value, to selection options, or reports support
Object generateDefaultValue(String generatorKey);
List<Object> generateOptions(String generatorKey);
boolean supports(String generatorKey);

// Entity name to attribute names, filtered by the allowed-entities configuration
Map<String, List<String>> getCompletionSchema();
boolean isEntityAllowed(String entityName);
boolean isEntityMapped(String entityName);
Optional<String> resolveAssociationTarget(String entityName, String attributeName);
boolean isNonBasicAttribute(String entityName, String attributeName);

Customisation

Assigning a Router Layout

The appjar's views are registered by DataQueryRouteConfigurer, a VaadinServiceInitListener. Setting its viewsRouterLayout before the routes are registered makes every Data Query view render inside the application's own layout:

@Autowired
@Qualifier("DataQueryRouteConfigurer")
DataQueryRouteConfigurer routeConfigurer;

@PostConstruct
public void configure() {
    routeConfigurer.setViewsRouterLayout(MainLayout.class);
}

The import is com.appjars.dataquery.flow.util.DataQueryRouteConfigurer. Unlike the other appjars, whose class is named RouteConfigurer, this one carries the appjar's name; the bean name matches the class name, so the qualifier above resolves it and can equally be omitted, the type being unique.

Customising View URLs

Every route the appjar registers is driven by a property, so the whole set can be moved under a different prefix without touching code:

com.appjars.dataquery.url.admin.queries=reports/admin/queries
com.appjars.dataquery.url.admin.queries-create=reports/admin/queries/create
com.appjars.dataquery.url.admin.queries-edit=reports/admin/queries/edit
com.appjars.dataquery.url.admin.categories=reports/admin/categories
com.appjars.dataquery.url.admin.dashboards=reports/admin/dashboards
com.appjars.dataquery.url.admin.dashboards-create=reports/admin/dashboards/create
com.appjars.dataquery.url.admin.dashboards-edit=reports/admin/dashboards/edit
com.appjars.dataquery.url.report=reports/view
com.appjars.dataquery.url.report-designer=reports/designer
com.appjars.dataquery.url.dashboard=reports/dashboards

The report view, the report designer and the dashboard view take a route parameter appended to their base — the query key for the first two, the dashboard slug for the third. A dashboard's address therefore changes with its slug and never requires a route to be registered at runtime.

Remember to update any route matchers in the application's security configuration to match.

Custom Value Generators

A value generator supplies a parameter's default value, its selection options, or both, at the moment a parameter form or a dashboard widget resolves. Implement ValueGenerator and register it as a Spring bean:

@Component
public class CurrentCostCentreValueGenerator implements ValueGenerator {

    public static final String KEY = "currentCostCentre";

    private final CostCentreService costCentreService;

    public CurrentCostCentreValueGenerator(CostCentreService costCentreService) {
        this.costCentreService = costCentreService;
    }

    @Override
    public boolean supports(String generatorKey) {
        return KEY.equals(generatorKey);
    }

    @Override
    public Object generateDefaultValue(String generatorKey) {
        return costCentreService.currentCostCentreCode();
    }

    @Override
    public List<Object> generateOptions(String generatorKey) {
        return List.copyOf(costCentreService.selectableCostCentreCodes());
    }
}

An administrator then names currentCostCentre in the Value Generator Key field of a parameter, or in the Value Generator field of a dashboard widget.

The interface is in com.appjars.dataquery.generator. The built-in generators are currentDate, currentDateTime, currentUser — which reads the authenticated principal from Spring Security — and query:<queryKey>:<columnKey>, which offers the values of a column of another query as options.

Data Source Connectors

DataSourceConnector in com.appjars.dataquery.connector is the interface the three shipped connectors implement. A connector interprets the query body in whatever format suits its data source, returns rows as maps keyed by lowercase column key, and must bind parameter and filter values rather than concatenating them. A connector whose results can feed a chain delegates its temporary table methods to TemporaryTableStore; one that cannot participate may throw UnsupportedOperationException and treat cleanup as a no-op.

public interface DataSourceConnector {

    // Whether this connector handles the given type
    boolean supports(ConnectorType connectorType);

    // One page, with sorting and column filters applied
    QueryExecutionResult execute(String queryBody, Map<String, Object> parameters,
        QueryExecutionContext context, int offset, int limit,
        List<QueryDefinitionSort> sortOrders, List<QueryColumnFilter> columnFilters);

    // Every row, for charts, exports and chained dependencies. The filtered overload has a
    // default implementation that delegates to execute with an unbounded window
    QueryExecutionResult executeAll(String queryBody, Map<String, Object> parameters,
        QueryExecutionContext context);
    QueryExecutionResult executeAll(String queryBody, Map<String, Object> parameters,
        QueryExecutionContext context, List<QueryColumnFilter> columnFilters);

    // Total row count for the same parameters and filters
    Long count(String queryBody, Map<String, Object> parameters,
        QueryExecutionContext context, List<QueryColumnFilter> columnFilters);

    // Write this result into the chain's temporary table
    void storeInTemporaryTable(QueryExecutionResult result, String logicalTableName,
        QueryExecutionContext context);

    // Drop every temporary table created during this execution
    void cleanupTemporaryTables(QueryExecutionContext context);
}

Note

A host application cannot yet contribute a connector of its own. A @Component implementing the interface is discovered by the registry, but ConnectorType — the enum the registry selects on, and the list the query form offers — is closed, so there is no value a host-provided connector could answer supports for. An open connector-type registry, letting an application contribute both a connector and its identity, is planned.