wood burning stoves 2.0*
The moose likes Performance and the fly likes doubt in creating connections.. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "doubt in creating connections.. " Watch "doubt in creating connections.. " New topic
Author

doubt in creating connections..

san patil
Greenhorn

Joined: Mar 30, 2003
Posts: 8
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..
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
I don't think scenario 2 works. After freeing the connection at the end of a method, it shouldn't be used anymore, so the next method call will use an invalid connection.


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
san patil
Greenhorn

Joined: Mar 30, 2003
Posts: 8
Actually code is working fine in both the scnerio's.
Freeing the connection at the end of a method,
is nothing but returning it back to the pool, so that it is available for another request so it can offcourse be used.
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by san patil:
Actually code is working fine in both the scnerio's.
Freeing the connection at the end of a method,
is nothing but returning it back to the pool, so that it is available for another request so it can offcourse be used.


If a1 and a2 were used in different threads, that could lead to desaster!
san patil
Greenhorn

Joined: Mar 30, 2003
Posts: 8
I agree with what you have mentioned.
Thanks a lot for your explanation!
san
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
You're welcome!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: doubt in creating connections..
 
Similar Threads
Connection Pooling
a mistake for bean
nullpointerexception
why jsp cannot use this class, which is no problem when compile singlely.
Problem in retrieving data and store it in string