NativeQuery#

com.zitlab.palmyra.store.NativeQuery

Overview#

Value object that carries a hand-written SQL statement plus its positional parameters, optional count query, and paging controls. Implements QueryOptions and CountQuery. Built and returned from NativeQueryHandler.getQuery(...).

params are positional ? placeholders (JDBC-style) — bind them in order. Set countQuery when pagination needs a distinct count different from a naive COUNT(*) over the main query.

Methods#

Method	Signature
`NativeQuery`	`NativeQuery()` · `NativeQuery(String query)` · `NativeQuery(String query, List<Object> params)` · `NativeQuery(String query, Object... params)`
`getQuery` / `setQuery`	`String getQuery()` · `void setQuery(String query)`
`getParams` / `setParams`	`List<Object> getParams()` · `void setParams(List<Object> params)`
`addParams`	`void addParams(Object param)` · `void addParams(Object... params)`
`clearParam`	`void clearParam()`
`hasParams`	`boolean hasParams()`
`getCountQuery` / `setCountQuery`	`String getCountQuery()` · `void setCountQuery(String countQuery)`
`getFetchSize` / `setFetchSize`	`int getFetchSize()` · `void setFetchSize(int fetchSize)` — default 100
`getLimit` / `setLimit`	`int getLimit()` · `void setLimit(int limit)`
`getOffset` / `setOffset`	`int getOffset()` · `void setOffset(int offset)`
`limitFirst`	`void limitFirst()`

Example#

@Override
public NativeQuery getQuery(FilterCriteria criteria, HandlerContext ctx) {
    String sql = """
        SELECT u.id, u.name, SUM(o.amount) total
        FROM users u JOIN orders o ON o.user_id = u.id
        WHERE o.created_at >= ?
        GROUP BY u.id, u.name
    """;
    NativeQuery q = new NativeQuery(sql, criteria.getSimpleCriteria().get("since"));
    q.setCountQuery("""
        SELECT COUNT(DISTINCT u.id)
        FROM users u JOIN orders o ON o.user_id = u.id
        WHERE o.created_at >= ?
    """);
    q.setFetchSize(500);
    return q;
}