7

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.

Community
  • 1
  • 1
Tiny
  • 27,221
  • 105
  • 339
  • 599
  • Define 'would not work.' Are you getting an error or is it returning `null`? – Dev Aug 14 '12 at 01: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 Aug 14 '12 at 02:30

5 Answers5

9

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());
Dev
  • 11,919
  • 3
  • 40
  • 53
  • 7
    `getSubString()`: The first character is at position 1, not 0, strangely, but this is what JavaDoc says – dzieciou Dec 09 '13 at 16:44
  • 1
    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). – bogdan.rusu Dec 16 '15 at 16:19
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);
    }
}
Axel
  • 31
  • 1
0

This might work

new BufferedReader(new InputStreamReader(catImage.getAsciiStream())).readLine()
ggDeGreat
  • 1,098
  • 1
  • 17
  • 33
0

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!!!

Shine
  • 31
  • 1
0

You can use this to read everything: 

new BufferedReader(new InputStreamReader(clob.getAsciiStream())).lines().collect(Collectors.joining());
lmo
  • 497
  • 5
  • 23