Thursday, May 17, 2007

Always remember to annotate properly your LOB columns on EJB3 JPA using Hibernate and MySQL

Some times when you work with annotations you tend to forget some of them. A very special case was when I had to persist a very large Character Object (CLOB) , to my MySQL database. I am using Hibernate as the persistence provider. It was the moment that I have forgoten to properly annotate my CLOB Column forgoting the point that MySQL has multiple types of CLOBs and you have to specify the Column Length to JPA - Hibernate so that my data are persisted properly.MySQL has 4 different types for text TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

So here was my not perfect annotation, char array ok, lazy for sure, Lob annotation.The Lob() annotation does not guarantee that this CLOB is going to be stored properly. As it is pointed here, the default columnn size is very small if you really have a large object!

@Lob()

@Basic(fetch=FetchType.LAZY)

@Column(name="notificationXML")


private char[] notificationXML;



So the annotation must specify using the length attribute a value of your choice depending on your needs.

@Lob()

@Basic(fetch=FetchType.LAZY)

@Column(name="notificationXML",
length=9048576)

private char[] notificationXML;


You can really increase this number up to 4.xxx.xxx.xxx figure..depending on your needs. For various DBs this number may change! So just a tip, always remember to annotate properly youir BLOB / CLOB columns depending on your needs!

No comments:

Post a Comment