aspose file tools*
The moose likes JDBC and the fly likes Do I close connections from a javax.sql.DataSource? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Do I close connections from a javax.sql.DataSource?" Watch "Do I close connections from a javax.sql.DataSource?" New topic
Author

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

John Summers
Ranch Hand

Joined: Oct 06, 2003
Posts: 125
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
Carol Enderlin
drifter
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
When you are finished using the connection, make sure you close the connection to return it to the connection pool.
Chetan Lakshmeshwar
Greenhorn

Joined: Feb 04, 2003
Posts: 8
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
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

can you post you code of creating connection

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

"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
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

"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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
My expereince is that closing a connection taken from a DataSource returns it to the pool, but leaves the connection open.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Chetan Lakshmeshwar
Greenhorn

Joined: Feb 04, 2003
Posts: 8
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;
}
}
}

}
Craig Jackson
Ranch Hand

Joined: Mar 19, 2002
Posts: 405
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.
Chetan Lakshmeshwar
Greenhorn

Joined: Feb 04, 2003
Posts: 8
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.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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
Chetan Lakshmeshwar
Greenhorn

Joined: Feb 04, 2003
Posts: 8
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Do I close connections from a javax.sql.DataSource?