Cache JDBC connections

Question

I have a small e-commerce application which relies heavliy on the database. I've seen some performance issues with it, primary when it comes to establishing a connection to the database. I'm not a DBA, or any other deep DB knowledge - but I know that it makes sense to cache the connections instead of establishing a new one all the time.

Below is my method for getting the connection:

public Connection getConnection() throws SQLException, ClassNotFoundException  {
    Class.forName("com.mysql.jdbc.Driver"); 
    con=DriverManager.getConnection("jdbc:mysql://182.31.456.32:3306/"+database+"",""+username+"",""+password+""); 
   // stm=con.createStatement(); 
    return con;
}

My question is - how can I easily cache a current connection and reuse it instead of creating a new one using JDBC/Java?

Thanks.

Answer 1

In a situation where you have a pretty large e-commerce application, you don't usually code DB connection they way you show in your code.

We make use of connection pools.

I guess, your e-commerce application would be deployed in some application server, use connection pool facility of that server to manage your database connections.

You can use one of connection pool APIs like C3P0 etc to explicitly code pooling functionality if its an application without a server.

I guess the thing that you are calling a connection caching is basically to ask to implement a Singleton. There would be lots of examples on Internet as how to implement a singleton but you shouldn't be closing connections in code then.

There is something called connection-timeout time, you need to set that properly if going with single connection caching solution.

Hope it helps !!

Answer 2

What you need is a connection pool that you can easily configure out of the box in Tomcat, this way the connection pool will be managed directly by Tomcat which avoids a lot of integration issues, guarantees a full compatibility and avoids adding new dependencies.

Here are the steps to follow:

1. Define the connection pool globally

In conf/contex.xml you need to define your connection pool as Resource so for example in you case it could be something like that:

<Context>

    ...
    <Resource name="jdbc/myPool" auth="Container" type="javax.sql.DataSource"
                initialSize="5" maxActive="20" minIdle="5" maxIdle="15" maxWait="10000"
                validationQuery="SELECT 1" validationQueryTimeout="5"
                testWhileIdle="true" testOnBorrow="true" testOnReturn="false"
                timeBetweenEvictionRunsMillis="30000" minEvictableIdleTimeMillis="60000"
                removeAbandoned="true" removeAbandonedTimeout="300" logAbandoned="false"
                poolPreparedStatements="true"
                username="myUsername" password="myPassword"
                driverClassName="com.mysql.jdbc.Driver" 
                url="jdbc:mysql://182.31.456.32:3306/mydb?autoReconnect=true" />
</Context>

2. Declare your connection pool for your webapp

In the web.xml of your webapp, you need to define it locally using a resource-ref as next:

<web-app>
    ...
    <resource-ref>
        <res-ref-name>jdbc/myPool</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
    ...
</web-app>

3. Access to my datasource from my code

You can then access to your DataSource using JNDI

Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myPool");
Connection conn = ds.getConnection();

4. Deploy your JDBC driver

Your JDBC driver needs to be available in the Common ClassLoader of Tomcat as it is a global resource, such that you need to put the jar of your driver in tomcat/lib

---

More details about JNDI Datasource in Tomcat here

Answer 3

You could use a singleton class like the one below:

public class DBHandler{

    private static Connection conn = null;

    private DBHandler(String connString){
        Class.forName("com.mysql.jdbc.Driver"); 
        conn=DriverManager.getConnection(connString);    
    }

    public static void getConnection(String connString) {
        if (conn == null)
            new DBHandler(String connString);
        return conn;
    }
}

It will create the connection during the first DBHandler.getConnection()call and caches it in the conn field. Later DBHandler.getConnection() calls will return this cached connection.

If your application needs to handle a lot of DB requests,please read about Connection Pooling as well.