Do I close connections from a javax.sql.DataSource?

Hi,

I'm writing a web application which gets connections from a JNDI provided javax.sql.DataSource. From these connections I execute queries and get ResultSets.

I'm assuming I call "close" on the ResultSets after I've finished with them, yes?

However I need to check...I'm also assuming I DON'T call "close" on the connections because this will screw up the connection pool. Is this correct? Do I just leave it up to the Container to close/open connections?

Thanks
john

When you are finished using the connection, make sure you close the connection to return it to the connection pool.

Hi,
I'm faced with a peculiar problem.
I did proceed to call close on the connection obtained from the pool but i found that instead of returning it to the pool it was being closed as if it were a standalone connection.
So I started working with the premise that after the stipulated time is over the container(tomcat) will close it.
But now i am running into certain problems.After some hits the errors start cropping up as connection is not got.I have to end up restarting the server all over again.
I am still developing this so its alright but i just can't think of moving it to forget production even UAT environment.

Can somebody tell me what and where i might be going wrong.
I'm following tomcat's guide for setting the pool word for word.no frills.

i would be most grateful if any of you can help me out with this.Do let me know if I have to give any more info on the lines of code etc

can you post you code of creating connection

Shailesh

"Chetu Bangalore"

Please look at javaranch's naming policy and change your display name accordingly

you can not have name of city in your name.A display name should be First name and last name separated by space.

Shailesh

"Chetu Bangalore",
Links to the things Shailesh mentioned:
JavaRanch Naming Policy Edit display name

The relevant part of the naming policy in this case is that names must not be obviously fictitious.

Thanks,
Jeanne
JDBC Forum Bartender

My expereince is that closing a connection taken from a DataSource returns it to the pool, but leaves the connection open.

This is my connection related class file.The tomcat version of pool in server xml has been modified to point to my database.
Kindly can somebody go through and let me know what is going wrong here.
I have actually commented the con.close() in releaseResources().But after a few hits i guess the pool is no longer able to supply me with connection and i face error pages popping up.but what seems a little crazy is a few pages work but some pages fail and these are the same pages that fail reeatedly when the pool is exhausted.
Pls help me out.
Also let me know if the design itself is flawed.

public class CommonConnector {

private static final String CLASS_NAME = "CommonConnector";

private static LoggerWrapperIF logger=xyz;//instance for logger of log4j
private static DataSource dataSrc;

private Connection con;

private Statement stmt;

private PreparedStatement pstmt;

private CallableStatement cstmt;

public CommonConnector() {

}

public DataSource getDataSource() throws ECATException{
try{
if(dataSrc==null) {
setDataSource();
return dataSrc;
}else {
return dataSrc;
}
}catch(ECATException ecatExObj){
String location=CLASS_NAME+".getDataSource() ->";
String message=location+" Error in getting a dataSource";
logger.logError(message,ecatExObj);
throw new ECATException(message,ErrorConstantsIF.SYSTEM_ERROR,ecatExObj);
}
}

private void setDataSource() throws ECATException{
try{
Context initContext=new InitialContext();
dataSrc=(DataSource)initContext.lookup("java:/comp/env/jdbc/ecat");
}catch(NamingException namingExObj){
String location=CLASS_NAME+".setDataSource() ->";
String message=location+" Error in setting a dataSource";
logger.logError(message,namingExObj);
throw new ECATException(message,ErrorConstantsIF.SYSTEM_ERROR,namingExObj);
}
}

private void setConnection(DataSource myDataSrc) throws ECATException{
try{
con=myDataSrc.getConnection();
}catch(SQLException sqlExObj){
String location=CLASS_NAME+".setConnection(DataSource myDataSrc) ->";
String message=location+" Error in setting a connection";
logger.logError(message,sqlExObj);
throw new ECATException(message,ErrorConstantsIF.DATABASE_ERROR,sqlExObj);
}

}

public Connection getConnection() throws ECATException{
try{
if(dataSrc==null) {
setDataSource();
}
if(con==null) {
setConnection(dataSrc);
}
return con;
}catch(ECATException ecatExObj){
String location=CLASS_NAME+".getConnection() ->";
String message=location+" Error in getting a connection";
logger.logError(message,ecatExObj);
throw new ECATException(message,ErrorConstantsIF.DATABASE_ERROR,ecatExObj);
}
}

public Connection getConnection(String userName,String password) throws ECATException{
try{
if(dataSrc==null) {
setDataSource();
}
con = dataSrc.getConnection(userName,password);
return con;
}catch(Exception exObj){
String location=CLASS_NAME+".getConnection(String userName,String password) ->";
String message=location+" Error in getting a connection";
logger.logError(message,exObj);
throw new ECATException(message,ErrorConstantsIF.DATABASE_ERROR,exObj);
}

}

public Statement getStatement() throws ECATException{
try{
return getConnection().createStatement();
}catch(SQLException sqlExObj){
String location=CLASS_NAME+".getStatement() ->";
String message=location+" Error in getting a statement";
logger.logError(message,sqlExObj);
throw new ECATException(message,ErrorConstantsIF.DATABASE_ERROR,sqlExObj);
}
}

public PreparedStatement getPreparedStatement(String sqlQuery) throws ECATException{
try{
return getConnection().prepareStatement(sqlQuery);
}catch(SQLException sqlExObj){
String location=CLASS_NAME+".getPreparedStatement(String sqlQuery) ->";
String message=location+" Error in getting a prepared statement";
logger.logError(message,sqlExObj);
throw new ECATException(message,ErrorConstantsIF.DATABASE_ERROR,sqlExObj);
}
}

public CallableStatement getCallableStatement(String sqlQuery) throws ECATException{
try{
return getConnection().prepareCall(sqlQuery);

}catch(SQLException sqlExObj){
String location=CLASS_NAME+".getCallableStatement(String sqlQuery) ->";
String message=location+" Error in getting a callable statement";
logger.logError(message,sqlExObj);
throw new ECATException(message,ErrorConstantsIF.DATABASE_ERROR,sqlExObj);
}
}

public void releaseResources(){

if(con !=null) {
try{
con.close();
}catch(SQLException sqlException){
// don't make connection null as it should be returned back to the pool
}
}
if(stmt !=null) {
try{
stmt.close();
}catch(SQLException sqlException){
stmt=null;
}finally{
stmt=null;
}
}
if(pstmt !=null) {
try{
pstmt.close();
}catch(SQLException sqlException){
pstmt=null;
}finally{
pstmt=null;
}
}
if(cstmt !=null) {
try{
cstmt.close();
}catch(SQLException sqlException){
cstmt=null;
}finally{
cstmt=null;
}
}
}

}

I think one possible problem, is that you are storing references to your Connection objects etc in instance variables. Could it be that one or more connection(s) is being shared by multiple requests and possibly not being returned to the database pool.

I have a dao class which has CommonConnector as a member variable.
Through which i operate.Do you still think it can be shared among requests.
Because as soon as i fire a query i call cleanUp defined in CommonConnector.So the connection variable should get released to the pool.Atleast that was how i wanted it to work and that is why i implemented it in this fashion.

Chetan,

As craig said that you are storing references to your Connection objects etc in instance variables

This is likely to be a reason of your problem. This might be a case when you are working on same instance and a connection is opened by one piece of code and closed by another.

even just for the testing purpose declare you connection varivale local to getConnection method or anything other but remove scope class level to local

Shailesh

Many many thanks Craig and Shailesh for your inputs.
I followed your suggestion and now it seems to work.
I went around on a clicking mission and it withstood the onsluaght :-)

Thank you very much.

Warm Regards,
Chetan