Handling of blob in JPA and JDBC

You can store large JSON/String objects in RDBMS with blob. Oracle DB recommends blob for storing JSON objects. In this article, we’ll learn it how to handle it using JPA.

Hibernate supports blob type without requiring any converter. However, Eclipselink requires converter to use it. You can use following converters for eclipselink that converts String to byte[] while saving and byte[] to String while reading.

@Converter(autoApply = false)
public class StringToByteArrayConverter implements AttributeConverter<String, byte[]> {

  @Override
  public byte[] convertToDatabaseColumn(String value) {
    if (Objects.isNull(value)) {
      return null;
    }
    return value.getBytes(StandardCharsets.UTF_8);
  }

  @Override
  public String convertToEntityAttribute(byte[] value) {
    return new String(value, StandardCharsets.UTF_8);
  }
}

Then, you can define your entity column using the following way, which will allow to use above defined converter that would convert the String to byte[] while saving and vice-versa while reading.

public class Entity {
  ...
  @Convert(converter = StringToByteArrayConverter.class)
  @Column(name = "JSON_OBJ")
  private String jsonObj;
}

Here, we are using autoApply=false, that would perform the conversion only when it is marked. Else, it will convert all string to byte[] while saving.

Native query support

You may want to use native query to select the JSON objects in some cases. Here, you can make use of Jackson to perform the conversion for you.

Query q = entityManager.createNativeQuery("SELECT json_obj FROM entity");
List<byte[]> list = q.getResultList();
List<CustomModel> modelList = Collections.EMPTY_LIST;
if (Objects.nonNull(list) && !list.isEmpty()) {
  List<JsonNode> jsonList = new ArrayList<>(list.size());
  // thread safe immutable reader
  final ObjectReader reader = objectMapper.reader();
  list.forEach(i -> {
    // use exception handler
    jsonList.add(reader.readTree(new ByteArrayInputStream(i)));
  });
  modelList = objectMapper.convertValue(jsonList,
      new TypeReference<List<CustomerModel>>() {
      });
}
return modelList;

Here, objectMapper is an instance of ObjectMapper class.

Similarly, you can create using following code:

JsonNode jsonNode = objectMapper.createObjectNode();
// add code to write JSON/string to JSON node
// immutable writer with thread safe write
final ObjectWriter writer = om.writer();
final byte[] bytes = writer.writeValueAsBytes(jsonNode);

JDBC

For fetching from DB

try (InputStream reader = rs.getBinaryStream("json_obj")) {
  // IOUtils is from apache commons-io
  if (reader != null) String jsonData = new String(IOUtils.toByteArray(reader), StandardCharsets.UTF_8));
...

For writing to DB

preparedStatement = connection.prepareStatement(query).unwrap(OraclePreparedStatement.class);
// Here objValue is your model
final byte[] valArray  = objectMapper.writeValueAsString(objValue).getBytes(StandardCharsets.UTF_8);
final Blob blob = ps.getConnection().createBlob();
blob.setBytes(1, valArray);
preparedStatement.setBlob(idx, blob);
// You must free the blob using blob.free() and release all resources.