« 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:

PLAIN TEXT
XML:
  1. <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  2.     <constructor-arg>
  3.         <ref bean="dataSource" />
  4.     </constructor-arg>
  5. </bean>

where dataSource is (got from JNDI):

PLAIN TEXT
XML:
  1. <bean id="dataSource"
  2.     class="org.springframework.jndi.JndiObjectFactoryBean">
  3.     <property name="jndiName">
  4.         <value>some/jndi/name</value>
  5.     </property>
  6. </bean>

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

PLAIN TEXT
JAVA:
  1. byte[] data = new byte[] { ... };
  2. TransactionTemplate txTemplate = new TransactionTemplate(transactionManager);
  3. MyTransaction transaction = new MyTransaction(data);
  4. try {
  5.     txTemplate.execute(transaction);
  6.     //there might be something like: return transaction.getResult();
  7. } catch (Exception e) {
  8.     //error... handle with care ;-)
  9. }

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

PLAIN TEXT
JAVA:
  1. private class MyTransaction extends TransactionCallbackWithoutResult {
  2.     private byte[] blobData;
  3.        
  4.     public InsertPacketTransaction(byte[] blobData) {
  5.         this.blobData = blobData;
  6.     }
  7.  
  8.     public void doInTransactionWithoutResult(TransactionStatus status) {
  9.         String sql = "insert into MYTABLE (MYID, ..., MYBLOB) values (?, ..., ?)";
  10.  
  11.         Integer myId = 0; //obtain ID for new record
  12.         BLOB blob = null;
  13.            
  14.         try {
  15.             blob = BLOB.empty_lob();
  16.         } catch (SQLException e) {
  17.             //probably shouldn't happen
  18.         }
  19.            
  20.         int types[] = new int[] { Types.INTEGER, ..., Types.BLOB };
  21.         Object args []= new Object[] { myId , ..., blob };
  22.            
  23.         jdbcTemplate.update(sql, args, types);
  24.  
  25.         List packetList = jdbcTemplate.query(
  26.             "select MYBLOB from MYTABLE where MYID = ? FOR UPDATE",
  27.             new Object[]{ myId },
  28.             new int[] { Types.INTEGER },
  29.             new RowMapper() {
  30.                 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
  31.                     return rs.getBlob("MYBLOB");
  32.                 }
  33.             }
  34.         );
  35.  
  36.         if (packetList.size() == 1) {
  37.             OutputStream os;
  38.             try {
  39.                 os = ((BLOB)packetList.get(0)).getBinaryOutputStream();
  40.                 os.write(blobData);
  41.                 os.flush();
  42.                 os.close();
  43.             } catch (SQLException e) {
  44.                 //handle
  45.             } catch (IOException e) {
  46.                 //handle
  47.             }
  48.         } else {
  49.             //handle - this shouldn't happen
  50.         }
  51.     }
  52. }

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. Responses are currently closed, but you can trackback from your own site.

3 Responses to “Oracle CLOB / BLOB & Spring”

  1. Lukas Barton says:
    March 31, 2008 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:
    March 31, 2008 at 11:10 am

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

  3. Lukas Barton says:
    March 31, 2008 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