Most efficient solution for reading CLOB to String, and String to CLOB in Java?

Question

I have a big CLOB (more than 32kB) that I want to read to a String, using StringBuilder. How do I do this in the most efficient way? I can not use the "int length" constructor for StringBuilder since the lenght of my CLOB is longer than a "int" and needs a "long" value.

I am not that confortable with the Java I/O classes, and would like to get some guidance.

Edit - I have tried with this code for clobToString():

private String clobToString(Clob data) {
    StringBuilder sb = new StringBuilder();
    try {
        Reader reader = data.getCharacterStream();
        BufferedReader br = new BufferedReader(reader);

        String line;
        while(null != (line = br.readLine())) {
            sb.append(line);
        }
        br.close();
    } catch (SQLException e) {
        // handle this exception
    } catch (IOException e) {
        // handle this exception
    }
    return sb.toString();
}

What exactly you want to do once you read the CLOB into a String? — Omar Al Kababji, Jan 30 '10 at 22:38
Do you mean CLOB in the database sense, or just "large string"? — skaffman, Jan 30 '10 at 22:41
The CLOB contains a large XML-string that will be passed to JAXB. — Jonas, Jan 30 '10 at 22:56
I am wondering if there is any helpful classes in Java NIO for this. — Jonas, Jan 31 '10 at 09:47
I found some helpful info about CLOB in this document: http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/clob.html But I don't understand the use of .getSubString(1,len), since len is a long and not an int. — Jonas, Jan 31 '10 at 09:52
@Sanoj: You say that the size of your CLOB is larger than an `int` can store - that's 4GB of string data. Are you sure about this? — skaffman, Jan 31 '10 at 12:00
No, not yet, but I have a feeling what kind of problems I can get of this, so I will do some more testing. I got good advice here, thanks. — Jonas, Jan 31 '10 at 12:24

score 54 · Answer 1 · answered Jan 30 '10 at 22:54

54

Ok I will suppose a general use, first you have to download apache commons, there you will find an utility class named IOUtils which has a method named copy();

Now the solution is: get the input stream of your CLOB object using getAsciiStream() and pass it to the copy() method.

InputStream in = clobObject.getAsciiStream();
StringWriter w = new StringWriter();
IOUtils.copy(in, w);
String clobAsString = w.toString();

answered Jan 30 '10 at 22:54

Omar Al Kababji

1,768
4
16
31

1

Thanks, that looks nice. But I leavy the question open little bit more, because I would prefer a solution that only uses the standard library. – Jonas Jan 31 '10 at 09:46
I already have the Apache Commons library loaded so this is the perfect solution. Thanks! – John Strickler Jun 02 '11 at 14:47
16

getAsciiStream will give you headaches if you use unicode. (or any characters falling outside of ascii) – TJ Ellis Sep 29 '11 at 12:41
24

I changed `InputStream` to `Reader` and `clobObject.getAsciiStream()` to `clobObject.getCharacterStream()` to prevent encoding issues. – Dormouse Jun 11 '14 at 07:52
2

`IOUtils.copy(in, w)` is deprecated, use `IOUtils.copy(in, w, StandardCharsets.UTF_8)` instead – Shephard Oct 05 '21 at 11:35

gavenkoa · Answer 2 · 2020-12-25T18:01:19.273

37

What's wrong with:

clob.getSubString(1, (int) clob.length());

?

For example Oracle oracle.sql.CLOB performs getSubString() on internal char[] which defined in oracle.jdbc.driver.T4CConnection and just System.arraycopy() and next wrap to String... You never get faster reading than System.arraycopy().

UPDATE Get driver ojdbc6.jar, decompile CLOB implementation, and study which case could be faster based on the internals knowledge.

edited Dec 25 '20 at 18:01

answered Jul 01 '14 at 15:41

gavenkoa

45,285
19
251
303

Leaves a lot of newlines characters in the string. – Gervase Sep 16 '14 at 19:58
2

@Gervase Newlines can be significant in XML. Anyway, you shoud trim useless spaces and newlines before storing it to the DB. – Florian F May 23 '16 at 14:43
Some points to clear: What happen if clob.length() is greater than Integer.MAX_VALUE? What's jar contains oracle.sql.CLOB? – Stephan May 26 '16 at 14:33
2

@Stephan I studied `ojdbc6.jar`. `Integer.MAX_VALUE` is a limit for array length for **JDK Platform 2** and String hold chars in array. So you out of luck for > 2 GiB CLOBs... Try streaming approach because you can't hold that data with pure Java memory model (unless you use some native extension and 64-bit platform with enough system memory). – gavenkoa May 27 '16 at 14:24
what's wrong? https://stackoverflow.com/questions/16249238/java-sql-sqlrecoverableexception-while-fetching-clob-data-using-hibernate-orac ... I face the same problem when SQL close connection to CLOB data on production – Marek Bernád Jul 08 '20 at 13:37
1

@MarekBernád OK. I believe that you have problem because you cross transaction / connection boundaries. It is the problem with cumbersome frameworks that hides resource management. If you in managed EE environment access getter inside `@Transactional` )) *If you bother with efficiency Hibernate is not a good framework*. – gavenkoa Jul 08 '20 at 16:12

score 23 · Answer 3 · answered Dec 10 '12 at 03:42

My answer is just a flavor of the same. But I tested it with serializing a zipped content and it worked. So I can trust this solution unlike the one offered first (that use readLine) because it will ignore line breaks and corrupt the input.

/*********************************************************************************************
 * From CLOB to String
 * @return string representation of clob
 *********************************************************************************************/
private String clobToString(java.sql.Clob data)
{
    final StringBuilder sb = new StringBuilder();

    try
    {
        final Reader         reader = data.getCharacterStream();
        final BufferedReader br     = new BufferedReader(reader);

        int b;
        while(-1 != (b = br.read()))
        {
            sb.append((char)b);
        }

        br.close();
    }
    catch (SQLException e)
    {
        log.error("SQL. Could not convert CLOB to string",e);
        return e.toString();
    }
    catch (IOException e)
    {
        log.error("IO. Could not convert CLOB to string",e);
        return e.toString();
    }

    return sb.toString();
}

score 21 · Accepted Answer · edited Aug 17 '10 at 14:55

21

I can not use the "int length" constructor for StringBuilder since the length of my CLOB is longer than a int and needs a long value.

If the CLOB length is greater than fits in an int, the CLOB data won't fit in a String either. You'll have to use a streaming approach to deal with this much XML data.

If the actual length of the CLOB is smaller than Integer.MAX_VALUE, just force the long to int by putting (int) in front of it.

edited Aug 17 '10 at 14:55

Jonas

121,568
97
310
388

answered Jan 31 '10 at 11:19

Barend

17,296
2
61
80

9

Indeed, if the CLOB size is bigger than 2^32 bytes, you've got big problems – skaffman Jan 31 '10 at 11:55
I would suggest writing it to a file, if he need the whole CLOB to process – Khaled.K Jan 05 '16 at 10:39

score 4 · Answer 5 · edited Nov 28 '14 at 20:03

4

If you really must use only standard libraries, then you just have to expand on Omar's solution a bit. (Apache's IOUtils is basically just a set of convenience methods which saves on a lot of coding)

You are already able to get the input stream through clobObject.getAsciiStream()

You just have to "manually transfer" the characters to the StringWriter:

InputStream in = clobObject.getAsciiStream();
Reader read = new InputStreamReader(in);
StringWriter write = new StringWriter();

int c = -1;
while ((c = read.read()) != -1)
{
    write.write(c);
}
write.flush();
String s = write.toString();

Bear in mind that

If your clob contains more character than would fit a string, this won't work.
Wrap the InputStreamReader and StringWriter with BufferedReader and BufferedWriter respectively for better performance.

edited Nov 28 '14 at 20:03

Zeus

6,386
6
54
89

answered Jan 31 '10 at 12:09

Edwin Lee

3,540
6
29
36

That looks similar to the code i provided in my question, are there any key differences between them that I don't see? In example in a performance point of view? – Jonas Jan 31 '10 at 12:41
Oops, i missed out on your code fragment! It's somewhat similar, but bear in mind that by just grabbing the BufferedReader.readLine(), you'll miss out on the linebreaks. – Edwin Lee Feb 01 '10 at 00:36
1

Small Correction Line 2 should be Reader read = new InputStreamReader(in); – Vivek Jun 04 '12 at 07:15
2

No, no, no. `getAsciiStream()` forces ASCII encoding and corrupts all non-ASCII-characters. What you're doing is getting an `InputStream` (bytes) from a character source, and then immediately turning them back into characters using a random (platform default) encoding on `InputStreamReader`. It's a redundant operation except for the fact that it corrupts non-ASCII data. Just read from the `getCharacterStream()` `Reader` directly and write to the `StringWriter`. – Christoffer Hammarström Sep 20 '12 at 12:02

score 4 · Answer 6 · answered Apr 13 '16 at 14:35

If using Mule, below are the steps.

Follow below steps.

Enable streaming in the connector i.e. progressiveStreaming=2

Typecast DB2 returned CLOB to java.sql.Clob (IBM Supports this type cast)

Convert that to character stream (ASCII stream sometimes may not support some special characters). So you may to use getCharacterStream()

That will return a "reader" object which can be converted to "String" using common-io (IOUtils).

So in short, use groovy component and add below code.

clobTest = (java.sql.Clob)payload.field1 
bodyText = clobTest.getCharacterStream() 
targetString = org.apache.commons.io.IOUtils.toString(bodyText)
payload.PAYLOADHEADERS=targetString return payload

Note: Here I'm assuming "payload.field1" is holding clob data.

That's it!

Regards Naveen

score 2 · Answer 7 · answered Jul 31 '18 at 13:06

2

A friendly helper method using apache commons.io

Reader reader = clob.getCharacterStream();
StringWriter writer = new StringWriter();
IOUtils.copy(reader, writer);
String clobContent = writer.toString();

answered Jul 31 '18 at 13:06

fl0w

3,593
30
34

score 1 · Answer 8 · answered Nov 13 '15 at 09:29

public static final String tryClob2String(final Object value)
{
    final Clob clobValue = (Clob) value;
    String result = null;

    try
    {
        final long clobLength = clobValue.length();

        if (clobLength < Integer.MIN_VALUE || clobLength > Integer.MAX_VALUE)
        {
            log.debug("CLOB size too big for String!");
        }
        else
        {
            result = clobValue.getSubString(1, (int) clobValue.length());
        }
    }
    catch (SQLException e)
    {
        log.error("tryClob2String ERROR: {}", e);
    }
    finally
    {
        if (clobValue != null)
        {
            try
            {
                clobValue.free();
            }
            catch (SQLException e)
            {
                log.error("CLOB FREE ERROR: {}", e);
            }
        }
    }

    return result;
}

score 0 · Answer 9 · answered Nov 22 '14 at 21:15

public static String readClob(Clob clob) throws SQLException, IOException {
    StringBuilder sb = new StringBuilder((int) clob.length());
    Reader r = clob.getCharacterStream();
    char[] cbuf = new char[2048];
    int n;
    while ((n = r.read(cbuf, 0, cbuf.length)) != -1) {
        sb.append(cbuf, 0, n);
    }
    return sb.toString();
}

The above approach is also very efficient.

score 0 · Answer 10 · edited Dec 04 '17 at 11:29

private String convertToString(java.sql.Clob data)
{
    final StringBuilder builder= new StringBuilder();

    try
    {
        final Reader         reader = data.getCharacterStream();
        final BufferedReader br     = new BufferedReader(reader);

        int b;
        while(-1 != (b = br.read()))
        {
            builder.append((char)b);
        }

        br.close();
    }
    catch (SQLException e)
    {
        log.error("Within SQLException, Could not convert CLOB to string",e);
        return e.toString();
    }
    catch (IOException e)
    {
        log.error("Within IOException, Could not convert CLOB to string",e);
        return e.toString();
    }
    //enter code here
    return builder.toString();
}

Usually it's better to explain a solution instead of just posting some rows of anonymous code. You can read [How do I write a good answer](https://stackoverflow.com/help/how-to-answer), and also [Explaining entirely code-based answers](https://meta.stackexchange.com/questions/114762/explaining-entirely-%E2%80%8C%E2%80%8Bcode-based-answers) — Anh Pham, Dec 04 '17 at 10:32

score 0 · Answer 11 · answered Jan 12 '21 at 17:57

 public static String clobToString(final Clob clob) throws SQLException, IOException {
        try (final Reader reader = clob.getCharacterStream()) {
            try (final StringWriter stringWriter = new StringWriter()) {
                IOUtils.copy(reader, stringWriter);
                return stringWriter.toString();
            }
        }
    }

Khaled.K · Answer 12 · 2016-01-06T07:53:57.070

CLOB are like Files, you can read parts of it easily like this

// read the first 1024 characters
String str = myClob.getSubString(0, 1024);

and you can overwrite to it like this

// overwrite first 1024 chars with first 1024 chars in str
myClob.setString(0, str,0,1024);

I don't suggest using StringBuilder and fill it until you get an Exception, almost like adding numbers blindly until you get an overflow. Clob is like a text file and the best way to read it is using a buffer, in case you need to process it, otherwise you can stream it into a local file like this

int s = 0;
File f = new File("out.txt");
FileWriter fw new FileWriter(f);

while (s < myClob.length())
{
    fw.write(myClob.getSubString(0, 1024));
    s += 1024;
}

fw.flush();
fw.close();

Most efficient solution for reading CLOB to String, and String to CLOB in Java?

12 Answers12

Linked