Thursday, December 27, 2012

Hibernate CLOB to String Conversion AliasToEntityMapResultTransformer not working

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
org.hibernate.Query query=((Session) em.getDelegate() )
  .createSQLQuery(sql);
query
  .setResultTransformer( AliasToEntityMapResultTransformer.INSTANCE);
List<Map<String,Object>> aliasToValueMapList=query.list();
return aliasToValueMapList; 
which will return non-blob and non clob type columns as it is. those are readable in the viewing end. but for Clob types it will store the object notation like org.hibernate.type.ClobType@2311
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;
 }
}


now,

...
query
  .setResultTransformer( MyResultTransformer.INSTANCE);
...
will return list of maps with Clob types as Converted String.

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

4 comments: