Converting object to CLOB

Question

I have a object array( it is an output of a native query in Oracle database) out of which the first element is of CLOB data type which needs to converted to a java String object. How would I achieve this? Please help.

 String sql = "select id, data from mytable";
            List< Object[] > results = getEntityManager().createNativeQuery(sql).getResultList();
Map< Long, String > map = new HashMap<>();
        for (Object[] result : results) {
            map.put(((Number) result[0]).longValue(), (String) result[1]);
        }

data is a column in mydata table with CLOB data type. result(1) would have my CLOB data

While putting in the HashMap I need to parse the result(1) as String.

Please provide a [MCVE] including the java (and, if necessary, SQL) code necessary to replicate your issue. — MT0, Jun 07 '17 at 11:48
Possible duplicate of [how to Retrive the CLOB value from Oracle using java](https://stackoverflow.com/questions/19486648/how-to-retrive-the-clob-value-from-oracle-using-java) — Rene, Jun 07 '17 at 12:15

C.Champagne · Answer 1 · 2017-06-07T12:47:11.293

1

If the length of the CLOB is small enough ( <Integer.MAX_VALUE) you can do the following :

clob.getSubString(1, (int) clob.length());

(Just have a look at this question)

EDIT :

The code you submitted in your question should become:

String sql = "select id, data from mytable";
List< Object[] > results = getEntityManager().createNativeQuery(sql).getResultList();
Map< Long, String > map = new HashMap<>();
Clob clob = (Clob)result[1];
String value = clob.getSubString(1, (int) clob.length());
map.put(((Number) result[0]).longValue(), value);

Please note that the loop in your original code was absolutely useless so I removed it.

Check also that result[1] is a java.sql.Clob

edited Jun 07 '17 at 12:47

answered Jun 07 '17 at 11:57

C.Champagne

5,381
2
23
35

I have updated my query. Could you please check now? – PeaceIsPearl Jun 07 '17 at 12:08
@PeaceIsPearl Sorry but I don't see what more do you need except that you have to replace `clob` by `((Clob)result[1])]`. I can add this in my answer. – C.Champagne Jun 07 '17 at 12:38
Thanks for editing the code. But I need the for loop as I am expecting multiple id and data rows. Never mind, that is not a problem. I tried the following thing Clob data = (Clob) result[1]; The data looks like this : clob2: STRINGDECODE('name ''pearl'' \n validations'). Searched further about STRINGDECODE function and it is there as I have a newline character in the clob data.If I remove the new line characters I see that data looks like this: clob2: ('name ''pearl'' validations') I am trying to make this query run on H2 database. – PeaceIsPearl Jun 09 '17 at 06:47

score 0 · Answer 2 · answered Jun 20 '17 at 07:16

0

As a solution to my query, got rid of the native query and used hibernate instead. Mapped my CLOB column with javax.persistence.Lob

 @Lob
 @Column(name = "DATA")
 byte[] clobData

answered Jun 20 '17 at 07:16

PeaceIsPearl

329
2
6
19

Converting object to CLOB

2 Answers2

Linked