Failed to read results from Spring-boot SQL query

Question

I am trying to read the data fetched from the data base using the following query from the repository interface:

public interface EventObjectRepository extends CrudRepository<EventObject, Long> {
    @Query(value = "select tb.content from table0 tb where tb.id=:id", nativeQuery = true)
    List<String> find(@Param("id") Long id);
}

Below is a sample snapshot of the results I got from this query, which is stored into a java list: List<String> results

[clob1: '{"identity":0,"original_text":"some text","rowid":2}', clob2: '{"identity":2,"original_text":"some text","rowid":3}', clob3: '{"identity":3,"original_text":"some text","rowid":4}', clob4: '{"identity":4,"product.name":"some name","original_text":"some text","commodity.name":"some name","rowid":5}']

However, when I was trying to access content of the list using for instance:

results.get(1)

I got the following error:

[ERROR] 2018-10-22 13:44:45.113 [http-nio-8090-exec-1] [dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.Cla
ssCastException: com.sun.proxy.$Proxy164 cannot be cast to java.lang.String] with root cause
java.lang.ClassCastException: com.sun.proxy.$Proxy164 cannot be cast to java.lang.String

What should I do properly to get the result string?

Answer 1

I finally got it to work with the help of CLOB.

First thing I did is to change the the String to CLOB inside the class definition as follows:

@Column(columnDefinition="text")
@Lob
private String content;

By adding the @Lob, the String will be saved to CLOB inside DB as suggested in the post here. Then to retrieve the data, the repository query will return a list of CLOBs as shown below:

public interface EventObjectRepository extends CrudRepository<EventObject, Long> {
    @Query(value = "select tb.content from table0 tb where tb.id=:id", nativeQuery = true)
    List<Clob> find(@Param("id") Long id);
}

After getting the data into a list of CLOBs, I need to convert each CLOB into a string using InputStream and IOUtils. For instance, to get one item from list:

InputStream stream = results.get(1).getAsciiStream();
StringWriter w = new StringWriter();
IOUtils.copy(stream, w);
String sample = w.toString();