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

Oracle XE Startup Problem : ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exit | ORA-12631 (Username retrieval failed)

Oracle eXpressEdition a compact and nice to use for development purpose.
But after i installed Oracle XE 11g in my machine it gave me a heavy headache to get it up and run correctly.

Problems i got are as below
  • ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exit
  • ORA-12631 (Username retrieval failed) 
  • http://127.0.0.1:8080/apex/ is not opening
  • listener.ora changes does not help
  • ....

I was Googling for more than a week for the problems and each solution leads to another problem for example if i solve ORA-27101 then the next gate will be ORA-12631(it goes one..).  

Finally i got relief from the headache.

  • To install Oracle XE user should have System Admin privilege.  
    so i got Admin privilege from my network admin.
  • I am able to install now, but still it makes problem.
  • XE service is not running or oracle Apex is not opening @ http://127.0.0.1:8080/apex/
  •  My account is actually networked (Windows NT user).
  • I uninstalled XE, then i logged in with the Local System Administrator account (not in NT domain).
  • I installed Oracle XE.
Now its up and running oracle Apex is also running well.

So careful, while installing Oracle XE be logged in the system as local Administrator.