« OC4J, Spring and messaging
Quick ‘n’ dirty - manipulate GIF in Java »

Oracle CLOB / BLOB & Spring

Reading and writing CLOBs and BLOBs from/to Oracle database via Spring's JdbcTemplate might be a little bit confusing as it significantly differs from dealing with other types. Interested? Read on... :-)

Reading BLOB from database

This is relatively simple. Just use ResultSet.getBlob() or ResultSet.getClob() method (usually in our custom Spring's RowMapper implementation) and read data using getBinaryStream() (for BLOBs) or getCharacterStream() (for CLOBs).

Inserting BLOB (CLOB) to database

Inserting must be done in two steps. First, we insert an empty BLOB (CLOB) into table row (along with other columns). Second, we lock & select currently inserted row from the table, open the stream and write data.

It is good to do both steps in one transaction (e.g. to prevent somebody from seeing empty BLOB in the meantime - but be sure to take transaction isolation level into account - READ COMMITTED or higher level is needed).

Let's say we declare jdbcTemplate bean in our spring.xml:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg>
        <ref bean="dataSource" />
    </constructor-arg>
</bean>

where dataSource is (got from JNDI):

<bean id="dataSource"
    class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName">
        <value>some/jndi/name</value>
    </property>
</bean>

Running both steps of BLOB (CLOB) insertion code in transaction can be done like this:

byte[] data = new byte[] { ... };
TransactionTemplate txTemplate = new TransactionTemplate(transactionManager);
MyTransaction transaction = new MyTransaction(data);
try {
    txTemplate.execute(transaction);
    //there might be something like: return transaction.getResult();
} catch (Exception e) {
    //error... handle with care ;-)
}

Where MyTransaction for inserting BLOB could be (Java 1.4 this time):

private class MyTransaction extends TransactionCallbackWithoutResult {
    private byte[] blobData;
       
    public InsertPacketTransaction(byte[] blobData) {
        this.blobData = blobData;
    }

    public void doInTransactionWithoutResult(TransactionStatus status) {
        String sql = "insert into MYTABLE (MYID, ..., MYBLOB) values (?, ..., ?)";

        Integer myId = 0; //obtain ID for new record
        BLOB blob = null;
           
        try {
            blob = BLOB.empty_lob();
        } catch (SQLException e) {
            //probably shouldn't happen
        }
           
        int types[] = new int[] { Types.INTEGER, ..., Types.BLOB };
        Object args []= new Object[] { myId , ..., blob };
           
        jdbcTemplate.update(sql, args, types);

        List packetList = jdbcTemplate.query(
            "select MYBLOB from MYTABLE where MYID = ? FOR UPDATE",
            new Object[]{ myId },
            new int[] { Types.INTEGER },
            new RowMapper() {
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getBlob("MYBLOB");
                }
            }
        );

        if (packetList.size() == 1) {
            OutputStream os;
            try {
                os = ((BLOB)packetList.get(0)).getBinaryOutputStream();
                os.write(blobData);
                os.flush();
                os.close();
            } catch (SQLException e) {
                //handle
            } catch (IOException e) {
                //handle
            }
        } else {
            //handle - this shouldn't happen
        }
    }
}

Writing CLOB is very similar - just use oracle.sql.CLOB instead of oracle.sql.BLOB everywhere and write data to CLOB using getCharacterOutputStream() method.

Tags: , , , , ,

This entry was posted on Sunday, February 24th, 2008 at 12.02 pm and is filed under Java. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “Oracle CLOB / BLOB & Spring”

  1. Lukas Barton Says:
    2008-03-31 at 10.32 am

    Why don’t you use native Oracle blob manipulations? (i.e. oracle.sql.BLOB) The code would be shorter and without arbitrary select.

  2. ludek Says:
    2008-03-31 at 11.10 am

    Hi, I would like to see such code, can you please provide an example?

  3. Lukas Barton Says:
    2008-03-31 at 12.45 pm

    See http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/oralob.htm#1043220 and http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html

Leave a Reply