Clob Datatype in MySQL and Hibernate mapping

Does MySQL support CLOB (Character Large Objects - used to store huge collection of character data)? The answer is yes, we have four different data types (depends on the size of the data) to support CLOB in MySQL. In this article I am going to explain different CLOB data types MySQL supports and mapping these fields in Hibernate.

CLOB Data Types in MySQL

MySQL provides four different data types to represent large character objects, they are:

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

Let us go though these data types in detail.

1. TINYTEXT - smallest CLOB representation, supports a maximum of 255 (2**8 - 1) characters.

2. TEXT - supports a maximum of 65,535 (2**16 - 1) characters.

3. MEDIUMTEXT - this data type supports a maximum of 16,777,215 (2**24 - 1) characters.

4. LONGTEXT - largest CLOB type with a maximum of 4,294,967,295 or 4GB (2**32 - 1) characters.

Note that some character encoding uses multiple bytes to store a single character, so the number of characters depends on the character encoding. In short, the storage space depends on the actual length of the data, the maximum possible length of the data type and the character set used.

Also the data is represented in a special manner for TEXT and BLOB data types, stored as two parts, first part is of fixed size 256 bytes where the second part is stored in a different table which is of 2000 bytes long. The second table is used only if the size of the data exceeds 256 bytes. This makes it difficult when you need to do capacity planning!

Let us try reading the Clob from database using plain jdbc:

Clob clob = rs.getClob(FIELD_BLOG_POST);
String blogPost = clob.getSubString(1, (int) clob.length());

Also you can use getAsciiStream() to retrieve the data as an ascii stream or getCharacterStream() to retrieve the data as a stream of characters.

Clob field Hibernate Mapping

Let us try mapping this clob in Hibernate, the mapping for "text" data type will look something like:

@Column(name = "BLOG_POST", length=65535)
@Type(type="text")
private String blogPost;

Just write getters and setters for blogPost and you are done!

Note: Make sure to enable strict SQL mode, else the value will be silently truncated by MySQL with only a warning if the size of the Clob column exceeds the permitted limit. Prior to MySQL 5.0.60, if strict SQL is on, the insertion will be suppressed with an error.