How to convert a CLOB data type to a String in Hibernate?

Question

In hibernate (3.2.1.GA), I use the following method to insert CLOB type data into Oracle (10g) database.

Hibernate.createClob(parameters.get("txtCatImage"));

parameters is a Map where all the request parameters have been stored. While retrieving the Clob data type from the database directly something like this entityObj.getCatImage() would not work.

Seen this and this questions but couldn't find the way.

The following is the entity that uses a Clob type property.

public class Category  implements java.io.Serializable {

    private Long catId; // Primary key.
    private Clob catImage; // CLOB type field.
    // Other fields.
    private static final long serialVersionUID = 1L;

    public Category() {}

    // Overloaded constructs + getters + setters + hashcode() + equals() + toString().
}

_{The Clob field in the database just stores an image file name, in this case.}

Define 'would not work.' Are you getting an error or is it returning null? — Dev
– Dev, Commented Aug 14, 2012 at 1:39
@Dev - It doesn't give any error. It just displays an object reference something like this org.hibernate.lob.SerializableClob@1e2ad75 with either obj.getCatImage().toString() or obj.getCatImage() instead of showing the actual contents which is an image file name in the Oracle database. The actual SQL on the Oracle prompt like SELECT * FROM category however shows the actaul contents on the Oracle terminal directly. — Tiny
– Tiny, Commented Aug 14, 2012 at 2:30

Dev · Accepted Answer · 2012-08-14 02:35:08Z

10

Either call Clob.getSubString(long, int) with appropriate arguments to get the desired String or read the Clob as an InputStream or Reader using Clob.getAsciiStream() or Clob.getCharacterStream().

If the Clob has fewer than 2147483647 (a.k.a. Integer.MAX_VALUE) characters you can do this

Clob clob = ... //Your clob
String clobString = clob.getSubString(0, clob.length());

edited Aug 14, 2012 at 2:35

answered Aug 14, 2012 at 2:29

Dev

12.3k3 gold badges45 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dzieciou Over a year ago

getSubString(): The first character is at position 1, not 0, strangely, but this is what JavaDoc says

bogdan.rusu Over a year ago

it's possible that in an early version the position began from 0. Also, both parameter must be int (clob.length() returns long so you need to convert that too).

Adi Prasetyo Over a year ago

java.sql.SQLException: Start position 1-based; must be 1 or more.

Axel · Accepted Answer · 2016-10-20 12:14:56Z

3

The solution from Dev (see above) to use "Clob.getSubString(long, int)" will be cost a lot of memory. I prefer the following solution.

private static String getAsString(Clob clob) {
    Reader reader = null;
    BufferedReader bufferedReader = null;
    try {
        reader = clob.getCharacterStream();
        bufferedReader = new BufferedReader(reader);
        return IOUtils.toString(bufferedReader);

    } catch (Exception e) {
        throw new RuntimeException("Error while reading String from CLOB", e);
    } finally {
        IOUtils.closeQuietly(reader);
        IOUtils.closeQuietly(bufferedReader);
    }
}

answered Oct 20, 2016 at 12:14

Axel

311 bronze badge

Comments

ggDeGreat · Accepted Answer · 2015-08-10 07:36:15Z

0

This might work

new BufferedReader(new InputStreamReader(catImage.getAsciiStream())).readLine()

answered Aug 10, 2015 at 7:36

ggDeGreat

1,2161 gold badge19 silver badges33 bronze badges

Comments

Shine · Accepted Answer · 2018-04-09 09:10:39Z

As Alex said, clob.getSubString(0, clob.length()) consumes memory and bad performance too. I had the same issue, where i am fetching a field of huge length varchar2(2000-4000 ) data from DB. The performance was slowing down just because of this varchar field to 1 min 20 secs. Fetching from Database to Entity classs was fast, but copying from Entity to SO object(or POJO) was taking huge time.

I am using JPA for DB data fetching. Entity field I kept as String. In SO Object(POJO) that field type I set as CLOB datatype.

After fetching data from DB, the data is available in Entity object. To copy to SO object(CLOB type),

SOobject.setLongStringField( new SerialClob(entityString.toCharArray()));//Converting String to CLOB

And at UI level, my SOobject, instead of normal getter setter method , i used as below (Alex's code)

public String getLongStringField() {
         Reader reader = null;
            BufferedReader bufferedReader = null;
            try {
                reader = longStringField.getCharacterStream();
                bufferedReader = new BufferedReader(reader);
                return IOUtils.toString(bufferedReader);

            } catch (Exception e) {

                throw new RuntimeException("Error while reading String from CLOB", e);
            } finally {
                IOUtils.closeQuietly(reader);
                IOUtils.closeQuietly(bufferedReader);
            }
    }

    public void setLongStringField(Clob longStringField) {
        this.longStringField= longStringField;
    }

hope this helps!!!

lmo · Accepted Answer · 2018-10-18 15:20:07Z

0

You can use this to read everything:

new BufferedReader(new InputStreamReader(clob.getAsciiStream())).lines().collect(Collectors.joining());

answered Oct 18, 2018 at 15:20

lmo

5475 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to convert a CLOB data type to a String in Hibernate?

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related