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.

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

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

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.

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

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

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.

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

A friendly helper method using apache commons.io

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

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

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.

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

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

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