JDBC Grouped List Pattern

Overview

A JDBC Grouped OsidList is the grouping counterpart of the JDBC list pattern. Where a JDBC list maps each result row to one OsidObject, a grouped list maps the several rows that share a primary key to a single OsidObject — the shape a query takes when an object is spread across rows, whether from a recursive WITH (CTE) expansion, a joined one-to-many, or a localizable text table.

It is built on the same buffered list pattern: a producer virtual thread walks the ResultSet, but instead of translating one row it gathers a run of rows sharing a key, hands them to a generator, and buffers the object the generator returns. Everything else — the bounded buffer, backpressure, eager execution, mid-stream error handling — is identical to the JDBC list pattern.

The query must order its rows so that the rows of one primary key are adjacent. Only neighbouring rows are gathered, so an unordered query yields one object per run of rows rather than one per key — quietly, since scattered rows are indistinguishable from separate objects. An ORDER BY on the grouping key (or a CTE that emits rows already grouped) is what makes the result well-formed.

As with the JDBC list, the statement is taken already prepared and bound; the list executes only what the caller prepared, never a query string.

Javadoc Reference

net.okapia.osid.streams

Class Declaration Pattern

public final class JDBCGroupedOsidObjectList
extends AbstractBufferedOsidList<OsidObject>
implements OsidObjectList

Factory Pattern

public static <R extends Keyed> JDBCGroupedOsidObjectList execute(Connection connection, PreparedStatement statement, JDBCOsidGroupGenerator<OsidObject, R> generator)

public static <R extends Keyed> JDBCGroupedOsidObjectList execute(Connection connection, PreparedStatement statement, JDBCOsidGroupGenerator<OsidObject, R> generator, boolean closeWhenDone, int bufferSize)

The short form closes the connection when the rows are exhausted and uses a default buffer size of 256. The row type R is inferred from the generator; the list itself never exposes it.

Parameter	Type	Description
`connection`	`Connection`	the connection the statement was prepared on
`statement`	`PreparedStatement`	a prepared, bound statement ordered so the rows of one object are adjacent
`generator`	`JDBCOsidGroupGenerator<OsidObject, R>`	a generator that reads rows and assembles an `OsidObject` from those sharing a key
`closeWhenDone`	`boolean`	`true` if the connection should be closed following the database transaction, `false` to leave it open (optional, defaults to `true`)
`bufferSize`	`int`	the capacity of the bounded buffer (optional, defaults to 256)

Generator Pattern

public interface JDBCOsidGroupGenerator<T, R extends Keyed>
R read(ResultSet resultSet)
T make(List<R> rows)

Where a one-row generator is a single make(ResultSet), a group generator is two steps. First read() snapshots the current row into an R — reading its column values without moving the cursor, which the source owns. Then make() receives the list of rows the source gathered for one key, in the order they arrived, and assembles the object. Whatever the object needs beyond its rows — an authority, a locale, a genus — the generator holds, so neither the list nor the source has to carry it.

The Keyed Row

public interface Keyed
Object key()

The row snapshot R carries the key it groups on: the source gathers rows for as long as their key() values compare equal, then hands the run to the generator. The key belongs to the row rather than to whatever reads it — the snapshot already holds the column, so it answers for its own group. Any type will do so long as equal keys are equals()-equal.

Usage

A closure table expands each requisite's rule tree into rows sharing the tree's root_id, ordered so a tree's rows are adjacent. The row snapshot carries that root as its key; the generator gathers a tree's rows and assembles one Requisite:

PreparedStatement statement = connection.prepareStatement("""
    SELECT root_id, node_id, parent_id, rule_type, operand
      FROM requisite_closure
     WHERE catalog_id = ?
     ORDER BY root_id, depth""");
statement.setString(1, catalogId);

org.osid.course.requisite.RequisiteList requisites =
    JDBCGroupedRequisiteList.execute(connection, statement,
                                     new RequisiteGenerator(authority, locale));

Where the RequisiteGenerator snapshots each row and assembles a requisite from the rows of one tree:

final class RequisiteRow implements Keyed {
    final long rootId, nodeId, parentId;
    final String ruleType, operand;
    // ... populated from the ResultSet ...
    public Object key() { return (this.rootId); }
}

final class RequisiteGenerator
    implements JDBCOsidGroupGenerator<Requisite, RequisiteRow> {

    public RequisiteRow read(ResultSet rs) throws SQLException {
        return (new RequisiteRow(rs));
    }

    public Requisite make(List<RequisiteRow> rows) {
        // rows are one tree, in ORDER BY order — assemble one Requisite
        return (new RequisiteBuilder()...build());
    }
}

The source reads one row ahead so it knows whether another object remains, gathers each run of equal-keyed rows, and offers the assembled object — several rows collapsing into one element for the buffered list to stream. A grouped list closes on close() or interrupt like any buffered list: the query is cancelled so a reader blocked on the database returns, and the connection is released.

JDBC Grouped OsidList Pattern