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;
}
}