Thanks David for the inputs. I will do the same. This might fix my original problem aslo, I will try.
-----Original Message----- From: David Smith [mailto:[EMAIL PROTECTED] Sent: Monday, July 24, 2006 5:00 PM To: Tomcat Users List Subject: Re: Tomcat fails to refresh connections when mySQL server on linux is shutdown and restarted Best practice is to store the DataSource, not the connections in your singelton class. Then get a connection, perform your queries, and close the connection immediately. The pool will take care of managing the connections including creating new ones when existing connections die which happens when the database server is restarted. --David Seetha Rao wrote: >Hi Chris, thanks for the comments. I thought since we have singleton class and >creating a connection object there, we will be reusing the same connection >object for all database operations. May be my thinking is not correct. Do you >suggest I just create the DataSource object in the private constructor and >then get connection object from the datasource when doing a >database query/update opeartions? Yes, I will make sure I close the stmt and >connection objects. > >----- >private static DataSource ds = null; > >private DBManager() throws Exception >{ > Context init = new InitialContext(); > Context ctx = (Context) init.lookup("java:comp/env"); > ds = (DataSource) ctx.lookup("jdbc/jalasevaDB"); > >} > >And then, for example, in a method to do query, > >myCon = ds.getConnection(); >stmt = myCon.createStatement(); > >And then close the stmt and mycon. >------- > >What would be the effect of calling 'ds.getConnection()' for every database >operation? > >Thanks again for your guidance, >Seetha > > >-----Original Message----- >From: Christopher Schultz [mailto:[EMAIL PROTECTED] >Sent: Sunday, July 23, 2006 8:04 PM >To: [EMAIL PROTECTED] >Cc: Tomcat Users List; [EMAIL PROTECTED] >Subject: Re: Tomcat fails to refresh connections when mySQL server on >linux is shutdown and restarted > > >Seetha, > > > >>To answer Tim's question, we are not explicitly closing connection >>and statement objects as the context xml has these resource parameters. >> >>removeAbandoned="true" >>removeAbandonedTimeout="60" >>logAbandoned="true" >> >> > >This probably means that you are leaking every single connection. :( > > > >>Shouldn't DBCP take care of creating new connection if the connection >>object is stale? >> >> > >You will be creating new Connection objects all the time -- basically >you'll never re-use a database connection, making the pool completely >irrelevant; you may as well call DriverManager.getConnection each time >you need to make a SQL query. > >Even if DBCP /does/ clean up after you, you /really/ need to call >"close" on your statement, resultset, and connection objects in finally >blocks in your code. If you don't, your code will probably not work >if/when you switch to another connection pool, another app server, >another database, etc. > >Most databases allocate lots of memory for each database connection on >the server side, which means that every useless connection you have >waiting around to be cleaned up by DBCP will be taking up memory on your >database server that could be used to serve actual requests. > >Whether this solves your original problem or not, you definitely need to >modify your code to close all of those objects. > >-chris > > > > >--------------------------------------------------------------------- >To start a new topic, e-mail: users@tomcat.apache.org >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- To start a new topic, e-mail: users@tomcat.apache.org To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To start a new topic, e-mail: users@tomcat.apache.org To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
