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

User · Accepted Answer

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

User · Answer

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

User · Answer

public static final String tryClob2String final Object value        final Clob clobValue    Clob  value      String result   null       try               final long clobLength   clobValue length             if  clobLength  lt  Integer MIN VALUE    clobLength  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  myClob length          fw write myClob getSubString 0  1024        s    1024     fw flush    fw close

User · Answer

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

User · Answer

A friendly helper method using apache commons io  Reader reader   clob getCharacterStream    StringWriter writer   new StringWriter    IOUtils copy reader  writer   String clobContent   writer toString

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to java

Examples related to stringbuilder

Examples related to clob