1. Introduction
In this tutorial, we’ll discuss mapping LOBs or database Large Objects. We’ll discuss following LOBs:
- Blob – Binary Large Object. It is used to store large binary data like image, audio, video etc.
- Clob – Character Large Object. It is used to store large character data like large text file.
We’ll not discuss the NCLOB in this tutorial. NCLOB stores character data encoded in the national character set.
Mapping LOBs (database Large Objects) can be done in 2 ways:
- Using the JDBC locator types. A LOB locator is only valid during the duration of the transaction in which it was obtained. JDBC LOB locators allow you to efficiently access the LOB data. JDBC LOB locators allow the JDBC driver to stream parts of the LOB data as needed resulting in efficient memory usage.
- Materializing the LOB data and map it to
byte[]orString. There is a performance tradeoff in this case as this option deals with the entire LOB contents in memory.
2. JDBC LOB locator types
The JDBC LOB locator types include:
- java.sql.Blob – This represents a mapping in Java for an SQL BLOB value. An SQL BLOB is a built-in type that stores a Binary Large Object as a column value in row of database column. By default drivers implement this interface using an SQL locator (BLOB). An SQL locator is a logical pointer to the SQL BLOB data rather than the data itself.
- java.sql.Clob – This represents the mapping in Java for an SQL CLOB type. An SQL CLOB is a built-in type that stores a Character Large Object as a column value in a row of a database table. By default drivers implement this interface using an SQL locator(CLOB). An SQL locator is a logical pointer to the SQL CLOB data rather than the data itself.
- java.sql.NClob – This represents the mapping in the Java for the SQL NCLOB type. An SQL NCLOB is a built-in type that stores a Character Large Object using the National Character Set as a column value in a row of a database table.
JDBC handling with LOB data varies from driver to driver. Hibernate takes care of this for you.
3. Example
In this example, we’ll create an entity Article and will show how to save the CLOB and BLOB data.
For this example, we have used the following dependencies in pom.xml:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.27</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.6.3.Final</version> </dependency>
The hibernate.cfg.xml looks like the following:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/mydb</property>
<property name="connection.username">root</property>
<property name="connection.password">admin</property>
<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.MySQL8Dialect</property>
<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>
<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">create</property>
<mapping class="com.learnitweb.entity.Article"/>
</session-factory>
</hibernate-configuration>
Following is the Article entity for our example:
package com.learnitweb.entity;
import java.sql.Clob;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Lob;
@Entity(name = "Article")
public class Article {
@Id
private Integer id;
private String title;
@Lob
private Clob articleText;
// getter and setter removed for brevity
}
Notice the use of @Lob annotation and Clob type for articleText field in the Article entity.
Its time to test our code now.
package com.learnitweb.client;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.engine.jdbc.ClobProxy;
import com.learnitweb.entity.Article;
import com.learnitweb.util.HibernateUtil;
public class Client {
public static void main(String[] args) {
Transaction transaction = null;
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
transaction = session.beginTransaction();
String text = "This is article text.";
final Article article = new Article();
article.setId(1);
article.setTitle("My first article");
article.setArticleText(ClobProxy.generateProxy(text));
session.save(article);
transaction.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Hibernate will generate the following SQL statements:
create table Article (id integer not null, articleText longtext, title varchar(255), primary key (id))
insert into Article (articleText, title, id) values (?, ?, ?)
binding parameter [1] as [CLOB] - [{clob}]
binding parameter [2] as [VARCHAR] - [My first article]
binding parameter [3] as [INTEGER] - [1]
To retrieve the value, you can use java.io.Reader.
// get Article from DB
Article article = session.get(Article.class, 1);
// read the CLOB value from Article
try (Reader reader = article.getArticleText().getCharacterStream()) {
String articleText = getDataFromReader(reader);
System.out.println(articleText);
} catch (Exception e) {
e.printStackTrace();
}
static String getDataFromReader(Reader reader) throws IOException {
String targetString = "";
int intValueOfChar;
while ((intValueOfChar = reader.read()) != -1) {
targetString += (char) intValueOfChar;
}
return targetString;
}
4. Map CLOB in materialized form
You could also map the CLOB in a materialized form. You can either use a String or a char[].
4.1 CLOB – materialized char[] mapping
@Entity(name = "Article")
public class Article {
@Id
private Integer id;
private String title;
@Lob
private char[] articleText;
// getter and setter removed for brevity
}
4.2 CLOB – materialized String mapping
@Entity(name = "Article")
public class Article {
@Id
private Integer id;
private String title;
@Lob
private String articleText;
// getter and setter removed for brevity
}
5. Mapping BLOB
In our example, we’ll save an image in database. Add cover field in the entity Article:
@Entity(name = "Article")
public class Article {
@Id
private Integer id;
private String title;
@Lob
private Blob cover;
}
Let us now run the code.
import java.io.File;
import java.io.IOException;
import java.io.Reader;
import java.nio.file.Files;
//other imports removed for brevity
public class Client {
public static void main(String[] args) {
Transaction transaction = null;
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
transaction = session.beginTransaction();
File fi = new File("D:\\myImage.jpg");
byte[] fileContent = Files.readAllBytes(fi.toPath());
final Article article = new Article();
article.setId(1);
article.setTitle("My first article");
article.setCover(BlobProxy.generateProxy(fileContent));
session.save(article);
transaction.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Hibernate will generate the following SQL statements:
create table Article (id integer not null, cover longblob, title varchar(255), primary key (id))
insert into Article (cover, title, id) values (?, ?, ?)
binding parameter [1] as [BLOB] - [{blob}]
binding parameter [2] as [VARCHAR] - [My first article]
binding parameter [3] as [INTEGER] - [1]
You can do the following while reading the data from database:
Article article = session.get(Article.class, 1);
try (InputStream inputStream = product.getImage().getBinaryStream()) {
// write your logic here to work with inputStream
}
5.1 BLOB – materialized byte[] mapping
You could also map the BLOB in a materialized form byte[].
@Entity(name = "Article")
public class Article {
@Id
private Integer id;
private String title;
@Lob
private byte[] cover;
}