Oracle

Character lob

A character lob is very simple, if you use can use Oracle 10 or Oracle XE (Express Edition). Oracle the setString() method of the prepared statement had a limitation of 32756 bytes = about 4000 characters. You can find additional documentation on the Oracle website. http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html The class has a simple String field:

   private String text;

Annotation mapping: Just add a @Lob annotation to a string field. That’s all.

import javax.persistence.Entity;
import javax.persistence.Lob;
..... snip ......
@Entity
public class Document implements Serializable {
...... snip .......
   @Lob
   private String text;

XML mapping

There is one caveat, if we use XML. You must specify the type or Hibernate will generate a varchar column instead of text, if you let Hibernate generate your tables.

    <property name="text" type="text"></property>

Oracle 9 work around If your field can be larger than 4000 characters, we need the following work around. We have to change the field type to Clob.

Annotation mapping. 

import java.sql.Clob;
import javax.persistence.Lob;
........ snip .....
@Lob
   private Clob textWorkaround;

XML mapping. 

    <property name="textWorkaround" type="clob"></property>

When you save data, use the following code. The code is not portable to Oracle 10.

/* writing a cblob */
/* initialize with short blob */
document.setTextWorkaround(Hibernate.createClob(" "));
/* save before we continue */
session.save(document);
/* get a oracle clob to have access to outputstream */
SerializableClob sc = (org.hibernate.lob.SerializableClob) document
.getTextWorkaround();
oracle.sql.CLOB clob = (oracle.sql.CLOB) sc.getWrappedClob();
/* write the text to the clob outputstream */
try {
   java.io.Writer pw = clob.getCharacterOutputStream();
   pw.write(buffer.toString());
   pw.close();
   session.getTransaction().commit();
} catch (SQLException e) {
   throw new RuntimeException(
         "Datenbankfehler beim Speichern des Lobs",e);
} catch (IOException e) {
   throw new RuntimeException(
         "Datenbankfehler beim Speichern des Lobs",e);
}
/* reading a cblob */
StringBuffer textFromWorkaround = new StringBuffer();
try {
    BufferedReader bufferedClobReader = new BufferedReader(documentReloaded.
      getTextWorkaround().getCharacterStream());
    String line = null;
    while((line = bufferedClobReader.readLine()) != null) {
        textFromWorkaround.append(line);
    }
    bufferedClobReader.close();
} catch (IOException e) {
    throw new RuntimeException("Fehler beim Lesen des Lobs",e);
} catch (SQLException e) {
    throw new RuntimeException("Fehler beim Lesen des Lobs",e);
}

Instead of using a Clob field in your class, we could hide this code as well. We could add a second property providing the clob as java.util.String, create a CustomType.

Binary lob (blob)

A binary lob is very simple, if you use can use Oracle 10 or Oracle XE (Express Edition). We have to options in the class: a java.sql.Blob field and a byte array. The corresponding column is always a blob. Tip: Only annotation mapping does support mapping a byte array to a blob out of the box. You could create a workaround and create a ArrayOutputStream and write this to a blob.

Annotation mapping. 

import java.sql.Blob;
import javax.persistence.Entity;
import javax.persistence.Lob;
...... snip .........
import org.hibernate.annotations.Type;
import org.hibernate.type.BlobType;@Entity
public class Image implements Serializable {
   @Lob
   private byte imageAsBlob[];

   private Blob imageAsBlob2;

XML mapping. A field of type java.sql.Blob can be mapped with the following code. The type is optionally:

    <property name="imageAsBlob2" type="java.sql.Blob"></property>

The byte array approach does not work for XML. Either convert your byte arrays from and to java.sql.Blob or create a custom type that provides this feature. You can find further information about custom types in the Hibernate wiki.

Samples of use. 

/* creating a blob */
byte byteArray[] = new byte[10000000];
for (int i = 0; i < byteArray.length; i++) {
   byteArray[i] = '1';
}
Image image = new Image();

image.setImageAsBlob(byteArray);  // a blob as byte array
image.setImageAsBlob2(Hibernate.createBlob(byteArray)); // a blob as blob

/* reading */
// read blob from a byte array is as simple as from a bytea
FileOutputStream outputStream =
   new FileOutputStream(new File("image_file_blob_array"));
outputStream.write(image.getImageAsBlob());
outputStream.close();
// reading of a blob from a blob is in fact a inputstream
outputStream = new FileOutputStream(new File("image_file_blob_blob"));
outputStream.write(image.getImageAsBlob2()
   .getBytes(1,(int)image.getImageAsBlob2().length()));
outputStream.close();

Tip: You can only access the length field if your transaction is open.

image.getImageAsBlob2().length()

Further discussions of blob mapping for older Oracle versions can be found here: http://www.hibernate.org/56.html http://forum.hibernate.org/viewtopic.php?t=931155