Query Helper

A new level of abstraction for the DAO. Basically, moves removes all the JDBC to a new layer which forces all queries to be precompiled in packages, thereby improving performance and making the DAO much cleaner and easier to read and maintain. It is driven by a table which stores the metadata for the procedure call. It returns the result set in a convenient ArrayList of HashMaps format. The code can be refined for project-specific requirements.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import javax.naming.NamingException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;


public class QueryHelper 
{
    Connection con = null;
    HashMap preparedStatements = null;
    
    String COLUMNNAME_METADATA="gcm_code_name";
    String COLUMNNAME_CALL="gcm_code_desc";
    String COLUMNNAME_CODE="gcm_code_short_name";
    String TABLENAME="gen_code_mst";

    public void openConnection()
    throws SQLException,NamingException
    {
      con = DBHelper.getConnection();
      con.setAutoCommit(false);
      preparedStatements = new HashMap();
    }

    public void closeConnectionRollback()
    throws SQLException
    {
      Object[] preparedStatementsList = ((Collection)preparedStatements.values()).toArray();
      boolean sqlExceptionOccurred=false;
      for(int i=0;i<preparedStatementsList.length;i++)
      {
        try
        {
          ((CallableStatement)(((HashMap)(preparedStatementsList[i])).get("CALL"))).close();
        }
        catch(SQLException e)
        {
          sqlExceptionOccurred=true;
        }
      }
      try
      {
        con.rollback();
      }
      catch(SQLException e)
      {
        sqlExceptionOccurred=true;
      }
      try
      {
        con.close();
      }
      catch(SQLException e)
      {
        sqlExceptionOccurred=true;
      }
      if (sqlExceptionOccurred)
        throw new SQLException();
    }
    
    public void closeConnectionCommit()
    throws SQLException
    {
      Object[] preparedStatementsList = ((Collection)preparedStatements.values()).toArray();
      boolean sqlExceptionOccurred=false;
      for(int i=0;i<preparedStatementsList.length;i++)
      {
        try
        {
          ((CallableStatement)(((HashMap)(preparedStatementsList[i])).get("CALL"))).close();
        }
        catch(SQLException e)
        {
          sqlExceptionOccurred=true;
        }
      }
      try
      {
        con.commit();
      }
      catch(SQLException e)
      {
        sqlExceptionOccurred=true;
      }
      try
      {
        con.close();
      }
      catch(SQLException e)
      {
        sqlExceptionOccurred=true;
      }
      if (sqlExceptionOccurred)
        throw new SQLException();
    }
    
    private void createCall(String queryShortName)
    throws SQLException
    {
      String codemstQuery="select "+COLUMNNAME_METADATA+" QUERY_METADATA, "+COLUMNNAME_CALL+" FULL_QUERY from "+TABLENAME+" where "+COLUMNNAME_CODE+"=?";
      PreparedStatement pStmt = con.prepareStatement(codemstQuery);
      pStmt.setString(1,queryShortName);
      ResultSet rs = pStmt.executeQuery();
      rs.next();
      String mainQuery=rs.getString("FULL_QUERY");
      String queryMetaData=rs.getString("QUERY_METADATA");
      pStmt.close();
      rs.close();

      String queryMetaDataSignature=queryMetaData.substring(queryMetaData.indexOf("@@")+2,queryMetaData.indexOf("@@",queryMetaData.indexOf("@@")+2));
      ArrayList types=new ArrayList();
      ArrayList inOut=new ArrayList();
      ArrayList name=new ArrayList();
      int prevCommaIndex=queryMetaDataSignature.indexOf("(");
      int nextCommaIndex=0;
      
      while(true)
      {
        if (queryMetaDataSignature.indexOf("()")>-1)
          break;
        nextCommaIndex=queryMetaDataSignature.indexOf(",",prevCommaIndex+1);
        if (nextCommaIndex==-1)
          nextCommaIndex=queryMetaDataSignature.indexOf(")",prevCommaIndex+1);
        if (nextCommaIndex==-1)
          break;
        String queryMetaDataSignatureSubstring=queryMetaDataSignature.substring(prevCommaIndex+1,nextCommaIndex);
        String queryMetaDataSignatureSubstringInOut=queryMetaDataSignatureSubstring.substring(0,queryMetaDataSignatureSubstring.indexOf(":"));
        String queryMetaDataSignatureSubstringType=queryMetaDataSignatureSubstring.substring(queryMetaDataSignatureSubstring.indexOf(":")+1,queryMetaDataSignatureSubstring.lastIndexOf(":"));
        String queryMetaDataSignatureSubstringName=queryMetaDataSignatureSubstring.substring(queryMetaDataSignatureSubstring.lastIndexOf(":")+1);
        
        types.add(queryMetaDataSignatureSubstringType);
        inOut.add(queryMetaDataSignatureSubstringInOut);
        name.add(queryMetaDataSignatureSubstringName);

        prevCommaIndex=nextCommaIndex;          
      }
      CallableStatement cStmt = con.prepareCall(mainQuery);        
      
      HashMap queryMap=new HashMap();
      queryMap.put("CALL",cStmt);
      queryMap.put("TYPE",types);
      queryMap.put("INOUT",inOut);
      queryMap.put("NAME",name);
      preparedStatements.put(queryShortName,queryMap);
    }

    public HashMap execute(String queryShortName,Object parameters)
    throws SQLException
    {
      HashMap results=null;
      HashMap queryMap = (HashMap)preparedStatements.get(queryShortName);
      if (queryMap==null)
        createCall(queryShortName);
      queryMap = (HashMap)preparedStatements.get(queryShortName);
      
      CallableStatement cStmt = (CallableStatement)queryMap.get("CALL");
      ArrayList types = (ArrayList)queryMap.get("TYPE");
      ArrayList name = (ArrayList)queryMap.get("NAME");
      ArrayList inOut = (ArrayList)queryMap.get("INOUT");
    
      ArrayList values=new ArrayList();
      HashMap record=null;
      ResultSetMetaData rsmd=null;
      int paramCounter=0;
      int colCount;
      
      for(int paramNo=0;paramNo<types.size();paramNo++)
      {
        if (((String)inOut.get(paramNo)).equals("IN"))
        {
          if (parameters.getClass().toString().indexOf("java.lang.String")!=-1)
          {
            values.add(((String[])parameters)[paramCounter]);
            paramCounter++;
          }
          else if (parameters.getClass().toString().indexOf("java.util.HashMap")!=-1)
          {
            values.add((String)((HashMap)parameters).get(((String)name.get(paramNo))));
          }
        }
        else
        {
          values.add(null);
        }
      }
      
      for(int paramNo=0;paramNo<types.size();paramNo++)
      {
        if (((String)inOut.get(paramNo)).equals("IN"))
        {
            cStmt.setString(paramNo+1,(String)values.get(paramNo));
        }
        else if (((String)inOut.get(paramNo)).equals("OUT"))
        {
          if (((String)types.get(paramNo)).equals("RESULTSET"))
            cStmt.registerOutParameter(paramNo+1,OracleTypes.CURSOR);
          if (((String)types.get(paramNo)).equals("STRING"))
            cStmt.registerOutParameter(paramNo+1,OracleTypes.VARCHAR);
        }
      }
      
      cStmt.execute();
      
      results=new HashMap();
      for(int paramNo=0;paramNo<types.size();paramNo++)
      {
        if (((String)inOut.get(paramNo)).equals("OUT"))
        {
          if (((String)types.get(paramNo)).equals("RESULTSET"))
          {
            ResultSet rset = ((OracleCallableStatement)cStmt).getCursor(paramNo+1);
            results.put(((String)name.get(paramNo)),toArrayList(rset));
            rset.close();
          }
          else if (((String)types.get(paramNo)).equals("STRING"))
          {
            String str = ((OracleCallableStatement)cStmt).getString(paramNo+1);
            results.put(((String)name.get(paramNo)),str);
          }
        }        
        
      }
      return results;
    }    

    private ArrayList toArrayList(ResultSet rs)
    throws SQLException
    {
      ArrayList results=null;
      results=new ArrayList();
      ResultSetMetaData rsmd=rs.getMetaData();
      int colCount=rsmd.getColumnCount();
      while(rs.next())
      {
        HashMap record=new HashMap();
        for(int i=0;i<colCount;i++)
          record.put(rsmd.getColumnName(i+1),rs.getString(rsmd.getColumnName(i+1)));
        results.add(record);
      }
      return results;
    }
}