Dynamic native queries with JSqlParser#

When a dashboard endpoint serves multiple chart widgets — each with its own combination of filters (department, stage, contractor, value range) — hard-coding a SQL variant per filter combination doesn’t scale. Instead, start with a base query and inject WHERE clauses programmatically using JSqlParser.

The architecture#

Request → Controller → QueryModifier.inject(baseSQL, filters) → JDBC → JSON

Base queries live as constants — one per report type, containing the JOINs and GROUP BYs that never change.
A QueryModifier parses the SQL at runtime, appends filter conditions to the WHERE clause, and returns the modified query string.
The controller (or NativeQueryHandler) executes the modified query and returns the results.

QueryModifier — safe WHERE injection#

import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.StringValue;

public class QueryModifier {

    /**
     * Parse the base SQL, append one or more AND conditions, return the modified SQL.
     */
    public static String inject(String baseSql, Map<String, Object> filters) {
        try {
            Select select = (Select) CCJSqlParserUtil.parse(baseSql);
            PlainSelect plain = select.getPlainSelect();

            for (var entry : filters.entrySet()) {
                if (entry.getValue() == null) continue;

                var condition = CCJSqlParserUtil.parseCondExpression(
                    entry.getKey() + " = '" + entry.getValue() + "'"
                );

                if (plain.getWhere() == null) {
                    plain.setWhere(condition);
                } else {
                    plain.setWhere(new AndExpression(plain.getWhere(), condition));
                }
            }
            return select.toString();
        } catch (Exception e) {
            throw new RuntimeException("Failed to modify query", e);
        }
    }
}

Base queries as constants#

public class DashboardQueries {

    public static final String PROJECTS_BY_DEPARTMENT = """
        SELECT d.name AS department, COUNT(p.id) AS project_count,
               SUM(p.awarded_value) AS total_value
        FROM project p
        JOIN department d ON d.id = p.department_id
        GROUP BY d.name
        ORDER BY total_value DESC
    """;

    public static final String MONTHLY_PROGRESS = """
        WITH months AS (
            SELECT generate_series(
                date_trunc('year', CURRENT_DATE),
                CURRENT_DATE, '1 month'
            )::date AS month
        )
        SELECT m.month, COUNT(p.id) AS started, SUM(p.budget) AS committed
        FROM months m
        LEFT JOIN project p ON date_trunc('month', p.start_date) = m.month
        GROUP BY m.month
        ORDER BY m.month
    """;

    public static final String TOP_VENDORS = """
        SELECT v.name AS vendor, COUNT(c.id) AS contracts, SUM(c.value) AS total
        FROM vendor v
        JOIN contract c ON c.vendor_id = v.id
        GROUP BY v.name
        ORDER BY total DESC
        LIMIT 10
    """;
}

Controller that uses it#

@RestController
@RequestMapping("/dashboard")
public class DashboardChartController {

    @Autowired private JdbcTemplate jdbc;

    @GetMapping("/projects-by-department")
    public List<Map<String, Object>> projectsByDepartment(
            @RequestParam(required = false) String stage,
            @RequestParam(required = false) Long departmentId,
            @RequestParam(required = false) Long contractorId) {

        Map<String, Object> filters = new LinkedHashMap<>();
        if (stage != null)        filters.put("p.stage", stage);
        if (departmentId != null) filters.put("p.department_id", departmentId);
        if (contractorId != null) filters.put("c.contractor_id", contractorId);

        String sql = QueryModifier.inject(DashboardQueries.PROJECTS_BY_DEPARTMENT, filters);
        return jdbc.queryForList(sql);
    }

    @GetMapping("/top-vendors")
    public List<Map<String, Object>> topVendors(
            @RequestParam(required = false) String stage) {

        Map<String, Object> filters = new LinkedHashMap<>();
        if (stage != null) filters.put("c.stage", stage);

        String sql = QueryModifier.inject(DashboardQueries.TOP_VENDORS, filters);
        return jdbc.queryForList(sql);
    }
}

Using JSqlParser inside a NativeQueryHandler#

JSqlParser works just as well inside a Palmyra NativeQueryHandler — you get the framework’s pagination, onQueryResult, and aclCheck for free while still composing the SQL dynamically.

@Component
@CrudMapping("/reports/projects-by-department")
public class ProjectsByDepartmentReport implements NativeQueryHandler {

    @Override
    public int aclCheck(FilterCriteria criteria, HandlerContext ctx) {
        // Rely on @Permission or return 0 for open access
        return 0;
    }

    @Override
    public void preProcess(FilterCriteria criteria, HandlerContext ctx) {
        // Promote URL params into the criteria so getQuery() can read them
        NativeQueryHandler.super.preProcess(criteria, ctx);
    }

    @Override
    public NativeQuery getQuery(FilterCriteria criteria, HandlerContext ctx) {
        // Build the filter map from whatever the client passed
        Map<String, Object> filters = new LinkedHashMap<>();
        String stage = ctx.getParams().get("stage");
        String deptId = ctx.getParams().get("departmentId");
        if (stage != null)  filters.put("p.stage", stage);
        if (deptId != null) filters.put("p.department_id", deptId);

        // Inject WHERE clauses into the base query
        String sql = QueryModifier.inject(DashboardQueries.PROJECTS_BY_DEPARTMENT, filters);

        NativeQuery q = new NativeQuery(sql);
        q.setCountQuery(QueryModifier.inject("""
            SELECT COUNT(*) FROM project p
            JOIN department d ON d.id = p.department_id
        """, filters));
        q.setFetchSize(500);
        return q;
    }

    @Override
    public Tuple onQueryResult(Tuple tuple, Action action) {
        // Post-process each row — e.g. format currency
        BigDecimal total = (BigDecimal) tuple.get("total_value");
        if (total != null) tuple.set("total_value", total.setScale(2, RoundingMode.HALF_UP));
        return tuple;
    }
}

This gives you:

Pagination — the client passes _limit, _offset, _total and the framework handles it.
Row-level hooks — onQueryResult runs on every row, so you can format, redact, or derive fields.
ACL — aclCheck and @Permission protect the endpoint the same way they protect any handler.
Dynamic WHERE — QueryModifier.inject(...) composes the SQL without string concatenation.

When to use NativeQueryHandler vs. a custom controller#

Use `NativeQueryHandler` + JSqlParser when…	Use a custom `@RestController` + JSqlParser when…
You need Palmyra’s pagination (`_limit`/`_offset`/`_total`)	The response shape doesn’t fit the `{ result, limit, offset, total }` envelope
You want `onQueryResult` for per-row transforms	The endpoint returns a non-list shape (a single aggregate, a tree, a file)
`@Permission` / `aclCheck` should protect the endpoint	You need full control over the HTTP response (headers, status codes, streaming)
The client is a Palmyra frontend grid / chart store	The client is a third-party system expecting a custom schema

Both approaches use the same QueryModifier and the same base-query constants. The choice is about what surrounds the SQL — Palmyra’s handler lifecycle or Spring’s raw controller model.

Gradle dependency#

implementation 'com.github.jsqlparser:jsqlparser:5.0'