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

I've not much experience with PostgreSql, but all the web applications I've worked on have used a single connection per set of actions on a page, closing it and disposing it when finished.

This allows the server to pool connections and stops problems such as the one that you are experiencing.

Answer 2

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 provides a connection pooling facility 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.

Hope this helps.