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.

Monday, October 24, 2011

Mule smtp outbound using Gmail


Following mule flow reads files from a directory and sends the content of the files to an email using gmail account.


<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:file="http://www.mulesoft.org/schema/mule/file"
xmlns:smtps="http://www.mulesoft.org/schema/mule/smtps" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
xmlns:email="http://www.mulesoft.org/schema/mule/email" xmlns:spring="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/3.1/mule-file.xsd
http://www.mulesoft.org/schema/mule/smtps http://www.mulesoft.org/schema/mule/smtps/3.1/mule-smtps.xsd
http://www.mulesoft.org/schema/mule/email http://www.mulesoft.org/schema/mule/email/3.1/mule-email.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/3.1/mule.xsd ">
<spring:beans />

<smtps:connector name="smtpsGmailConnector"
fromAddress="syourid@gmail.com" ccAddresses="toid@domain.com">

<smtps:header key="mail.transport.protocol" value="smtps" />
<smtps:header key="mail.smtps.auth" value="true" />
<smtps:header key="mail.smtps.quitwait" value="false" />
<smtps:tls-client />
<smtps:tls-trust-store path="greenmail-truststore" />
</smtps:connector>

<flow name="9f1f86a7-021f-4d20-b062-40fcd1c6c9fa">
<file:inbound-endpoint path="E:/someDir"
pollingFrequency="1000" fileAge="500" reverseOrder="false" doc:name="File"
doc:description="Read/write a file from the filesystem" />
<echo-component></echo-component>

<smtps:outbound-endpoint connector-ref="smtpsGmailConnector"
user="yourid" password="yourpassword" host="smtp.gmail.com"
subject="Your order has been placed!">

<email:string-to-email-transformer />
</smtps:outbound-endpoint>
</flow>
</mule>



Run with mule

Put some text file int the directory "E:/someDir"

Console will look something like the following image


Thursday, October 20, 2011

Simple Reverse Geo-coding in Java using Google Map

The Java Class

package geo;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

public class MyGeo {
public static void main(String ar[]) throws Exception {
System.out.println(new MyGeo().getAddress("13.031067,80.239656"));
}
public String getAddress(String latlong){
String address = null;
String gURL = "http://maps.google.com/maps/api/geocode/xml?latlng=" + latlong + "&sensor=true";
try {
DocumentBuilderFactory df = DocumentBuilderFactory.newInstance();
DocumentBuilder db = df.newDocumentBuilder();
Document dom = db.parse(gURL);
Element docEl = dom.getDocumentElement();
NodeList nl = docEl.getElementsByTagName("result");
if (nl != null && nl.getLength() > 0){
address=((Element)nl.item(0)).getElementsByTagName("formatted_address").item(0).getTextContent();
for(int i=0;i<nl.getLength();i++){
String temp=((Element)nl.item(i)).getElementsByTagName("formatted_address").item(0).getTextContent();
}
}
} catch (Exception ex) {
address = "Err";
}
return address;
}
public String getAddress(String lat, String lon) {
return getAddress(lat+ "," + lon);
}
public String getAddress(double lat, double lon) {
return getAddress("" + lat, "" + lon);
}
}

Run
>java geo.MyGeo
Venkatanarayana Rd, CIT Nagar, Chennai, Tamil Nadu, India


Note: Google has some restriction in this web-service call like number of requests per day from one IP.
Ref: http://code.google.com/apis/maps/documentation/geocoding/#ReverseGeocoding

Thursday, September 15, 2011

Postgres DBlink Example

dblink is one of the features available with postgres to do something by connecting remote pgdatabase from pg-sql.

To enable dblink execute the file located @ \share\contrib with the name of dblink.sql in to the required DB.

Following example function is to extract remote db's data and migrate it to local. Structure of the both tables vary is the notable one.

-- Function: extractobudata()

-- DROP FUNCTION extractobudata();

CREATE OR REPLACE FUNCTION extractobudata()
  RETURNS void AS
$BODY$
DECLARE
    last_extracted_data numeric;
BEGIN
    select max(packetid) into last_extracted_data from obuperiodicdata;

    INSERT INTO
        obuperiodicdata
        (packetid,
        obuserialnumber,
        companysign,
        gpsspeed,
        lat,
        lon,
        alt,
        heading,
        storedon,
        distance,
        enginerpm,
        receivedon,
        poiid)
                SELECT *
        FROM dblink('dbname= port=5432 host= user= password=', 'select
        af_sno,
  af_obu_serial_no ,
af_company_sign,
  af_gps_speed,
  af_lat,
  af_lon,
  af_alt,
  af_heading,
  af_timestamp,
  af_distance,
  af_engine_rpm,
  af_received_time,
  poi_id
        from at_obu_periodic_data where af_sno > '|| last_extracted_data  )
        AS t1(packetid numeric ,
        obuserialnumber character(100),
        companysign character(100) ,
        gpsspeed numeric,
        lat numeric,
        lon numeric,
        alt numeric,
        heading numeric,
        storedon timestamp,
        distance numeric,
        enginerpm numeric,
        receivedon timestamp,
        poiid integer );
   
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION extractobudata() OWNER TO postgres;

Installing & Enabling PostGIS in Postgres

  1. Check with the stack builder for PostGIS spatial Extension is installed.
  2. Enable to install(if not) and click next. 
  3. Download and install postGIS extension.
     
  4. Once it is installed check for the database postgis/template_postgis. If any of the above databases is there then that can be used.
  5. Otherwise create a new database manually and execute the sql file which should be located @ <c:\postgresinstallationlocation>\share\contrib\ with the name of postgis.sql.
  6. That database will have two tables called geometry_columns & spatial_ref_sys, some 100s of functions and some trigger functions.
  7. Now using that database as a template, any number of databases can be created with PostGIS enabled.


Friday, February 4, 2011

J2EE Web Application - Simple Single SignOn (SSO)

To have a common account (username-password) for various applications of an umbrella and to have authentication at a place to access all of the applications without the need to enter password for each is called Single Sing-on (SSO).


Here is a simple SSO implementation of web applications using JSP (would run any java web server).
Steps as follows:
  1. Create views for login and success login.jsp & success.jsp respectively for example.
  2. Write the action (as a servlet) login.do for example to handle and authenticate the request if the credentials are valid.
  3. Set a session attribute on success of login, username for example.
    session.setAttribute("username", userName);
  4. Create a jsp isLivingSession.jsp for example, which is going to act as javascript source and is the key part of our SSO.
login.jsp
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Login</title>
</head>
<body>
<form action="login.do?c=<%=request.getParameter("c")%>">
<input name="username" type="text" />
<input name="password" type="text" />
<input type="submit" value="submit" />
</form>
</body>
</html>

login.do
.....
if(loginSuccess){
 session.setAttribute("username", userName);
 c=request.getParameter("c");
 if(c!=null && !c.trim().equals("")){

   response.sendRedirect(c);
   //user will 
be automatically redirected to the calling application or page.
  }
 else{
response.sendRedirect("success.jsp");}
}
.......

isLivingSession.jsp

<%=session.getAttribute("username")==null?"window.location.href='http://"+request.getServerName()+":"+request.getServerPort()+request.getContextPath()+"login.jsp?c='+unescape(window.location.href);":""%>

Add the following line in all web pages or a file which is included by all pages header of the application which should use SSO. This could be used in any server or application or platform.

<script type="text/javascript" src="yoursite.com/isLivingSession.jsp?ignore=currtimeinmilliseconds"></script> 

one more thing should be noted is the web application should support javascript, so add noscript tag in all web pages.

and thats it.. go and play.