convert String to Clob and vice versa in Hibernate

Question

Suppose that I have a Class:

class EventTransaction {
    .....
    private Clob dataXML;

    public Clob getDataXML() {
       return dataXML;
    }

    public void setDataXML(Clob dataXML) {
       this.dataXML = dataXML;
    }
}

And Hibernate mapping xml:

 <property name="dataXML" type="java.sql.Clob">
        <column name="XML" sql-type="CLOB"/>
 </property>

In java code, how to I convert a String to Clob and vice versa to save into to the database:

Ex: EventTransaction et = new EventTransaction();
    String xml = "fdfsafafafa";
    et.setDataXML(convertStringToClob(xml));
    HibernateTemplate.saveOrUpdate(et);

Could you please help how to implement function convertStringToClob(String data);

Thanks,

I'm waiting for your acceptance! – Amir Pashazadeh Dec 08 '18 at 01:09 — Amir Pashazadeh, Dec 08 '18 at 01:09

score 18 · Answer 1 · edited Nov 08 '22 at 09:52

18

Do this

@Column(name="XML")
@Lob
private String dataXML;

public String getDataXML() {
   return dataXML;
}

public void setDataXML(String dataXML) {
   this.dataXML = dataXML;
}

So there is no need to convert, and everything is done by Hibernate.

I showed it using annotations, the same thing can be done using .hbm.xml files.

edited Nov 08 '22 at 09:52

VXp

11,598
6
31
46

answered May 20 '13 at 03:35

Amir Pashazadeh

7,170
3
39
69

If we do that we will get problem with 64k limitation. I want to save a larger xml file like string – hoang nguyen May 20 '13 at 03:39
4

And who limits you to 64k? – Amir Pashazadeh May 20 '13 at 21:02
2

Wow uhm... you just saved me about 20 lines of code, a cache for "de-clob-ed Strings" and a workaround for serialisation... Just goes to show you that you should know your tools (Hibernate) before using them. Thanks! – avalancha Mar 07 '18 at 17:08
That 64k is limitation of the database, just select proper type in DB and there will be no limitation! – Amir Pashazadeh Dec 08 '18 at 01:10
The most clean and simplest I've seen. Thanks dude! – C.B. Feb 15 '19 at 14:05
@hoangnguyen If you are using MySQL there are at least 2 types of LOBs one is a small LOB with 64k limit, and one a big LOB without that limitation (I cannot recall the names now as I am not working with MySQL for a long time).... it is up to you to use proper one according to your needs, – Amir Pashazadeh Jun 04 '23 at 14:13

score 5 · Answer 2 · answered May 20 '13 at 03:08

5

Here is code I made a long time ago to convert a Clob to a String. It's meant to be used in a utility class.

    public static String convertClobToString(Clob clob) throws IOException, SQLException {
            Reader reader = clob.getCharacterStream();
            int c = -1;
            StringBuilder sb = new StringBuilder();
            while((c = reader.read()) != -1) {
                 sb.append(((char)c));
            }

            return sb.toString();
     }

And if I am not mistaken, to create a Clob you would do something like this

     Clob myClobFile = new SerialClob("my string".toCharArray());

answered May 20 '13 at 03:08

george_h

1,562
2
19
37

java.lang.ClassCastException: javax.sql.rowset.serial.SerialClob cannot be cast to oracle.sql.CLOB – hoang nguyen May 20 '13 at 03:24
I get this exception: java.lang.ClassCastException: javax.sql.rowset.serial.SerialClob cannot be cast to oracle.sql.CLOB – hoang nguyen May 20 '13 at 03:24
Import Clob interface from java.sql.Clob and the other should be javax.sql.rowset.serial.SerialClob. Personally I never had to use this with hibernate, I used the @Lob annotation and on my Table declared the column as CLOB. – george_h May 20 '13 at 04:55
If I do that I cannot save a large XML String, we cannot save a string with length > 64000 characters – hoang nguyen May 20 '13 at 05:33
@hoangnguyen What is your database? I have experience with Oracle, and I know there is no such limitation when you declare the column type as CLOB in Oracle. – Amir Pashazadeh Dec 08 '18 at 01:12

Taoufik Mohdit · Answer 3 · 2014-02-25T15:09:07.253

0

The limitation of 64000 characters is on the database side when you declare the XML column as VARCHAR (and not on Java String), so as long as your column XML is a CLOB, it should work.

Excerpt from working code:

Entity:

private String xml;

SQL (ORACLE):

XML       CLOB,

Hibernate mapping:

<property name="xml" type="java.lang.String">
    <column name="XML" length="999999" />
</property>

If you want to store the XML as a file, then you should rather use BLOBs as shown below :

Entity:

private byte[] xmlFile;

SQL (ORACLE):

XML       BLOB,

Hibernate mapping:

<property name="xmlFile" type="java.io.File">
    <column name="XML" />
</property>

edited Feb 25 '14 at 15:09

answered May 20 '13 at 09:01

Taoufik Mohdit

1,910
3
26
39

I declared XML column is CLOB. The problem is still happen. – hoang nguyen May 21 '13 at 03:41
try declaring the dataXML field in your class as String (instead of Clob); change the XML mapping accordingly. In the code snippet above (extracted from working code) String is used instead of Clob on the Java side – Taoufik Mohdit May 23 '13 at 09:07

convert String to Clob and vice versa in Hibernate

3 Answers3

Linked