Server-side dashboards with Querier UI

#java #backend #ui #webdev

Querier UI lets you build server-side HTML dashboards powered by Chart.js directly from your Querier SQL queries — no JavaScript or frontend tooling required.

The rendered output is a self-contained HTML page (or an embeddable fragment) that you can serve directly from any Java web framework.

How it works

Define one or more widgets, each backed by a Querier Select query
Assemble them into a Dashboard
Provide a QueryRunner that bridges Querier to your database layer
Call renderHtml() or renderFragment() to get the output

Quick Start — Dashboards

import com.aytmatech.querier.Aggregate;
import com.aytmatech.querier.Select;
import com.aytmatech.querier.dashboard.*;

// 1. Define a QueryRunner (shown here with Spring JdbcTemplate)
QueryRunner runner = select -> {
    Select.PositionalSqlAndParams sp = select.toPositionalSql();
    return jdbcTemplate.queryForList(sp.sql(), sp.params().toArray());
};

// 2. Define a widget
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();

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

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

Serving from Spring MVC

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

// Returns only the <div> grid + <script> block — no <html>/<head> wrappers
String fragment = DashboardRenderer.builder()
    .dashboard(myDashboard)
    .queryRunner(myRunner)
    .build()
    .renderFragment();

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

By default, Chart.js is loaded from https://cdn.jsdelivr.net/npm/chart.js. Override this for offline or self-hosted deployments:

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

Multi-dataset charts

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

Fine-tune each widget with ChartOptions:

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