CLOB and BLOB saved through Hibernate

It’s been a while. I haven’t got much time recently. This past week, I did find something worth blogging. We are trying to save files into oracle. We could either do CLOB or BLOB, and we decided to use hibernate. First I thought it would be kind of difficult. It turned out it was not that bad.

Essentially, three things to take care of before you can save CLOB or BLOB to oracle ( we use 10g).

(1) mapping file:

<?xml version=”1.0″?>
<!DOCTYPE hibernate-mapping PUBLIC
“-//Hibernate/Hibernate Mapping DTD 3.0//EN””&gt;

<class name=”MessageFile” table=”MessageFile”>
<id name=”id” column=”id”>
<generator class=”native”/>
<property name=”name”/>
<property name=”file_blob”/>
<property name=”file_clob”/>

(2) hibernate.cfg.xml  to include the mapping:

<mapping resource=”MessageFile.hbm.xml”/>

(3) Java code for Clob/Blob:

// Configuration
Configuration config = new Configuration().configure();
SessionFactory sessionFactory = config.buildSessionFactory();

MessageFile mfile = new MessageFile();
mfile.setFile_clob(Hibernate.createClob(” “));  // cannot be blank, needs one char
mfile.setFile_blob(Hibernate.createBlob(new byte[1]));  // cannot be empty array, needs one elem

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();;

// now update and put CLOB/BLOB in
session.refresh(mfile, LockMode.UPGRADE);

FileInputStream fileInputStream = new FileInputStream(“c:\\temp\\sample.txt”);

File clobFile = new File(“c:\\temp\\sample.txt”);
FileReader clobFileReader = new FileReader(new File(“c:\\temp\\sample.txt”));

mfile.setFile_clob(Hibernate.createClob(clobFileReader, (int) clobFile.length()));




Now confirm the LOB’s in Oracle by:

SELECT name, file_clob, utl_raw.cast_to_varchar2(file_blob) from Messagefile;

Remember, the query may not work well in sqlPlus due to buffer limit. Nevertheless,  reading CLOB/BLOB from the table using Hibernate is not hard.

The good thing with this is, there is no need to use any oracle specific class, at least with hiberate 3.