JDBC List Pattern

Overview

A JDBC OsidList executes a caller-supplied PreparedStatement and streams the rows of its result set as OsidObjects. It is built on the buffered list pattern: a producer virtual thread walks the JDBC ResultSet, translating each row through a supplied generator, and filling the bounded buffer. Reading from the result set pauses when the number of buffered elements reaches bufferSize and resumes as elements are retrieved, so a large result set never lands in memory all at once.

The statement is taken already prepared and bound, never assembled from a query string. Parameters are set through JDBC placeholders rather than concatenated into SQL, so a caller cannot open an injection hole through this list — the list executes only what the caller prepared.

A JDBC list is obtained through a static execute() factory rather than a constructor. The statement is executed before the factory returns — a bad connection or malformed SQL surfaces immediately as an OperationFailedException at the call site — and the producer thread is buffering rows by the time the caller holds the list. A failure while reading rows mid-stream is held and raised on the consumer side as an OperationFailedException at the next read.

Javadoc Reference

net.okapia.osid.streams.spi

Class Declaration Pattern

public final class JDBCOsidObjectList
extends AbstractBufferedOsidList<OsidObject>
implements OsidObjectList

Factory Pattern

public static JDBCOsidObjectList execute(Connection connection, PreparedStatement statement, JDBCOsidObjectGenerator<OsidObject> generator)

public static JDBCOsidObjectList execute(Connection connection, PreparedStatement statement, JDBCOsidObjectGenerator<OsidObject> generator, boolean closeWhenDone, int bufferSize)

The short form closes the connection when the result set is exhausted and uses a default buffer size of 256.

Parameter	Type	Description
`connection`	`Connection`	the connection the statement was prepared on
`statement`	`PreparedStatement`	a prepared, bound statement
`generator`	`JDBCOsidObjectGenerator<OsidObject>`	a generator to translate a result row into an `OsidObject`
`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

@FunctionalInterface public interface JDBCOsidObjectGenerator<T>
T make(ResultSet resultSet)

The generator translates the current row of the result set into an OSID object — typically a lambda that reads column values and populates an object builder. The generator reads from the supplied result set but must not move the cursor; the source owns cursor positioning.

Usage

Any object implementing JDBCOsidObjectGenerator<OsidObject> may be passed, allowing a row translator to be defined once (such as in your OsidObject definition) and reused across queries:

PreparedStatement statement = connection.prepareStatement(
    "SELECT * FROM billings WHERE catalog_id = ?");
statement.setString(1, catalogId);

org.osid.acknowledgement.BillingList billings =
    JDBCBillingList.execute(connection, statement, new BillingGenerator());

The list invokes the generator's make() method from its producer thread, so a shared generator should be stateless or otherwise safe to call from another thread. The generator is passed a single result row to extract and make the OsidObject to be returned from this OsidList.

The query has already been executed when execute() returns, and rows are buffering on the producer thread while the consumer processes each element. With closeWhenDone defaulted to true, the connection is closed once the result set is drained or the list is closed.

Alternative syntax using a lambda with the row translation written inline to make it more confusing.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM billings");

org.osid.acknowledgement.BillingList billings =
    JDBCBillingList.execute(connection, statement, results -> {
        return (new BillingBuilder()
              .id(BasicId.valueOf(results.getString("id")))
              .displayName(Plain.valueOf(results.getString("display_name")))
              .description(Plain.valueOf(results.getString("description")))
              .build());
    });

Another alternative syntax using lambdas and an implementation of a method called makeBilling, keeping the call site to a single expression which may be easier to read but people will have to figure out where you defined it:

PreparedStatement statement = connection.prepareStatement("SELECT * FROM billings");

org.osid.acknowledgement.BillingList billings =
    JDBCBillingList.execute(connection, statement,
                            results -> makeBilling(results));

Or, you can go back to option 1 and simply implement JDBCOsidObjectGenerator in your OsidObject so people can know how it was built. No, we're not boomers.