Friday, December 4, 2009

Handling BLOB/CLOB in postgreSQL with JDBC/Hibernate

Handling BLOB/CLOB in postgreSQL with JDBC/Hibernate

What is a BLOB/CLOB? It is a large object (binary for BLOB and char for CLOB). It is commonly used when you're storing large files into the database as column values.

It was really a challenge to work with BLOB/CLOB and in the process I almost pulled my hair out. Below are the hard lessons learned:

  • Avoid using LOB if possible! If say, it is just a few KB in size, just treat it as a string or byte array.
  • You must NOT try to read the stream outside the transaction. Note that using the open session in view is NOT enough. Once the transaction is ended, even if the Hibernate session is still open, you still can't read it (you'll get the "invalid large object descriptor" exception).
  • You must NOT try to read a LOB twice in the transaction. The "pointer" can't seem to be reset so you read past some portion, you can't read it again.
  • I still haven't figured this one out: If you repeatedly read a row containing a LOB in different transactions, it may cause the same "invalid large object descriptor" when you try to read it in a new Hibernate session.