How to use SINGLETON pattern in a application to access Database?

This is sample code for accessing database by applying Singleton design pattern which is also thread safe because of eager initialization.

/*

Java class code for the above purpose.

*/

import java.sql.Connection;

import java.sql.DriverManager;

public class DBConf {

    private static Connection connection;

       

    static{

        try{

            String dbUrl = "jdbc:mysql://dbhost:dbport/dbname";

            String dbUname = "dbusername";

            String dbPword = "dbpassword";

            

            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection(dbUrl, dbUname, dbPword);

        }

        catch(Exception ex){

            ex.printStackTrace();

        }

    }

    

    private DBConf(){}

    

    public static Connection getConnection(){

        return DBConf.connection;

    }

}

Look at the constructor which is defined as private so no one can instantiate it. The client can use the getConnection() method to get the Connection which is already eagerly loaded. This single connection can be used to access the Database by the whole application by not openning new connections.

Now a question comes, when to close the connection. My solution is just add a listener class to this application. 

/*

Add this code to your web.xml file. Please replace ] with > and [ with < in the following code.

*/

[listener]

        [listener-class]ApplicationWatch[/listener-class]

[/listener]

/*

Write the following class in your application default source directory.

*/

import javax.servlet.ServletContextListener;

import javax.servlet.ServletContextEvent;

public class ApplicationWatch implements ServletContextListener {

/* Application Startup Event */

public void contextInitialized(ServletContextEvent ce) {}

/* Application Shutdown Event */

public void contextDestroyed(ServletContextEvent ce) {

            try{

                DBConf.getConnection().close();

            }

            catch(Exception ex){

                ex.printStackTrace();

            }

        }

}

So, when the application will be destroyed (i.e. undeploying the application from the server) the connection will be closed.

Problems

1. This connection should be alive for the application lifetime. But the real scenario is, Database like MySQL shutdown the idle connection when it reaches its wait timeout, default is 8 hours.

But for, heavy hitted application for database connection, connection pooling is a better solution. Hope I will write about it in future.

Wish this article will help you. Thanks.

Hibernate Exception - java.net.SocketException: Broken pipe

In my current project I am using Hibernate ORM model for java to mysql bridge. And after deploying the module in a test sever I got the exception "java.net.SocketException: Broken pipe".

I have found the possible reasons may be, 

1. I didn't set the connection timeout parameters for the connection pool

2. The main scenario is, hibernate connection pool keeps the opened connection for a long time and mysql closes it when its idle time reaches for the opened connection.

3. When hibernate trys to do something with this connection which is closed by mysql got the exception.

Possible solution is, try to define the idle timeout value for the connection in hibernate lower than the mysql system connection idle timeout. This is very easy in hibernate.cfg.xml file to define the connection pool parameters and a lot of web resources can be found by google how to do it. 

Thanks. Hope it will help you.