Working with Blobs and Clobs

Working with Blobs and Clobs

There are a number of issues that have to be taken into consideration when working with Large Objects like Blobs and Clobs.

• Blob and Clob object methods often throw checked SQLExceptions and because of that, it is awkward to use them in lambdas commonly used in streams.
• Blob and Clob objects are only valid within the stream and cannot be used directly outside the stream.

Work Arounds

There are several ways of handling LOBs within Speedment.

• Use a column TypeMapper and convert a Blob to a byte[] or a Clob to a String. This makes it much more easy to work with the data but the drawback is that the Blob and Clob are eagerly converted regardless of being used or not in the stream.
• Restrict the usage of LOBs within the stream and materialize the LOBs to another object if used. Use wrapper methods that can access the LOBs and that can wrap the SQLException into a RuntimeException. A Blob can be materialized using a SerialBlob and a Clob can be materialized using a SerialClob.

Examples

Assuming we have an object that handles a Blob like this using its C2 column:

public interface LargeObjectsDatatypes {
    Optional<Blob> getC2();
    LargeObjectsDatatypes setC2(Blob c2);
}

we can handle the Blob within the stream like this:

    manager.stream().forEach(bt -> {
        bt.getC2().ifPresent(b -> {
            try {
                System.out.println("length = " + b.length());
            } catch (SQLException sqle) {
                throw new RuntimeException(sqle);
            }
        }
        );
    });

Because the Blob does not escape the stream (it is only used within the stream's forEach() method) it can be used directly.

If we want to use a Blob outside a stream, we can do like this:

final Optional<Blob> blob = manager.stream()
        .map(this::copyBlob) // Create a materialized Blob
        .findAny()
        .flatMap(LargeObjectsDatatypes::getC2);

// Wrapper method
private LargeObjectsDatatypes copyBlob(LargeObjectsDatatypes bt) {
    try {
        final Optional<Blob> oldBlob = bt.getC2();
        final Blob newBlob;
        if (oldBlob.isPresent()) {
            newBlob = new SerialBlob(oldBlob.get());
        } else {
            newBlob = null;
        }
        bt.setC2(newBlob);
        return bt;
    } catch (SQLException sqle) {
        throw new RuntimeException(sqle);
    }
}