Using Large Objects

As of version 1.3.x, Fluent JDBC provides basic support for creating, updating, and retrieving large objects using a JDBC driver that includes the large object support included in JDBC 4 (Java 6).

• Inserting or Updating Large Objects
• Retrieving Large Objects

Inserting or Updating Large Objects

To include a large object in an insert or update operation, you provide a handler for the associated statement parameter, using Parameter.with.

Suppose we create a table with a single BLOB column.

jdbc.execute("CREATE TABLE lob_demo (id INTEGER, lob_data BLOB )");

We can insert a row into the table as follows.

jdbc.update()
    .using("INSERT INTO lob_demo(id, lob_data) VALUES(?, ?)")
    .execute(
        Parameter.with(1), 
        Parameter.with(new BlobHandler() {
            public void handleBlob(Blob blob) throws SQLException {
              blob.setBytes(1, "this is just some sample text".getBytes())
            }
        )
    );

The handler receives a Blob object that can be manipulated as needed to set the contents of the BLOB column. After your handler returns, the resulting Blob is set as the value of the associated statement parameter. The same approach can also be used in an UPDATE statement.

The ClobHandler and NClobHandler interfaces can be used to specify handlers for columns of type CLOB and NCLOB.

Retrieving Large Objects

Using either the handlingResultWith or mappingRowsWith method with the Fluent JDBC query builder, you can retrieve values for large objects using any of the large object methods on the JDBC ResultSet that is passed to your handler.

When using the extractingColumn method with your query, you can return a large object type directly. The following example shows how to retrieve the BLOB column in our example table.

Blob blob = jdbc.queryForType(Blob.class)
        .using("SELECT lob_data FROM lob_demo WHERE id = ?")
        .extractingColumn()
        .retrieveValue(Parameter.with(1));