Skip to main content

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.

About the Author

Sunil is a technical writer with more than 8 years of IT experience, worked with multi national companies, designed and developed large enterprise applications. He writes about latest technologies, social media and search engine optimization.

Comments

Popular posts from this blog

HDFC Bank introduces Missed Call Service to know Account Balance

Missed call is a powerful business tool in developing countries like India where customers give a miss call to specific phone numbers for getting account details, providing feedback, voting etc. On receiving a missed call from a registered phone number, the underlying app performs a phone number lookup and sends the data to the caller via text message(SMS) or records the call details for future processing.HDFC Bank recently introduced missed call service for its retail customers which allows to retrieve bank account details, mini statement etc. by simply giving a miss call to their toll free numbers.Following services are now available: 1800 270 3333 - Account Balance 1800 270 3355 - Mini Statement 1800 270 3366 - Request for new Cheque Book 1800 270 3377 - Request for Bank account statement Also you can download HDFC Mobile Banking Application by giving a missed call to : 1800 270 3344. Other banks providing missed call serviceAxis bank(known as Axis Dial) - 09225892258Bank of India…

Induction Cooker Showing an Error Code? Induction Cooker Error Codes Explained

Are you searching for Induction Cook-top error codes? Here you can find the error codes of all popular induction cooktops and how to troubleshoot it.

These are for your reference only, do not try to open your cooktop without proper safety measures, we advise you to call the service person if any servicing is needed.

If you want to know how Induction Cook-top works, read our previous article titled What is Induction Cooker? How Induction Cooker Works?.

Whirlpool Induction Cooker Error CodesError CodeErrorSolutionF0An internal error was detected.Disconnect power. Wait 5 seconds before reconnecting power. If the symbol appears again, call for service.F2The surface cooking area is too hot and has turned off.Remove the pans from the surface cooking area. "F2" will disappear when the surface cooking area has cooled. If you turn the surface cooking area back on and "F2" reappears, the cooktop is still too hot. Turn off the surface cooking area and allow it to cool.F4The po…

LICHFL - Generating Home Loan Statements Online

Generating an online statement from LIC Housing Finance Ltd is very easy, simply follow the below steps to create an online account with LICHFL and generate statements online! You may use the online generated statement as a proof for principal paid for a housing loan(under section 80C) and interest paid(under section 24) while filing income tax returns.Want to know how to save maximum income tax? Read our most read article how to save maximum income tax(opens in new tab).Before reading further, make sure you have the following information with you: Your Loan Account NumberSanctioned AmountStep 1 - Open LICHFL websiteVisit LICHFL website and click on the 'New Customers? Click Here' link(refer the below screenshot). Step 2 - Enter your loan account detailsFill in the following details: New Loan NumberSanctioned AmountDate of BirthSecurity Codeand click on the Submit button. Step 3 - Enter your personal detailsEnter your email address, a username and password and click on submi…