do we need multiple connection objects

[Pacerier]

Heys all, i have a class with a static reference to a java.sql.Connection object. i mainly call the object's prepareStatement().

Every other servlet that needs the DB will basically contact this class and they will all share the same connection object. I'm wondering if i'm going to bump into some real concurrency issues or is it perfectly alright to do so?

[travishein]

I'm pretty sure that this is a bad idea to do. because it's possible to get into a state where the connection is in use by one thread (request from a given user), because each servlet request is invoked in its own thread right. Any failure or jammed state from one request would also effect every user as well.

The usual way is to have a connection per request (either fetch it inside the servlet as needed, or create a threadLocal object). where the connection is fetched from a DataSource, and when we are finished with the connection object in our servlet, we invoke the connection.close() method, which doesn't actually close the connection, but usually releases it back to the JDBC connection pool, such as the commons-pool project that is bundled with tomcat.

Additionally, we usually configure the credentials for the data source in the context.xml or the servlet container (e.g. server.xml) as a JNDI data source name, where in our application we declare a reference to this JNDI named object to retreive the DataSource instance, and from there in each servlet where needed invoke DataSource.getConnection().

[Pacerier]

heys, btw just to check, is it true that if we do not explicitly connection.close then the connection will be closed automatically after x seconds?

[Tolls]

Entirely depends on the database settings.
If you don't explicitly close the connections on a webapp you will almost certainly hit the connection wall and be unable to connect...unless the webapp does very little.

You should always explicitly close resources you open.

As already said for your original question, do not share a connection within a multi-threaded environment. They are not thread safe.

[Pacerier]

ok thanks for the reply