how to get one JDBC Connection for one user in application, if I have 50 users

Question

I am creating a jdbc connection in web application.suppose ,50 users will use the application. currently, my application taking more than 20 connection for single user. how to improve the performance?

public class AuditConnection 
{
private static String URL = 
"jdbc:postgresql://localhost:5432/MDM_Audit? 
user=rajan&password=rajan";

private static String driverName = "org.postgresql.Driver";   

private static Connection con;

public static Connection getConnection() {
    try {
        Class.forName(driverName);
        try {
            con = DriverManager.getConnection(URL);
        } catch (SQLException ex) {
            System.out.println("Failed to create the database connection."); 
        }
    } catch (ClassNotFoundException ex) {
        // log an exception. for example:
        System.out.println("Driver not found."); 
    }
    return con;
}

}

Answer 1

Session & Context

In a web app, you should not be thinking about one database connection per user. Defining “user” in a web app is a bit amorphous. A user could be using multiple browser tabs/windows, or even multiple browsers. The user could be starting and closing sessions, or sessions could be expiring, and the user reconnecting.

Instead, think in terms of:

• Session
  Each time a user accesses your web app in a browser tab/window, a session is launched. The Java Servlet environment is built to handle the technical details on your behalf, handing you a HttpSession object. The session is closed when the user closes the browser tab/window, or after a time-out of inactivity, or when you programmatically close it, or when the Servlet container determines a problem such as failed network connection.
  • Each session includes a key-value store of “attributes” where you can keep objects scoped for this session.
• Context
  The word “context” is Servlet-speak for your entire web app at runtime. The context is established before the first user request is processed, and is closed after the last response is sent.
  • On the ServletContext object you will find a key-store of “attributes” similar to the session’s attributes store.

The Servlet spec defines hooks into the lifecycle of both the session and context.

The hook for the context lifecycle is to write a class that implements ServletContextListener. You implement a pair of methods, one called by the Servlet container before the first user connection is handled (your web app is launching), and the other called after the last response is sent out (your web app is shutting-down). Search Stack Overflow for much existing coverage of this topic.

DataSource

Rather than establishing connections as you show there, it is generally better to use a DataSource object.

The JDBC driver from jdbc.postgresql.org provides an implementation of DataSource. See this Question for details: Produce a DataSource object for Postgres JDBC, programmatically.

One benefit of using the DataSource interface is that if you ever change to using a different JDBC driver (such as the pgjdbc-ng driver for Postgres), you need not change any of your calls to getConnection littered throughout your codebase.

The context starting-up hook discussed above is an ideal place for you to load your driver, test a connection to the database, and configure a DataSource object. Store that DataSource object in the “attributes” key-value store on the ServletContext mentioned above.

When you need a connection, access the ServletContext object to retrieve the DataSource your stored there as an attribute. Call DataSource::getConnection to obtain a Connection for your database.

Be sure to close the Connection object when finished with it. Learn to use the try-with-resources feature to make this closing chore a bit more elegant and easy in your code.

Connection pooling

Many people will suggest using connection-pooling. The theory here is that obtaining a connection is relatively expensive, taking a significant amount of time and CPU cycles.

Without pooling, we get a fresh connection to the database, do a bit of limited work, and then close and discard that connection. The Connection object goes out of scope and becomes a candidate for garbage-collection. On the database side, the process that was established by the Postgres Postmaster to handle that connection is closed, and memory freed-up. When a new connection is requested, the Postmaster must go to the bother of setting up a new process and allocating memory in the host OS.

With pooling, a bunch of existing database connections are held open, waiting to be used. When your app needs database work done, a connection is retrieved from the pool, you do your work, and then when you think you are closing the connection, the pool actually intervenes to keep that connection open and at the ready for future use. So every interaction with the database no longer means creating and destroying a connection with the database server.

If you go this route, the work you did above to use DataSource pays off. You switch from using a fresh-connection-each-time implementation of the DataSource interface with a connection-pooling implementation of the interface. So none of your code using the database need change.

Personally, I no longer use database connection pooling. Experiments of mine showed the premise of expensive connections to be exaggerated. Another problem: you must worry about connections being left in proper connection. For example, no pending transactions left open. Yet another problem: Trying to decide on a connection pooling implementation. There have been several over the years, and all have had various issues and problems, thereby inspiring yet another implementation. The upshot: The risk-benefit ratio of pooling is not good enough in my judgement.

Answer 2

You can keep the Connection opened without closing

Use Connection Pooling

(or)

Or you can save the connection object in the session and retrieve it in the servlet and pass it to the function in your DAO class whenever needed.