Hibernate Clob conversion is little tricky especially when we use with JPA.
There is no predefined Transformer for this in Hibernate due to some reason.
Code to retirive records as List of map
reason is AliasToEntityMapResultTransformer class does not have mechanism to convert clob to string as it may lead memory issue.
AliasToEntityMapResultTransformer.java
Solution is we can write our own result transformer.
MyResultTransformer.java
Note: Considerable thing here is memory. ex. for 100 records if each record has 1mb of data in a clob type attribute, the total size is >100 mb
Code to retirive records as List of map
org.hibernate.Query query=((Session) em.getDelegate() )
.createSQLQuery(sql);
query
.setResultTransformer( AliasToEntityMapResultTransformer.INSTANCE);
List<Map<String,Object>> aliasToValueMapList=query.list();
return aliasToValueMapList; reason is AliasToEntityMapResultTransformer class does not have mechanism to convert clob to string as it may lead memory issue.
AliasToEntityMapResultTransformer.java
..{
.........
...
public Object transformTuple(Object[] tuple, String[] aliases) {
Map result = new HashMap(tuple.length);
for ( int i=0; i<tuple.length; i++ ) {
String alias = aliases[i];
if ( alias!=null ) {
result.put( alias, tuple[i] );
}
}
return result;
}
...
.........
..} Solution is we can write our own result transformer.
MyResultTransformer.java
package com.util;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.io.IOUtils;
import org.hibernate.transform.BasicTransformerAdapter;
public class MyResultTransformer extends BasicTransformerAdapter {
public final static MyResultTransformer INSTANCE;
static {
INSTANCE = new MyResultTransformer();
}
private MyResultTransformer() {
}
private static final long serialVersionUID = 1L;
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < aliases.length; i++) {
Object t = tuple[i];
if (t != null && t instanceof Clob) {
Clob c = (Clob) tuple[i];
try {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
IOUtils.copy(c.getAsciiStream(), bos);
t = new String(bos.toByteArray());
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
map.put(aliases[i], t);
}
return map;
}
}
...
query
.setResultTransformer( MyResultTransformer.INSTANCE);
...
Note: Considerable thing here is memory. ex. for 100 records if each record has 1mb of data in a clob type attribute, the total size is >100 mb
