PagedList: Lazy Paginated Views

PagedList<T> is a column-based, on-demand paginated list backed by the database. It targets UI scenarios — data grids, dropdown pickers, search screens — where you want to expose a potentially very large result set without materializing it in memory, while still supporting per-column filtering, sorting, and foreign-key traversal.

It implements kotlin.collections.AbstractList<T> (and therefore Java's List<T>), so every get(index), size, and iterator() call Just Works — the list loads pages behind the scenes.

Quick Start

Construction takes only the entity type. The [Stormify] instance is resolved from the default instance on first access, so the same class works identically from Kotlin and Java. If you are juggling multiple Stormify instances in the same process, use stormify.attach(...) at construction time to bind the list to a specific one.

KotlinJava

import onl.ycode.stormify.biglist.Column
import onl.ycode.stormify.biglist.PagedList
import db.stormify.Company_   // KSP-generated typed paths

val list = PagedList<Company>()
// val list = stormify.attach(PagedList<Company>())    // when not using a default instance

list.addColumn(Company_.name)                                 // auto-detect TEXT
list.addColumn(Company_.contactPerson.firstName,              // OR across FK-traversed fields
               Company_.contactPerson.lastName)
list.addRawColumn("SUM(order_total)", Column.NUMERIC)

list.getColumn(0).filter = "Acme"
list.getColumn(1).sort = Column.ASCENDING

// Consume as a normal List
val firstCompany = list[0]                                    // triggers page load
val totalMatches = list.size                                  // triggers COUNT query
for (company in list) println(company.name)                   // streams page-by-page

import onl.ycode.stormify.biglist.Column;
import onl.ycode.stormify.biglist.PagedList;
import db.stormify.Company_;   // KSP-generated typed paths

PagedList<Company> list = new PagedList<>(Company.class);
// PagedList<Company> list = stormify.attach(new PagedList<>(Company.class));  // when not using a default instance

list.addColumn(Company_.name);                                 // auto-detect TEXT
list.addColumn(Company_.contactPerson().firstName,             // OR across FK-traversed fields
               Company_.contactPerson().lastName);
list.addRawColumn("SUM(order_total)", Column.NUMERIC);

list.getColumn(0).setFilter("Acme");
list.getColumn(1).setSort(Column.ASCENDING);

// Consume as a normal List
Company firstCompany = list.get(0);                            // triggers page load
int totalMatches = list.size();                                // triggers COUNT query
for (Company c : list) System.out.println(c.getName());        // streams page-by-page

Enabling Type-Safe Paths

Typed paths like Company_.name are generated by the annproc KSP processor. Add it to your build (once; the same processor also generates entity metadata):

Gradle (Kotlin)Gradle (Java)

plugins {
    id("com.google.devtools.ksp") version "2.2.20-2.0.2"
}

dependencies {
    ksp("onl.ycode:annproc:2.0.0")
}

plugins {
    id 'com.google.devtools.ksp' version '2.2.20-2.0.2'
}

dependencies {
    ksp 'onl.ycode:annproc:2.0.0'
}

For each entity class Foo the processor emits a Foo_ object under the db.stormify package, with fields for each scalar property and nested objects for FK references. See Annotations for the full setup. On Native/Android/iOS, annproc is required anyway (for entity metadata) — you get typed paths for free.

Columns

A column is the unit of filtering and sorting. It can be backed by:

A single entity field
- addColumn(Company_.name)
A foreign-key path — auto-generates the JOIN
- addColumn(Order_.customer.name)
Any combination of the above — pass any number of paths on the same column; they OR-filter together, and you can freely mix scalar fields and FK paths in the same call:
- addColumn(Person_.firstName, Person_.lastName) → two scalar fields
- addColumn(Order_.notes, Order_.customer.name) → scalar field + FK path
- addColumn(Order_.customer.name, Order_.shippingAddress.city) → two FK paths
A raw SQL expression — for calculated columns
- addRawColumn("SUM(total)", Column.NUMERIC)

Filters between different columns use AND semantics; multiple paths within the same column use OR.

KotlinJava

val list = PagedList<Order>()
val nameCol   = list.addColumn(Order_.customer.name)       // FK traversal
val statusCol = list.addColumn(Order_.status)              // scalar
val rawCol    = list.addRawColumn("total * tax_rate", Column.NUMERIC)

nameCol.filter = "Acme"
statusCol.filter = "ACTIVE"
// Both filters active → WHERE customer.name LIKE %Acme% AND status = 'ACTIVE'

PagedList<Order> list = new PagedList<>(Order.class);
Column nameCol   = list.addColumn(Order_.customer().name);   // FK traversal
Column statusCol = list.addColumn(Order_.status);            // scalar
Column rawCol    = list.addRawColumn("total * tax_rate", Column.NUMERIC);

nameCol.setFilter("Acme");
statusCol.setFilter("ACTIVE");
// Both filters active → WHERE customer.name LIKE %Acme% AND status = 'ACTIVE'

String paths with dot notation work too (addColumn("customer.name")) — useful for cross-cutting code where a field is chosen at runtime. Mix both styles freely.

Column Types and Filter Syntax

Each column has a Column.Type that controls how the filter string is parsed. The type is auto-detected from the field's Kotlin type, or you can set it explicitly via the two-argument addColumn(type, ...) overload.

Text (`Column.TEXT`)

Case-insensitive by default. Supports several patterns:

Filter value	Meaning
`Alice`	Substring match: `LIKE %Alice%`
`*lice`	Ends with: `LIKE %lice`
`Ali*`	Starts with: `LIKE Ali%`
`"Alice"`	Exact match (still case-insensitive unless `isCaseSensitive = true`)

Set column.isCaseSensitive = true for a case-sensitive column.

Numeric (`Column.NUMERIC`)

Filter value	Meaning
`42`	Equals
`> 10`	Greater than
`>= 10`	Greater than or equal
`< 100`	Less than
`<= 100`	Less than or equal
`10 ... 20`	Range (inclusive)

Temporal (`Column.TEMPORAL`)

Covers LocalDate, LocalTime, LocalDateTime, Instant. Supports the same comparison operators and range syntax as NUMERIC. Values are parsed via the active input parser.

Enum (`Column.ENUM`)

For enum-backed fields. Auto-detected when the field's Kotlin type is an enum. The filter string is matched (case-insensitively, as a substring) against the enum's display names. Multiple matching display names produce an IN clause of the corresponding DB values.

KotlinJava

enum class Status { ACTIVE, INACTIVE, BANNED }

val list = PagedList<User>()
list.addColumn(User_.status)            // auto-detects as ENUM
list.getColumn(0).filter = "active"     // matches ACTIVE and INACTIVE

public enum Status { ACTIVE, INACTIVE, BANNED }

PagedList<User> list = new PagedList<>(User.class);
list.addColumn(User_.status);
list.getColumn(0).setFilter("active");

To customize the display names (e.g., localized UI strings), implement the HumanReadable interface on the enum. For non-enum fields that you want to treat as enums (or to override the auto-built map), pass a display-to-DB-value Map as the first argument to addColumn — the same method name, resolved to the enum-column overload via the first-argument type:

KotlinJava

val displayMap = mapOf("Ενεργός" to 1, "Ανενεργός" to 0)
list.addColumn(displayMap, User_.statusCode)

Map<String, Object> displayMap = Map.of("Ενεργός", 1, "Ανενεργός", 0);
list.addColumn(displayMap, User_.statusCode);

NULL Filter

Any column can filter for NULL using the sentinel constant Column.NULL:

KotlinJava

list.getColumn(0).filter = Column.NULL   // → WHERE name IS NULL

list.getColumn(0).setFilter(Column.NULL);   // → WHERE name IS NULL

Sorting

Set column.sort to Column.ASCENDING or Column.DESCENDING. Any number of columns can be active sorts at once; the list sorts by them in the order they were defined:

KotlinJava

list.getColumn(0).sort = Column.ASCENDING    // primary
list.getColumn(1).sort = Column.DESCENDING   // secondary

list.getColumn(0).setSort(Column.ASCENDING);
list.getColumn(1).setSort(Column.DESCENDING);

column.clearSort() deactivates the sort for a single column; list.reset() clears all filters and sorts at once (but keeps constraints — see below).

If no column has an active sort, rows are ordered by the entity's primary key. This requires a single-column PK; for composite keys you must explicitly set a sort on at least one column, otherwise the list throws IllegalStateException.

Constraints

Constraints are a fixed WHERE clause that is always applied, independent of column filters. Use them to scope the list to a sub-query (e.g., "only orders for user 42"):

KotlinJava

list.setConstraints("customer_id = ?", 42)

list.setConstraints("customer_id = ?", 42);

Constraints and column filters combine with AND. Unlike filters, constraints are not cleared by list.reset() — they're considered part of the list's fundamental definition.

Distinct Mode

Set list.isDistinct = true to make the underlying query use SELECT DISTINCT. This is useful when JOINs cause row duplication. Note that DISTINCT affects both size (which becomes COUNT(DISTINCT ...)) and page queries. Aggregations are not affected — getAggregator() ignores the flag.

Pagination and Caching

PagedList keeps one page of rows in memory at a time. pageSize defaults to 15 and can be changed:

KotlinJava

list.pageSize = 50

list.setPageSize(50);

Indexing outside the currently cached page triggers a new page load. The size property triggers a COUNT(*) query the first time it's read and caches the result. The list stays in sync with your configuration — filter, sort, constraint, and page-size changes are picked up automatically on the next access.

Stormify uses the underlying SQL dialect's pagination syntax (LIMIT/OFFSET, FETCH FIRST, ROWNUM, etc.) automatically.

Selected Entity

You can mark one entity as selected — it appears as the first element of the list regardless of any sort order. This is useful for UI patterns like "show the currently highlighted row at the top":

KotlinJava

list.selected = currentCompany
list[0]  // always currentCompany (when non-null)

list.setSelected(currentCompany);
list.get(0);  // always currentCompany (when non-null)

The helpers list.add(entity) and list.remove(entity) set or clear the selection as a side effect. Use them when you create/delete an entity and want the list to reflect the change immediately without resetting the user's filters:

KotlinJava

val company = stormify.create(Company(name = "Acme"))
list.add(company)        // appears first

stormify.delete(company)
list.remove(company)     // selection cleared

Company company = stormify.create(new Company("Acme"));
list.add(company);

stormify.delete(company);
list.remove(company);

Filter Values (Distinct Per-Column)

For populating dropdown pickers, call column.getFilterValues(). This returns a FilterValues — another lazy paginated list, but of the distinct values of that column, filtered by the other columns' active filters. The user only sees values that would actually return results under the current filter state.

KotlinJava

val statusCol = list.addColumn(User_.status)
val distinctStatuses = statusCol.getFilterValues()
distinctStatuses.forEach { println(it) }

Column statusCol = list.addColumn(User_.status);
FilterValues distinctStatuses = statusCol.getFilterValues();
distinctStatuses.forEach(System.out::println);

FilterValues stays in sync with the parent list — any change to filters or constraints is reflected on the next access.

Facet Counts (`withCounts`)

Some pickers want to show the value plus how many rows it would produce ("Category: Books (12), Movies (4)"). Call withCounts() on the FilterValues to get a sibling FilterCountedValues — same lazy pagination, but each element is a FilterCountedValue(value, count):

KotlinJava

val categoryCol = list.addColumn(Product_.category)
val counts = categoryCol.getFilterValues().withCounts()
for (entry in counts) println("${entry.value} (${entry.count})")

Column categoryCol = list.addColumn(Product_.category);
FilterCountedValues counts = categoryCol.getFilterValues().withCounts();
for (FilterCountedValue entry : counts)
    System.out.println(entry.getValue() + " (" + entry.getCount() + ")");

Repeated calls to withCounts() on the same FilterValues return the same instance, so you can share it across multiple reads without re-querying.

Input Parsing and Locale

Raw user input (e.g., from a text field) often uses locale-specific formats — numbers like 1.234,56 (comma decimal), dates like 31/12/2026 (day-first). PagedList applies an input parser — a (String, Column.Type) -> String function — that transforms the filter string before it reaches SQL.

Resolution order: column → list → global → identity (no transformation).

KotlinJava

// Global — applies to all PagedList instances unless overridden
PagedList.defaultInputParser = { input, type ->
    when (type) {
        Column.NUMERIC -> input.replace(".", "").replace(",", ".")
        Column.TEMPORAL -> flipDayMonthYear(input)
        else -> input
    }
}

// Per-list override
myList.inputParser = { input, type -> /* ... */ }

// Per-column override
myList.getColumn(0).inputParser = { input, type -> /* ... */ }

// Global — applies to all PagedList instances unless overridden
PagedList.setDefaultInputParser((input, type) -> {
    if (type == Column.NUMERIC) return input.replace(".", "").replace(",", ".");
    if (type == Column.TEMPORAL) return flipDayMonthYear(input);
    return input;
});

// Per-list override
myList.setInputParser((input, type) -> /* ... */);

// Per-column override
myList.getColumn(0).setInputParser((input, type) -> /* ... */);

Set to null (the default) to fall through to the next level.

Raw Columns

A raw column is backed by a SQL expression instead of an entity field — useful for calculated values, concatenations, or expressions no entity property maps to. There are two forms:

Simple — just an expression and a type

Pass the expression and the column type; the default converter decides the WHERE clause based on the type (LIKE ? for TEXT, = ? / BETWEEN ? AND ? for NUMERIC, date comparison for TEMPORAL, mapped equality for ENUM).

KotlinJava

list.addRawColumn("firstName || ' ' || lastName")              // default TEXT → LIKE
list.addRawColumn("SUM(total)", Column.NUMERIC)                // numeric → = or BETWEEN
list.addRawColumn("YEAR(created_at)", Column.NUMERIC)

list.addRawColumn("firstName || ' ' || lastName");             // default TEXT → LIKE
list.addRawColumn("SUM(total)", Column.NUMERIC);               // numeric → = or BETWEEN
list.addRawColumn("YEAR(created_at)", Column.NUMERIC);

This covers the majority of raw-column needs.

Custom — with a `SqlGenerator`

When the default converter isn't enough, pass a SqlGenerator — a SAM interface that produces the SQL fragment and stages bind parameters via a SqlArgsCollector. Use it when you need to:

Translate the input value in a non-trivial way (e.g. "50k" → 50000)
Emit more than one placeholder (e.g. BETWEEN ? AND ? with a computed upper bound)
Choose different SQL shapes based on the input format

KotlinJava

val col = list.addRawColumn("users.id", Column.NUMERIC) { column, value, args ->
    val n = value.toIntOrNull() ?: 0
    args.accept(n)
    "$column % ? = 0"    // rows whose id is divisible by n
}
col.filter = "3"         // keep ids divisible by 3

Column col = list.addRawColumn("users.id", Column.NUMERIC, (column, value, args) -> {
    int n = Integer.parseInt(value);
    args.accept(n);
    return column + " % ? = 0";
});
col.setFilter("3");

`HumanReadable` (Display Names)

Implement HumanReadable on enums (or any class) that participate in enum columns to provide localized display names:

KotlinJava

enum class Status(override val displayName: String) : HumanReadable {
    ACTIVE("Ενεργή"),
    INACTIVE("Ανενεργή"),
    BANNED("Αποκλεισμένη")
}

public enum Status implements HumanReadable {
    ACTIVE("Ενεργή"),
    INACTIVE("Ανενεργή"),
    BANNED("Αποκλεισμένη");

    private final String displayName;
    Status(String displayName) { this.displayName = displayName; }

    @Override public String getDisplayName() { return displayName; }
}

When the enum column auto-builds its display-name-to-DB-value map, it uses displayName instead of name. This is also what FilterValues returns for enum columns — so your dropdown shows the localized labels instead of ACTIVE/INACTIVE/BANNED.

Refreshing After External Changes

When you mutate data outside of the list's awareness — typically via stormify.create/update/delete — the list's cached row count and current page are unaware of the change. Call list.refresh() to force the next read to re-query:

KotlinJava

stormify.create(Company(name = "Acme"))
list.refresh()            // next access will re-query
println(list.size)        // includes the new row

stormify.create(new Company("Acme"));
list.refresh();           // next access will re-query
System.out.println(list.size());

refresh() is pure cache invalidation — there are no listeners to notify. After calling it, the list itself is ready, and you are responsible for whatever UI refresh your framework needs.

Streaming with `forEachStreaming`

A PagedList supports ordinary iteration (for (row in list)), but the index-based iterator walks page by page and issues one query per page — fine for small result sets, but wasteful for exports over tens of thousands of rows.

For efficient, one-query streaming use forEachStreaming, which runs a server-side cursor over the current filter / sort state.

KotlinJava

// Single query, streams every row regardless of page size.
list.forEachStreaming { row -> exportWriter.write(row) }

// Single query, streams every row regardless of page size.
list.forEachStreaming(row -> exportWriter.write(row));

for (row in list) and the plain list.forEach { … } / list.forEach(consumer) keep their page-by-page semantics — they resolve to the default kotlin.collections.Iterable.forEach / java.lang.Iterable.forEach and walk the index-based iterator one page at a time. Pick forEachStreaming when you want the single-query cursor behaviour.

Sibling-batch friendly. Under the hood, rows are forwarded to your callback in chunks matching the default sibling-batch size rather than one at a time. The buffering is invisible to your code (you still see a stream of single rows), but it guarantees that if your callback touches a foreign-key reference — row.customer.name, row.category.label — the first touch triggers a single batched lookup covering the whole chunk of siblings instead of issuing an individual follow-up query per row. No configuration, no API change — just the same forEachStreaming call behaving optimally for the common streaming-with-lazy-FK pattern. See Sibling Batch Optimization for the underlying mechanism.

Aggregations

list.getAggregator() returns a PagedAggregator — a fluent builder for SQL aggregate queries that respect the list's current constraints and per-column filters. isDistinct is ignored for aggregates.

The builder methods take a path (as String or typed ScalarPath) plus an optional alias. When alias is omitted the aggregator generates a unique one by combining the function name and the path (sum_revenue, count_id, countDistinct_category, …).

Single-Value Aggregation

Chain one method and call execute<R>() (or execute(Class<R>) from Java) to get a typed scalar:

KotlinJava

import java.math.BigDecimal

val total: BigDecimal? = list.getAggregator()
    .sum(Company_.revenue)
    .execute<BigDecimal>()

val rowCount: Long? = list.getAggregator()
    .count("*")
    .execute<Long>()

val categories: Long? = list.getAggregator()
    .countDistinct(Product_.category)
    .execute<Long>()

import java.math.BigDecimal;

BigDecimal total = list.getAggregator()
    .sum(Company_.revenue)
    .execute(BigDecimal.class);

Long rowCount = list.getAggregator()
    .count("*")
    .execute(Long.class);

Long categories = list.getAggregator()
    .countDistinct(Product_.category)
    .execute(Long.class);

Multi-Value Aggregation

Keep chaining aggregators to build a MultiAggregator — its execute() runs a single query and returns a Map<String, Any?> keyed by each aggregator's alias (auto-generated or explicit):

KotlinJava

val row = list.getAggregator()
    .sum(Company_.revenue, "total")     // → row["total"]
    .avg(Company_.revenue, "average")   // → row["average"]
    .min(Company_.revenue)              // → row["min_revenue"]
    .max(Company_.revenue)              // → row["max_revenue"]
    .count("*", "cnt")                  // → row["cnt"]
    .execute()

Map<String, Object> row = list.getAggregator()
    .sum(Company_.revenue, "total")        // → row.get("total")
    .avg(Company_.revenue, "average")      // → row.get("average")
    .min(Company_.revenue, null)           // → row.get("min_revenue")
    .max(Company_.revenue, null)           // → row.get("max_revenue")
    .count("*", "cnt")                     // → row.get("cnt")
    .execute();

Explicit aliases that collide with an already-added entry throw IllegalArgumentException. Auto-generated aliases never collide — repeating sum("revenue") gives sum_revenue, sum_revenue_2, sum_revenue_3, …

Raw Expressions

When the built-in functions are not enough, use raw(expression) to emit an arbitrary SQL fragment. Auto-generated aliases sanitize the expression (operators and parentheses become underscores, duplicates get a numeric suffix) so you don't have to come up with one yourself — although supplying an explicit alias is always an option.

KotlinJava

// SUM(qty * price) respecting current filters
val revenue = list.getAggregator()
    .raw("SUM(order_item.qty * order_item.price)")
    .execute<BigDecimal>()

// Multi with a raw alongside a typed aggregator
val row = list.getAggregator()
    .sum(Order_.total, "total")                                 // → row["total"]
    .raw("SUM(order.total * tax_rate)", "total_with_tax")       // → row["total_with_tax"]
    .raw("AVG(order.discount)")                                 // → row["AVG_order_discount"]
    .execute()

// SUM(qty * price) respecting current filters
BigDecimal revenue = list.getAggregator()
    .raw("SUM(order_item.qty * order_item.price)", null)
    .execute(BigDecimal.class);

// Multi with a raw alongside a typed aggregator
Map<String, Object> row = list.getAggregator()
    .sum(Order_.total, "total")                                 // → row.get("total")
    .raw("SUM(order.total * tax_rate)", "total_with_tax")       // → row.get("total_with_tax")
    .raw("AVG(order.discount)", null)                           // → row.get("AVG_order_discount")
    .execute();

Saving and Restoring State

For navigation flows where the user leaves a grid / picker screen and comes back, use saveState() / restoreState(). They capture the per-column filters, sorts, case-sensitivity flags, the page size, and the distinct flag into a plain PagedListState data object — easy to persist in a view model or session store:

KotlinJava

// Before leaving the screen
val state: PagedListState = list.saveState()
viewModel.savedListState = state

// When coming back
val list = PagedList<Company>()
list.addColumn(Company_.name)
list.addColumn(Company_.revenue)
viewModel.savedListState?.let { list.restoreState(it) }

// Before leaving the screen
PagedListState state = list.saveState();
viewModel.savedListState = state;

// When coming back
PagedList<Company> list = new PagedList<>(Company.class);
list.addColumn(Company_.name);
list.addColumn(Company_.revenue);
if (viewModel.savedListState != null)
    list.restoreState(viewModel.savedListState);

restoreState() is permissive: column keys present in the saved state but missing from the current list are silently ignored (so your list can add/remove columns between sessions without blowing up). Constraints, the selected entity, and input parsers are not part of the state — they are structural or ephemeral, not user-visible choices.

State keys are derived from each column's paths (raw columns use their SQL expression; field columns use their paths joined alphabetically), so the order in which paths were passed to addColumn does not affect the key.

PagedList: Lazy Paginated Views

Quick Start

Enabling Type-Safe Paths

Columns

Column Types and Filter Syntax

Text (Column.TEXT)

Numeric (Column.NUMERIC)

Temporal (Column.TEMPORAL)

Enum (Column.ENUM)

NULL Filter

Sorting

Constraints

Distinct Mode

Pagination and Caching

Selected Entity

Filter Values (Distinct Per-Column)

Facet Counts (withCounts)

Input Parsing and Locale

Raw Columns

Simple — just an expression and a type

Custom — with a SqlGenerator

HumanReadable (Display Names)

Refreshing After External Changes

Streaming with forEachStreaming

Aggregations

Single-Value Aggregation

Multi-Value Aggregation

Raw Expressions

Saving and Restoring State

Text (`Column.TEXT`)

Numeric (`Column.NUMERIC`)

Temporal (`Column.TEMPORAL`)

Enum (`Column.ENUM`)

Facet Counts (`withCounts`)

Custom — with a `SqlGenerator`

`HumanReadable` (Display Names)

Streaming with `forEachStreaming`