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

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.

EditI 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();
}

String to Clob in Java?

I have a string in java, but the datatype in database is Clob. How do i get a Clob from String?

cannot convert clob to string

I am trying to convert my clob object to string to display that on my JSTL page for that I am writting the following code public String convertClobToString(Clob clob){ String toRet=; if(clob!=null)

Convert String to Clob in Java

I have a situation where I need to make Clob object from String. The problem is I can’t have ConnectionManager in that method. I need to some utility like public Clob getClob(String data){ } Can an

Converting from String to Clob using setString() not working

I am trying to convert a String into a Clob to store in a database. I have the following code: Clob clob = connection.createClob(); System.out.println(clob before setting: + clob); clob.setString(1

convert string to oracle.sql.clob in java?

I’m coding a Java function inside Oracle Database which produce allot of text! how to convert a string to CLOB (oracle.sql.CLOB) in java? what is the straight-forward way to do? I’m trying to build a

Convert Java String to CLOB

I have the following table: The problem is that when I try to use JDBC to insert a String to this field I always get the following error: java.sql.SQLException: Data size bigger than max size for thi

Conversion From String to Clob and Clob to String

I am trying to save XML response which comes as StrignBuffer to method. I need to save this string data to Sql as CLOB and retrieve the same. The response can be upto 3-4Kb, which should be retrieved

Oracle clob to String in JSP

I am using JSP to access Oracle 10g. One of the table includes a field with clob data type. When it is retrieved with getString API and be assigned to String type, it gives an error (java.sql.SQLExce

String to CLOB with postgreSQL

I’m trying to read a clob from postgreSQL DB, change it, and write it back. I was able to read the clob successfully using the following code: PreparedStatement statement = connection.prepareStatement

Java: Clob to byte[]

How can I read a java.sql.Clob into a byte[]?

Answers

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();

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.

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(read);
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

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

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();
}

What’s wrong with:

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

?

For example Oracle oracle.sql.CLOB make getSubString() from internal char[] which defined in oracle.jdbc.driver.T4CConnection and just System.arraycopy() and next wrap to String…

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.

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;
}

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();

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