Querier | AytmaTech Projects

Features

Type-safe: Use Java method references such as Order::getId to avoid raw string column names.
Readable and maintainable: Fluent builder API for clear query composition.
Reporting and analytics friendly: aggregations, GROUP BY, HAVING, subqueries, window functions, CTEs, and set operations.
Execution-agnostic: Named, positional, and indexed parameter outputs.
JPA compatible: Supports custom annotations and both jakarta.persistence and javax.persistence.
Zero runtime dependencies: Lightweight and dependency-free at runtime.
Java 17+: Built with modern Java.
Server-side dashboards (optional): Generate Chart.js powered HTML dashboards without writing JavaScript.

Requirements

Java 17 or higher
Maven 3.8.1+ for building from source

Modules

Querier is a multi-module library published under a single version. Pull in only what you need.

Artifact	Description	Required
querier-core	Type-safe SQL query builder with core features	Always
querier-ui	Server-side dashboard rendering with Chart.js	Optional

querier-ui declares querier-core as a transitive dependency.

Installation

Replace QUERIER_VERSION with the latest release View Releases

Core only (querier-core) - Maven

pom.xml

<dependency>
    <groupId>com.aytmatech</groupId>
    <artifactId>querier-core</artifactId>
    <version>QUERIER_VERSION</version>
</dependency>

Core only (querier-core) - Gradle

build.gradle

implementation 'com.aytmatech:querier-core:QUERIER_VERSION'

Core + Dashboards UI (querier-ui) - Maven

pom.xml

<dependency>
    <groupId>com.aytmatech</groupId>
    <artifactId>querier-ui</artifactId>
    <version>QUERIER_VERSION</version>
</dependency>

Core + Dashboards UI (querier-ui) - Gradle

build.gradle

implementation 'com.aytmatech:querier-ui:QUERIER_VERSION'

Quick Start

1. Define entity classes

Option 1: Custom annotations

Order.java

import com.aytmatech.querier.annotation.Table;
import com.aytmatech.querier.annotation.Column;

@Table("orders")
public class Order {
    private Long id;
    private Long customerId;
    private BigDecimal total;
    private OrderStatus status;

    public Long getId() {
        return id;
    }

    @Column("customer_id")
    public Long getCustomerId() {
        return customerId;
    }

    public BigDecimal getTotal() {
        return total;
    }

    public OrderStatus getStatus() {
        return status;
    }
}

Option 2: JPA annotations

Order.java with JPA

import jakarta.persistence.Table;
import jakarta.persistence.Column;

@Table(name = "orders")
public class Order {
    private Long id;
    private Long customerId;
    private BigDecimal total;
    private OrderStatus status;

    public Long getId() { return id; }

    @Column(name = "customer_id")
    public Long getCustomerId() { return customerId; }

    public BigDecimal getTotal() { return total; }
    public OrderStatus getStatus() { return status; }
}

Custom annotations take precedence over JPA annotations. Without annotations, Querier falls back to snake_case conversion.

2. Build a query

Select Builder

Select select = Select.builder()
    .select(Order::getId).select(Order::getTotal)
    .from(Order.class)
    .where(Condition.eq(Order::getStatus, OrderStatus.PAID))
    .build();

Select.SqlAndParams sp = select.toSqlAndParams();
// sp.sql() -> "SELECT orders.id, orders.total FROM orders WHERE orders.status = :param0"
// sp.params() -> {param0: "PAID"}

3. Execute the query

Using EntityManager

EntityManager em = ...;
Select.SqlAndParams sp = select.toSqlAndParams();
Query nativeQuery = em.createNativeQuery(sp.sql(), Tuple.class);
sp.params().forEach(nativeQuery::setParameter);
List<?> rows = nativeQuery.getResultList();

Framework Compatibility

Framework / API	Method	Placeholder Style	Output
Spring NamedParameterJdbcTemplate	toSqlAndParams()	:paramName	SqlAndParams(sql, Map)
Spring JdbcTemplate	toPositionalSql()	?	PositionalSqlAndParams(sql, List)
Plain JDBC PreparedStatement	toPositionalSql()	?	PositionalSqlAndParams(sql, List)
Hibernate native query	toSqlAndParams()	:paramName	SqlAndParams(sql, Map)
jOOQ	toPositionalSql()	?	PositionalSqlAndParams(sql, List)
R2DBC DatabaseClient	toSqlAndParams()	:paramName	SqlAndParams(sql, Map)
Vert.x SQL Client	toIndexedSql()	$1, $2, ...	IndexedSqlAndParams(sql, List)
Logging / Debugging	toPlainSql()	Values inlined	String

Usage examples by framework

Spring NamedParameterJdbcTemplate

Select.SqlAndParams sp = select.toSqlAndParams();
List<Map<String,Object>> rows = namedJdbc.queryForList(sp.sql(), sp.params());

Spring JdbcTemplate

Select.PositionalSqlAndParams sp = select.toPositionalSql();
List<Map<String,Object>> rows = jdbc.queryForList(sp.sql(), sp.params().toArray());

Plain JDBC PreparedStatement

Select.PositionalSqlAndParams sp = select.toPositionalSql();
PreparedStatement ps = connection.prepareStatement(sp.sql());
for (int i = 0; i < sp.params().size(); i++) {
    ps.setObject(i + 1, sp.params().get(i));
}
ResultSet rs = ps.executeQuery();

jOOQ

Select.PositionalSqlAndParams sp = select.toPositionalSql();
Result<Record> result = dsl.resultQuery(sp.sql(), sp.params().toArray()).fetch();

Hibernate Native Query

Select.SqlAndParams sp = select.toSqlAndParams();
Query query = session.createNativeQuery(sp.sql(), Tuple.class);
sp.params().forEach(query::setParameter);
List<?> results = query.getResultList();

R2DBC DatabaseClient

Select.SqlAndParams sp = select.toSqlAndParams();
DatabaseClient.GenericExecuteSpec spec = client.sql(sp.sql());
for (var entry : sp.params().entrySet()) {
    spec = spec.bind(entry.getKey(), entry.getValue());
}
Flux<Map<String,Object>> rows = spec.fetch().all();

Vert.x SQL Client

Select.IndexedSqlAndParams sp = select.toIndexedSql();
Tuple tuple = Tuple.tuple();
sp.params().forEach(tuple::addValue);
client.preparedQuery(sp.sql()).execute(tuple);

Logging / Debugging

String plainSql = select.toPlainSql();
log.debug("Executing query: {}", plainSql);
// WARNING: Not safe for execution - use for logging only

Identifier Quoting

Querier supports database-specific quoting for reserved words and special identifiers.

QuoteStrategy options

Database	QuoteStrategy	Example output
Default	NONE	orders.id
PostgreSQL, Oracle, SQLite	ANSI	"orders"."id"
MySQL, MariaDB	MYSQL	`orders`.`id`
SQL Server, Azure SQL	SQL_SERVER	[orders].[id]

Using QuoteStrategy

PostgreSQL / ANSI

Select select = Select.builder()
    .select(Order::getId)
    .from(Order.class)
    .quoteStrategy(QuoteStrategy.ANSI)
    .build();
// SQL: SELECT "orders"."id" FROM "orders"

MySQL / MariaDB

Select select = Select.builder()
    .select(Order::getId)
    .from(Order.class)
    .quoteStrategy(QuoteStrategy.MYSQL)
    .build();
// SQL: SELECT `orders`.`id` FROM `orders`

SQL Server / Azure SQL

Select select = Select.builder()
    .select(Order::getId)
    .from(Order.class)
    .quoteStrategy(QuoteStrategy.SQL_SERVER)
    .build();
// SQL: SELECT [orders].[id] FROM [orders]

Default behavior is QuoteStrategy.NONE for backward compatibility.

Quoting applies to table names, schema and catalog qualifiers, and columns in SELECT, WHERE, JOIN, GROUP BY, ORDER BY, and HAVING.
Raw expressions and CTE fragments are not auto-quoted and should be quoted manually when needed.

Schema-qualified names example

@Table(name = "orders", schema = "sales", catalog = "mydb")
public class Order { ... }

Select select = Select.builder()
    .select(Order::getId)
    .from(Order.class)
    .quoteStrategy(QuoteStrategy.ANSI)
    .build();
// SQL: SELECT "mydb"."sales"."orders"."id" FROM "mydb"."sales"."orders"

Usage Examples

Basic SELECT

Select columns

Select select = Select.builder()
    .select(Order::getId).select(Order::getTotal)
    .from(Order.class)
    .build();
// SQL: SELECT orders.id, orders.total FROM orders

WHERE conditions

Simple and complex conditions

Select select = Select.builder()
    .select(Order::getId).select(Order::getTotal)
    .from(Order.class)
    .where(Condition.eq(Order::getStatus, OrderStatus.PAID))
    .build();

Select complex = Select.builder()
    .select(Order::getId)
    .from(Order.class)
    .where(Condition.and(
        Condition.or(
            Condition.eq(Order::getStatus, OrderStatus.PAID),
            Condition.eq(Order::getStatus, OrderStatus.SHIPPED)
        ),
        Condition.gt(Order::getTotal, new BigDecimal("50"))
    ))
    .build();

Comparisons: eq, ne, gt, lt, gte, lte
Pattern matching: like
Range: between, in
Null checks: isNull, isNotNull
Logical operators: and, or, not
Subqueries: exists

JOINs

Join example

Select select = Select.builder()
    .select(Order::getId).select(Customer::getName)
    .from(Order.class)
    .join(Customer.class, Condition.eq(Order::getCustomerId, Customer::getId))
    .build();
// SQL: SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id

join (INNER JOIN)
leftJoin (LEFT JOIN)
rightJoin (RIGHT JOIN)
fullJoin (FULL OUTER JOIN)
crossJoin (CROSS JOIN)

Aggregations

GROUP BY and HAVING

Select select = Select.builder()
    .select(Order::getStatus).select(Aggregate.sum(Order::getTotal).as("total_revenue"))
    .from(Order.class)
    .groupBy(Order::getStatus)
    .having(Condition.gt(Order::getTotal, new BigDecimal("1000")))
    .orderBy(OrderBy.desc("SUM(orders.total)"))
    .build();

Window functions

Running total

Select select = Select.builder()
    .select(Order::getId)
    .select(Order::getTotal)
    .select(WindowFunction.of(Aggregate.sum(Order::getTotal))
        .partitionBy(Order::getCustomerId)
        .orderBy(OrderBy.asc(Order::getCreatedAt))
        .as("running_total"))
    .from(Order.class)
    .build();

Subqueries

Aggregate subquery

Select subquery = Select.builder()
    .select(Aggregate.avg(Order::getTotal).as("avg_total"))
    .from(Order.class)
    .build();

Select main = Select.builder()
    .select(Order::getId).select(Order::getTotal)
    .from(Order.class)
    .where(Condition.gt(Order::getTotal, subquery))
    .build();

CTEs

WITH customer_totals AS (...)

Select cte = Select.builder()
    .select(Order::getCustomerId).select(Aggregate.sum(Order::getTotal).as("total_spent"))
    .from(Order.class)
    .groupBy(Order::getCustomerId)
    .build();

Select main = Select.builder()
    .with("customer_totals", cte)
    .select(Expression.raw("customer_totals.customer_id"))
    .select(Expression.raw("customer_totals.total_spent"))
    .from(Expression.tableRef("customer_totals"))
    .where(Condition.gt(Expression.raw("customer_totals.total_spent"), 5000))
    .build();

Set operations

UNION and more

Select select = Select.builder()
    .select(Order::getCustomerId)
    .from(Order.class)
    .build();

Select unionSelect = Select.builder()
    .select(Order::getProductId)
    .from(Order.class)
    .union(select)
    .build();
// Also available: unionAll(), intersect(), except()

ORDER BY, LIMIT, OFFSET

Sorted and paginated

Select select = Select.builder()
    .select(Order::getId).select(Order::getTotal)
    .from(Order.class)
    .orderBy(OrderBy.desc(Order::getTotal), OrderBy.asc(Order::getId))
    .limit(10)
    .offset(20)
    .build();

SELECT DISTINCT

Distinct statuses

Select select = Select.builder()
    .distinct()
    .select(Order::getStatus)
    .from(Order.class)
    .build();

Dashboards (querier-ui)

Build server-side HTML dashboards powered by Chart.js directly from Querier queries.

This module requires the querier-ui artifact. The output can be a full self-contained HTML page or an embeddable fragment.

Define widgets backed by Select queries
Assemble them into a Dashboard
Provide a QueryRunner for execution
Render with renderHtml() or renderFragment()

Quick start

Dashboard rendering

QueryRunner runner = select -> {
    Select.PositionalSqlAndParams sp = select.toPositionalSql();
    return jdbcTemplate.queryForList(sp.sql(), sp.params().toArray());
};

DashboardWidget revenueWidget = DashboardWidget.builder()
    .title("Revenue by Status")
    .chartType(ChartType.BAR)
    .query(
        Select.builder()
            .select(Order::getStatus)
            .select(Aggregate.sum(Order::getTotal).as("revenue"))
            .from(Order.class)
            .groupBy(Order::getStatus)
            .build()
    )
    .labelColumn("status")
    .dataset(ChartDataset.of("revenue")
        .label("Total Revenue")
        .backgroundColor("#4e79a7")
        .build())
    .build();

Dashboard dashboard = Dashboard.builder()
    .title("Sales Overview")
    .layout(DashboardLayout.GRID_2_COLS)
    .addWidget(revenueWidget)
    .build();

String html = DashboardRenderer.builder()
    .dashboard(dashboard)
    .queryRunner(runner)
    .build()
    .renderHtml();

Supported chart types

BAR, HORIZONTAL_BAR, LINE, PIE, DOUGHNUT, RADAR, POLAR_AREA, SCATTER, BUBBLE

Serving from Spring MVC

Controller endpoint

@GetMapping(value = "/dashboard", produces = MediaType.TEXT_HTML_VALUE)
@ResponseBody
public String dashboard() {
    return DashboardRenderer.builder()
        .dashboard(myDashboard)
        .queryRunner(myRunner)
        .build()
        .renderHtml();
}

Embedding in an existing page

Thymeleaf / JSP fragment

String fragment = DashboardRenderer.builder()
    .dashboard(myDashboard)
    .queryRunner(myRunner)
    .build()
    .renderFragment();

Custom Chart.js URL (offline / self-hosted)

Override CDN URL

DashboardRenderer.builder()
    .dashboard(myDashboard)
    .queryRunner(myRunner)
    .chartJsUrl("/static/js/chart.umd.min.js")
    .build()
    .renderHtml();

Multi-dataset charts

Grouped series

DashboardWidget multiWidget = DashboardWidget.builder()
    .title("Revenue vs Order Count per Customer")
    .chartType(ChartType.BAR)
    .query(monthlyQuery)
    .labelColumn("customer_id")
    .dataset(ChartDataset.of("revenue")
        .label("Revenue").backgroundColor("#4e79a7").build())
    .dataset(ChartDataset.of("order_count")
        .label("Order Count").backgroundColor("#f28e2b").build())
    .build();

Chart options

Axis labels, legend, stacked mode

.chartOptions(ChartOptions.builder()
    .xAxisLabel("Month")
    .yAxisLabel("Revenue (USD)")
    .legendPosition(ChartOptions.LegendPosition.BOTTOM)
    .stacked(true)
    .build())

Dashboard layout options

Layout	Description
SINGLE_COL	One chart per row
GRID_2_COLS	Two charts per row (default)
GRID_3_COLS	Three charts per row
MASONRY	Auto-fit responsive grid

Full example: 4 widgets with full HTML and fragment rendering

OrderDashboardService.java

/**
               * Builds and renders a full HTML dashboard page for Order analytics.
               *
               * @return a self-contained HTML string ready to be served to the browser
               */
              public String renderDashboard() {

                  // QueryRunner: bridges Querier Select -> JdbcTemplate
                  QueryRunner runner = select -> {
                Select.PositionalSqlAndParams sp = select.toPositionalSql();
                return jdbcTemplate.queryForList(sp.sql(), sp.params().toArray());
                  };

                  // Build the dashboard
                  Dashboard dashboard = Dashboard.builder()
                    .title("Orders Dashboard")
                    .layout(DashboardLayout.GRID_2_COLS)
                    .addWidget(revenueOverTimeWidget())
                    .addWidget(ordersCountByStatusWidget())
                    .addWidget(revenueByStatusWidget())
                    .addWidget(topRevenueWidget())
                    .build();

                  // Render as full HTML
                  return DashboardRenderer.builder()
                    .dashboard(dashboard)
                    .queryRunner(runner)
                    .build()
                    .renderHtml();
              }

              /**
               * Builds and renders a fragment HTML dashboard for Order analytics.
               *
               * @return a fragment HTML string ready to be embedded in an existing page
               */
              public String renderDashboardFragment() {

                  // QueryRunner: bridges Querier Select -> JdbcTemplate
                  QueryRunner runner = select -> {
                Select.PositionalSqlAndParams sp = select.toPositionalSql();
                return jdbcTemplate.queryForList(sp.sql(), sp.params().toArray());
                  };

                  // Build the dashboard
                  Dashboard dashboard = Dashboard.builder()
                    .title("Orders Dashboard")
                    .layout(DashboardLayout.GRID_2_COLS)
                    .addWidget(revenueOverTimeWidget())
                    .addWidget(ordersCountByStatusWidget())
                    .addWidget(revenueByStatusWidget())
                    .addWidget(topRevenueWidget())
                    .build();

                  // Render as fragment
                  return DashboardRenderer.builder()
                    .dashboard(dashboard)
                    .queryRunner(runner)
                    .build()
                    .renderFragment();
              }

              // Widget 1: Total revenue grouped by status (BAR)
              private DashboardWidget revenueByStatusWidget() {
                  Select query = Select.builder()
                    .select(Order::getStatus)
                    .select(Aggregate.sum(Order::getTotal).as("revenue"))
                    .from(Order.class)
                    .groupBy(Order::getStatus)
                    .orderBy(OrderBy.desc("SUM(orders.total)"))
                    .build();

                  return DashboardWidget.builder()
                    .title("Total Revenue by Status")
                    .chartType(ChartType.BAR)
                    .query(query)
                    .labelColumn("status")
                    .dataset(ChartDataset.of("revenue")
                      .label("Revenue")
                      .backgroundColor("#4e79a7")
                      .borderColor("#2e5a8a")
                      .build())
                    .chartOptions(ChartOptions.builder()
                      .xAxisLabel("Order Status")
                      .yAxisLabel("Revenue")
                      .legendPosition(ChartOptions.LegendPosition.TOP)
                      .build())
                    .build();
              }

              // Widget 2: Order count grouped by status (DOUGHNUT)
              private DashboardWidget ordersCountByStatusWidget() {
                  Select query = Select.builder()
                    .select(Order::getStatus)
                    .select(Aggregate.countAll().as("order_count"))
                    .from(Order.class)
                    .groupBy(Order::getStatus)
                    .build();

                  return DashboardWidget.builder()
                    .title("Order Count by Status")
                    .chartType(ChartType.DOUGHNUT)
                    .query(query)
                    .labelColumn("status")
                    .dataset(ChartDataset.of("order_count")
                      .label("Orders")
                      .backgroundColor("#4e79a7", "#f28e2b", "#e15759", "#76b7b2", "#59a14f")
                      .build())
                    .chartOptions(ChartOptions.builder()
                      .legendPosition(ChartOptions.LegendPosition.RIGHT)
                      .build())
                    .build();
              }

              // Widget 3: Revenue over time for PAID orders only (LINE)
              private DashboardWidget revenueOverTimeWidget() {
                  Select query = Select.builder()
                    .select(Order::getCreatedAt)
                    .select(Aggregate.sum(Order::getTotal).as("daily_revenue"))
                    .from(Order.class)
                    .where(
                      Condition.and(
                        Condition.eq(Order::getStatus, "PAID"),
                        Condition.between(
                          Order::getCreatedAt,
                          LocalDateTime.of(2025, 10, 1, 0, 0, 0),
                          LocalDateTime.of(2025, 12, 1, 23, 59, 59)
                        )
                      )
                    )
                    .groupBy(Order::getCreatedAt)
                    .orderBy(OrderBy.asc(Order::getCreatedAt))
                    .build();

                  return DashboardWidget.builder()
                    .title("Daily Revenue Trend (Paid Orders)")
                    .chartType(ChartType.LINE)
                    .query(query)
                    .labelColumn("created_at")
                    .dataset(ChartDataset.of("daily_revenue")
                      .label("Daily Revenue")
                      .backgroundColor("rgba(78, 121, 167, 0.2)")
                      .borderColor("#4e79a7")
                      .fill(true)
                      .build())
                    .chartOptions(ChartOptions.builder()
                      .xAxisLabel("Date")
                      .yAxisLabel("Revenue")
                      .legendPosition(ChartOptions.LegendPosition.TOP)
                      .build())
                    .build();
              }

              // Widget 4: Revenue vs order count per customer (multi-dataset BAR)
              private DashboardWidget topRevenueWidget() {
                  Select query = Select.builder()
                    .select(Order::getCustomerId)
                    .select(Aggregate.sum(Order::getTotal).as("customer_revenue"))
                    .select(Aggregate.countAll().as("customer_orders"))
                    .from(Order.class)
                    .where(Condition.gt(Order::getTotal, new BigDecimal("50")))
                    .groupBy(Order::getCustomerId)
                    .orderBy(OrderBy.desc("SUM(orders.total)"))
                    .limit(10)
                    .build();

                  return DashboardWidget.builder()
                    .title("Top 10 Customers: Revenue vs Order Count")
                    .chartType(ChartType.BAR)
                    .query(query)
                    .labelColumn("customer_id")
                    .dataset(ChartDataset.of("customer_revenue")
                      .label("Revenue")
                      .backgroundColor("#4e79a7")
                      .borderColor("#2e5a8a")
                      .build())
                    .dataset(ChartDataset.of("customer_orders")
                      .label("Order Count")
                      .backgroundColor("#f28e2b")
                      .borderColor("#c06d1a")
                      .build())
                    .chartOptions(ChartOptions.builder()
                      .xAxisLabel("Customer ID")
                      .yAxisLabel("Value")
                      .legendPosition(ChartOptions.LegendPosition.TOP)
                      .stacked(false)
                      .build())
                    .build();
              }

API Reference

Core Classes (querier-core)

Select - Main query builder with fluent API
Select.SqlAndParams - Record containing SQL with named (:param) placeholders and parameter map
Select.PositionalSqlAndParams - Record containing SQL with positional (?) placeholders and ordered parameter list
Select.IndexedSqlAndParams - Record containing SQL with indexed ($1, $2, ...) placeholders and ordered parameter list
Condition - WHERE and JOIN condition builder
Aggregate - Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
WindowFunction - Window functions with PARTITION BY and ORDER BY
OrderBy - ORDER BY clause builder
Expression - Raw SQL expressions
CaseWhen - CASE WHEN expressions
JoinType - Enum for JOIN types
AggregateFunction - Enum for aggregate functions
CaseKeyword - Enum for CASE WHEN expression keywords
SetOperationType - Enum for set operation keywords
SortDirection - Enum for sort direction for ORDER BY clauses
SqlClause - Enum for clause keywords used in SELECT statements
SqlOperator - Enum for comparison and logical operators
SqlValue - Enum for special SQL values

Dashboard Classes (querier-ui)

Dashboard - Top-level dashboard definition (title, layout, widgets)
DashboardWidget - A single chart widget binding a Select query to a chart type and dataset(s)
DashboardRenderer - Executes queries and renders the HTML output
QueryRunner - Functional interface bridging Querier to your database execution layer
ChartDataset - Maps a result-set column to Chart.js dataset properties (label, colors, fill)
ChartData - Internal model representing the Chart.js data object (labels + datasets)
ChartDataMapper - Transforms raw query rows into ChartData
ChartOptions - Chart.js options (legend position, axis labels, stacked mode, responsive)
ChartType - Enum of supported Chart.js chart types
DashboardLayout - Enum for the dashboard grid layout

Annotations and naming

Custom: @Table("table_name"), @Column("column_name")
JPA: @Table(name = "table_name"), @Column(name = "column_name")
camelCase getters convert to snake_case by default, such as getOrderTotal() to order_total

Building from Source

Build commands

git clone https://github.com/AytmaTech/querier.git
cd querier

# Build all modules
mvn clean install

# Build only core
mvn clean install -pl querier-core

# Run tests
mvn test

License and Contributing

This project is licensed under the MIT License. Contributions are welcome and pull requests are encouraged.

Support

For issues, questions, or suggestions, open an issue on GitHub or contact [email protected].