Handling BLOB and CLOB with Hibernate

Learn to insert and fetch a BLOB or CLOB type field from the database using Hibernate Session and JPA EntityManager interface.

1. LOB Types in Database

Large Objects (LOBs) are a set of data types that are designed to hold large amounts of data. A LOB datatype can hold up to terabytes depending on the database and its configuration.

 Also. a table can have multiple LOB columns and those LOB columns can be of any LOB type. LOBs support random access to data and can have object attributes.

Generally, databases provide support for the following LOB types:

• BLOB: Stores any kind of data in binary format. Typically used for multimedia files such as images and videos.
• CLOB: Used for large strings or documents that use the database character set exclusively.
• NCLOB: Stores string data in National Character Set format.
• BFILE: A binary file stored outside of the database in the host operating system file system, but accessible from database tables. Use BFILEs to store static binary data that is not manipulated in the application.

2. Representing LOB in Entity Classes

In Java Entity objects, we can represent the binary or character data in many forms ranging from a byte array, and character array to specialized classes java.sql.Clob, java.sql.Blob and java.sql.NClob.

By default, Hibernates maps:

• the java.sql.Blob is mapped to BLOB type in the database.
• the java.sql.Clob is mapped to CLOB type in the database.
• the java.sql.NClob is mapped to NCLOB type in the database.

@Entity
@Table(name = "TBL_BOOK")
public class Book implements Serializable {
  @Id
  @Column(name = "id", nullable = false)
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  private Long id;

  private String name;

  @Column
  private java.sql.Blob cover;

  //Getters and setters are hidden for brevity
}

Or we can directly use the byte[] to store binary data and provide SQL datatype using columnDefinition attribute of @Column annotation. By default, byte[] is mapped to varbinary datatype.

@Column(columnDefinition = "BLOB")
private byte[] cover;

Creating the above entity will generate the following SQL in the log file.

Hibernate: create table TBL_BOOK (id bigint not null, cover BLOB, name varchar(255), primary key (id))

3. Storing LOB Types

We need to convert the file or image into byte[] and use the persist() method from either Session or EntityManager interface. If we are using any other datatype instead of byte array then we need to follow its related procedure.

Book book = new Book();
book.setName("Hibernate");
book.setCover(Files.readAllBytes(Paths.get("C:/temp/book.png")));

em.persist(book);

 Assertions.assertNotNull(book.getId());

Check out the SQL logs.

Hibernate: select next value for TBL_BOOK_SEQ
Hibernate: insert into TBL_BOOK (cover, name, id) values (?, ?, ?)

Also below is the code for HibernateUtil.java

4. Fetching LOB Types

To fetch the LOB type is also very similar to other normal fetches. We get the data back into byte array that we can use to recreate the file from it.

Book bookFetched = em.find(Book.class, book.getId());
Assertions.assertNotNull(bookFetched.getCover());

byte[] cover = bookFetched.getCover();

try (FileOutputStream fos
       = new FileOutputStream("C:\temp\testBook.png")) {
fos.write(cover);
} catch (Exception e) {
   e.printStackTrace();
}

5. Conclusion

In this hibernate tutorial, we learned to store and select large objects such as images, videos and other documents. We learned to map these objects into Database column types and Java types as well.

Happy Learning !!

Sourcecode on Github