multithreading - How to manage db connections on server?

Question

I have a severe problem with my database connection in my web application. Since I use a single database connection for the whole application from singleton Database class, if i try concurrent db operations (two users) the database rollsback the transactions. This is my static method used:

All threads/servlets call static Database.doSomething(...) methods, which in turn call the the below method.

private static /* synchronized*/ Connection getConnection(final boolean autoCommit) throws SQLException {
    if (con == null) {
        con = new MyRegistrationBean().getConnection();
    }
    con.setAutoCommit(true); //TODO
    return con;
}

What's the recommended way to manage this db connection/s I have, so that I don't incurr in the same problem.

Answer 1

Keeping a Connection open forever is a very bad idea. It doesn't have an endless lifetime, your application may crash whenever the DB times out the connection and closes it. Best practice is to acquire and close Connection, Statement and ResultSet in the shortest possible scope to avoid resource leaks and potential application crashes caused by the leaks and timeouts.

Since connecting the DB is an expensive task, you should consider using a connection pool to improve connecting performance. A decent applicationserver/servletcontainer usually already provides a connection pool feature in flavor of a JNDI DataSource. Consult its documentation for details how to create it. In case of for example Tomcat you can find it here.

Even when using a connection pool, you still have to write proper JDBC code: acquire and close all the resources in the shortest possible scope. The connection pool will on its turn worry about actually closing the connection or just releasing it back to pool for further reuse.

You may get some more insights out of this article how to do the JDBC basics the proper way. As a completely different alternative, learn EJB and JPA. It will abstract away all the JDBC boilerplate for you into oneliners.

Hope this helps.

See also:

• Is it safe to use a static java.sql.Connection instance in a multithreaded system?
• Am I Using JDBC Connection Pooling?
• How should I connect to JDBC database / datasource in a servlet based application?
• When is it necessary or convenient to use Spring or EJB3 or all of them together?