How to manage db connections on server?

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*/ Connec开发者_JAVA百科tion 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.

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?

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.

Singleton should be the JNDI pool connection itself; Database class with getConnection(), query methods et al should NOT be singleton, but can be static if you prefer.

In this way the pool exists indefinitely, available to all users, while query blocks use dataSource.getConnection() to draw a connection from the pool; exec the query, and then close statement, result set, and connection (to return it to the pool).

Also, JNDI lookup is quite expensive, so it makes sense to use a singleton in this case.