Hi,
I have come across the following scenerio's on connection pooling at my work.
I am really not sure whether it is a better approach to initialize a connection object inside each and every method of Adapter class or just initialize it once in a constructor and eventually use it and release it upon usage.
The reason I have this doubt in my mind is, I have worked on the same issue with both the approaches and both are working fine.
Scenerio 1.
Initilizing the connection object in each and every method.
Code goes as below..
Class xxxAdapter
{
Connection conn=null;
Statement stmt=null;
public xxxAdapter()
{
}
public void getName(int param1,
String param2) throws Exception
{
String sql="SELECT EMP_NAME FROM EMP WHERE EMP_ID="'+param1+"' AND EMP_DEPT='"+param2+"'";
try
{
conn = DBConnectionPool().getConnection(); // Initilize the connection object to get the connection from pool
stmt= conn.createStatement(sql);
--- REST CODE GOES HERE ----
}
catch(SQLException e)
{
// Exception caught
}
finally
{
if(conn != null)
DBConnectionPool().freeConnection(conn); //return the connection to the pool
}
}
public void getDetails(String param1) throws Exception
{
String sql="SELECT * FROM EMP WHERE EMP_NAME="'+param1+"' ";
try
{
conn = DBConnectionPool().getConnection(); // Initilize the connection object to get the connection from pool
stmt= conn.createStatement(sql);
--- REST CODE GOES HERE ----
}
catch(SQLException e)
{
// Exception caught
}
finally
{
if(conn != null)
DBConnectionPool().freeConnection(conn); //return the connection to the pool
}
}
Scenerio 2.
Initilizing the connection in constructor only once and use it when needed.
Code goes as below..
Class xxxAdapter
{
Connection conn=null;
Statement stmt=null;
public xxxAdapter()
{
conn = DBConnectionPool().getConnection(); // Initilize the connection object to get the connection from pool
}
public xxxAdapter(Connection connIn)
{
conn = connIn; // Initilize the connection object with the connection object of calling class.
}
public void getName(int param1, String param2) throws Exception
{
String sql="SELECT EMP_NAME FROM EMP WHERE EMP_ID="'+param1+"' AND EMP_DEPT='"+param2+"'";
try
{
stmt= conn.createStatement(sql);
--- REST CODE GOES HERE ----
}
catch(SQLException e)
{
// Exception caught
}
finally
{
if(conn != null)
DBConnectionPool().freeConnection(conn); //return the connection to the pool
}
}
public void getDetails(String param1) throws Exception
{
String sql="SELECT * FROM EMP WHERE EMP_NAME="'+param1+"' ";
try
{
stmt= conn.createStatement(sql);
--- REST CODE GOES HERE ----
}
catch(SQLException e)
{
// Exception caught
}
finally
{
if(conn != null)
DBConnectionPool().freeConnection(conn); //return the connection to the pool
}
}
Which approach would be better? Awaiting to have your comments..
