B/CLOB handling in jOOQ

Question

Here's what the jOOQ manual has to say about the subject:

jOOQ currently doesn't explicitly support JDBC BLOB and CLOB data types. If you use any of these data types in your database, jOOQ will map them to byte[] and String instead. In simple cases (small data), this simplification is sufficient. In more sophisticated cases, you may have to bypass jOOQ, in order to deal with these data types and their respective resources.

Is there a way to bypass some of jOOQ, but still keep the code relatively clean? Binding LOB parameters manually but leaving everything else to jOOQ for example?

And what are the possible obvious pitfalls I should avoid?

Answer 1

I found two solutions for this.

1. The Spring approach

2. Non-Spring approach

In the first case, just inject a JdbcTemplate and use this helper code:

public static LobHandler getAppropriateLobHandler(Factory factory) {
    LobHandler lobHandler = null;
    switch( factory.getDialect() ) {
        case ORACLE: lobHandler = new OracleLobHandler(); break;
        default: lobHandler = new DefaultLobHandler(); break;
    }
    return lobHandler;
}

Use this code to read BLOBs:

    return jdbcTemplate.queryForObject( 
        "select BLOB from TABLE where PK = ?",
        args,
        new RowMapper<InputStream>() {

            @Override
            public InputStream mapRow( ResultSet rs, int rowNum ) throws SQLException {
                return lobHandler.getBlobAsBinaryStream( rs, 1 );
            }
        }
    );

and this to write them:

   jdbcTemplate.execute(
            "update TABLE set BLOB = ? where PK = ?",
            new AbstractLobCreatingPreparedStatementCallback( lobHandler ) {

                @Override
                protected void setValues( PreparedStatement ps, LobCreator lobCreator ) throws SQLException {
                    lobCreator.setBlobAsBinaryStream( ps, 1, stream, sizeInBytes );
                    ps.setLong( 2, pk );
                }
            }
        );

In the second case, use JDBC to read and write the BLOBs unless you have Oracle. When you have Oracle, you need to use their special C/BLOB locator pattern.