Felipe Cypriano

You're about to read

Solving Grails/GORM Data Truncation Error on Blob Column Using Java

Today GORM was in a bad mood and refuses to insert a text in a blob (clob) column. If a set a non null value in the attribute which was mapped to a blob field I end up with this strange error:

Caused by: java.sql.DataTruncation: Data truncation

Data truncation for a blob column makes no sense since blobs are made to handle large amount of data, as far as I know the limit is your storage space. What I needed was an improved version of the Setting class that cames with settings plugin, I wanted to store large texts in some settings, larger the 100 characters which is the current limit, so I create a new attribute to store large texts, this new attribute is a blob in the dabase. Here is the new grails domain class that I created:

grails-app/domain/Setting.groovy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
class Setting {
    String code
    String content
    String largeText

    static constraint {
        code(length: 100)
        content(length: 255)
    }
    static mapping {
         largeText type: text
    }

    public static final String LARGE_TEXT = large_text
    public String getContent(){
       content != LARGE_TEXT ? content : largeText
    }

    public void setContent(String value) {
        if (value && value.size() > 255) {
            this.content = LARGE_TEXT
            largeText = value
        } else {
            this.content = value
            largeText = null
        }
    }
}

This is the logic behind the class, very simple and effective. The problem begun when I set a content bigger than 255 characters and call save()… boom java.sql.DataTruncation. After searching for a solution, and asking in the mailing list, I don’t find an answer. So I decide to make the mapping the way I was sure it works: using annotations and POJO. Then I create a java class instead of a groovy one, obvious parts are omitted:

src/java/entity/Setting.java
1
2
3
4
5
6
7
8
9
10
11
12
13
package entity;
// don’t forget the imports
@Entity
public class Setting implements Serializable {
    @Column(length = 100, unique = true)
    private String code;
    @Column(length = 255)
    private String content;
    @Lob
    private String largeText;

    // Getters and setters, the ones for content are identical the groovy version
}

Ok, the POJO is here but how will it work with grails and all its great features, like dynamic finders? It’s very simple just tell Hibernate, which is under the hood in Grails, that you want to map the Setting class. Open the file grails-app/conf/hibernate/hibernate.cfg.xml and configure it like this:

1
2
3
4
5
6
7
8
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC '-//Hibernate/Hibernate Configuration DTD 3.0//EN'
        'http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd'>
<hibernate-configuration>
    <session-factory>
        <mapping class="entity.Setting" />
    </session-factory>
</hibernate-configuration>

And voilà your POJO is now a full-blown Grails domain class. For now on you can call the dynamic finders, save method and everything:

1
2
Setting.findByCode(your.setting)
settingObj.save()

This is how I solved a problem with GORM and blob columns. I belive that I need to do all this because of the database I’m using in this project, which is Firebid 2.1. After all what matters is how great is groovy/grails integration with plain java.