Most Efficient Solution for Reading Clob to String, and String to Clob in Java

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

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.

Convert Clob to String

Assuming you're using standard JDBC, once you have a ResultSet object you should be able to call ResultSet#getString("clob_field_name") to retrieve your CLOB data.

Handle large data object, should release Clob?

The fact that the clob isn't used outside that method doesn't imply that calling free() is unuseful: if you don't call it you are delaying the release of resources at least until the transaction ends (as Oracle's doc says).
This is also discussed in Do java.sql.Array/Blob/Clob types need to be "free()"ed? and Should JDBC Blob (not) be free()'d after use? and JDBC 4's java.sql.Clob.free() method and backwards compatibility

About simple/native way of getting a String from clob, see Most efficient solution for reading CLOB to String, and String to CLOB in Java?

Get Clob Java Spring with SqlRowSet

I found the answer combining two stackoverflow questions:

Spring SqlRowSet getting Clob as String using JdbcTemplate

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

First, you need to create a Clob object, and then work on a method to extract the string from the Clob object. My code looks like the following and it works:

            Clob clobObject = (Clob)rs.getObject("name");                                       
            final StringBuilder sb = new StringBuilder();
            try
                {
                    final Reader reader = clobObject.getCharacterStream();
                    final BufferedReader br = new BufferedReader(reader);
                    int b;
                    while(-1 != (b = br.read()))
                    {
                        sb.append((char)b);
                    }
                    br.close();
                    String theName = sb.toString();
            catch (Exception ex)
            {

            }

How to convert clob to string with encoding in java

The Clob already has an encoding. It's whatever you've specified in the database, and once you read it on Java side it'll be a String (with the implicit UTF-16 encoding, not that it matters at all).

Whatever you think you're doing with all those encoding tricks is wrong and useless. You only need to specify an encoding when turning bytes to chars or the other way around. You're dealing with chars only (except in your first example where you for some unknown reason want to turn them to bytes).

If you want to use IOUtils, then readFully(Reader input, char[] buffer) would be the method to use.

The platform default encoding has no effect in this whole question, since you shouldn't be working with bytes at all.

Edit:
A slightly more modern way with the standard JDK classes would be to use Reader.read(CharBuffer target) like

CharBuffer cb = CharBuffer.allocate((int) xmlClob.length());
while(r.read(cb) != -1)
    ;
return cb.toString();

but it doesn't really make a huge difference (it's a bit nicer looking).

How to extract Json Clob to java

I think there are two different issues.

On one hand, you need to convert the CLOB retrieved from the database to String. For that purpose, the solution proposed by @user7294900 is very good. In any way, please, consider read this related SO question, it provides other alternatives as well.

In any or other way, consider that you have a method that, given a CLOB, returns a String:

public String getClobAstring(Clob clob) {
  //... Any tf the implementations mentioned
} 

Now, the second problem has to do with how to return that information as JSON.

It will be very dependent on how you are serializing your object model to JSON and how your CLOB information looks like.

For instance, supposing that you are using Jackson, and that your CLOB information is an object without arrays and nested objects, you can try the following.

ObjectMapper mapper = new ObjectMapper();
Clob clob = (Clob) result.get("clob");
String json = getClobAstring(clob);
// If the structure of the json stored as Clob is simple,
// you can convert it to a Map, for example
Map<String, String> clobData = mapper.readValue(json, Map.class);
// If you face any error with the last line above, try using a `TypeReference` instead
// Map<String, String> clobData = mapper.readValue(json, new TypeReference<HashMap<String,String>>(){});


// Instantiate `GetResp` and associate clobData
GetResp getResp = new GetResp(); 
getResp.setClobData(clobData)

Of course, this will only be valid if, as mentioned, the structure of the JSON stored as CLOB is simple; if that it is not the case, you can create an object that hold the clob model:

public class ClobData {
  private String one;

  //...
}

and convert it appropriately:

ObjectMapper mapper = new ObjectMapper();
Clob clob = (Clob) result.get("clob");
String json = getClobAstring(clob);
// If the structure of the json stored as Clob is simple,
// you can convert it to a Map, for example
ClobData clobData = mapper.readValue(json, ClobData.class);

// Instantiate `GetResp` and associate clobData
GetResp getResp = new GetResp(); 
getResp.setClobData(clobData)

Equivalently, according to your last comments:

// Get CLOB from the database
Clob clob = (Clob) result.get("clob");

// Convert to string using one of the mentioned methods
String json = getClobAstring(clob);

// Now, you need to convert this json String to something else.
// The process will be very dependent of the underlying JSON processing library
// I assume you are using Jackson.
// In order to read the returned json, first build an ObjectMapper
ObjectMapper mapper = new ObjectMapper();

// For your comments, you are using the class SecondClass
SecondClass secondClass = mapper.readValue(json, SecondClass.class);

//If instead, you are returning a list of objects, for example, try this:
// SecondClass[] secondClasses = mapper.readValue(json, SecondClass[].class);

// Or this, it is the same
// List<SecondClass> secondClasses = mapper.readValue(json, new TypeReference<List<SecondClass>>(){});

// Then, you need to provide that information to the Base class

// In some place you instantiated it, it is not really important how
Base base = new Base(); 

// Then, I suppose you should provide the SecondClass information to
// that instance
base.setFieldWithJson(secondClass);

// Or:
//base.fieldWithJson = secondClass;

Once constructed, as you are using Spring Boot, you can return this json to the client using the standard mechanisms provided by the framework. For instance, in your Controller define the following method - please, note the @ResponseBody annotation, it will instruct Spring to serialize the returned object to JSON and return to the client in the HTTP response:

@GetMapping("/base-information")
// As mentioned, annotate your method with @ResponseBody
@ResponseBody
// You can provide the parameters that you need, in the way you need
public Base getBaseInformation(@RequestBody params) {
  // Based on the parameters, interact with your services and
  // its the underlying the database, what deemed appropriate
  // to fill base fields
  String err = result.get("err");
  String acc = result.get("acc");
  // in the case of the blob field, transform it as mentioned
  Clob clob = (Clob) result.get("clob");
  String json = getClobAstring(clob);
  ObjectMapper mapper = new ObjectMapper();
  List<SecondClass> fieldWithJson = mapper.readValue(json, new TypeReference<List<SecondClass>>(){});

  // Now, create an instance of the Base class and fill its 
  // different fields with the values obtained
  Base base = new Base();
  base.setErr(err);
  base.setAcc(acc);
  base.setFieldWithJson(fieldWithJson);

  // If you prefer, you can pass all this information in the class
  // constructor, for example
  // Base base = new Base(err, acc, fieldWithJson);
  
  // Then, return base with all the information fulfilled
  // Spring will handle json conversion for you as the method is 
  // annotated with @ResponseBody.
  return base;
}

If you are using another library, GSON, Moshi, etcetera, you will find similar approaches as well.

Use an Oracle clob in a predicate created from a String > 4k

I had to revert to using a PreparedStatement, but i've improved the normal implementation a bit by getting the connection from Spring and using apache commons BeanListHandler to map the ResultSet to an object List

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

@Autowired
DataSource dataSource;

List<MyDao> myMethod(String fileData){
    String myQuery="select * from dcr_mols WHERE flexmatch(ctab,?,'MATCH=ALL')=1";

try {
    Connection conn = this.dataSource.getConnection()   // Get connection from spring

    Clob myClob =  conn.createClob();   // Open a dB clob 
    myClob.setString( 1, fileData);     // add data to clob
    PreparedStatement ps = conn.prepareStatement(myQuery);
    ps.setClob(1,myClob);              // Add a clob into the PreparedStatement
    ResultSet rs =ps.executeQuery();   // Execute the prepared statement

    //ResultSetHandler<List<MyDao>> handler = new BeanListHandler<MyDao>(MyDao.class);   // Define the ResultSet handler
    ResultSetHandler<List<MyDao>> handler = new BeanListHandler<MyDao>(MyDao.class, new BasicRowProcessor(new GenerousBeanProcessor()));  // This is better than the above handler , because GenerousBeanProcessor removes the requirement for the column names to exactly match the java variables

    List<MyDao> myDaoList = handler.handle(rs);   // Map ResultSet to List of MyDao objects
    }catch (Exception e) {
        e.printStackTrace();
    }

return myDaoList;
}

---

Related Topics