How to extract Json Clob to java

Question

First of all I have SpringBootApplication. I declared a stored procedure there:

 public Get(JdbcTemplate jdbcTemplate) {
            setJdbcTemplate(jdbcTemplate);
            setSql(PROCEDURE);
    
      
  
    declareParameter(new SqlOutParameter("smth", Types.NUMERIC));
    declareParameter(new SqlOutParameter("clobData", Types.CLOB)); //json data
compile()

Here is the code for getting parametres, which is derived from controller:

public Base get(some parametres) throws SQLException {

        var res = super.execute(some parametres);

        final var err= (String) res.get("error");
        final var clobData = (Clob) res.get("clobName"); //json
//other code

return new Base(err, clobData);

Base.class

public class Base {
String err;
List<SecondClass> json;
}

Overall the response would be like this:

{
"err": "smth",
"acc": "123",
"clobData": [{
"one": "two"
...
    }, { ...
}]
}

I am able to retrieve all fields except field with Clob (json) data. I dont know whats the problem. Maybe I should cast it to String (I couldn't, because the response body said that Clob can't be cast to String) and show somehow? Or there is a solution with Json object?

Answer 1

You need to read clob:

StringBuilder sb = new StringBuilder();
try (Reader reader = clobData.getCharacterStream(); 
    BufferedReader br = new BufferedReader(reader)) {
    String line;
    while(null != (line = br.readLine())) {
       sb.append(line);
    }
} catch (Exception e) {
    //handle error
}
String json = sb.toString();

Answer 2

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.