Creating Database Connection in Java Application

Creating the database connection in Java application is an expensive task. In this article I am going to explain creating the different database connections in Java application using JDBC drivers. We can configure the different database configurations in properties file and getting the respective databae connections passing the database type to the DBConnectionUtils class. Here are the steps to create the Database Utils classes.

1. Create the Properties file which contains the different Database connection properySQL Database Configurations.

data

## Mbase.mysql.driver=com.mysql.jdbc.Driver

ties(CSPROPERTIES.properties):

database.mysql.url=jdbc:mysql://localhost:3306/test

database.mysql.username=root
database.mysql.password=

## SQL Server Database Configurations.
database.sql.driver=net.sourceforge.jtds.jdbc.Driver
database.sql.url=jdbc:jtds:sqlserver://localhost:1433/SQLDB
database.sql.username=dbuser
database.sql.password=dbpwd

2. Creating the Singleton Resource properties class to read each property from the Properties file(CSProperties.java):

import java.util.ResourceBundle;

public class CSProperties {
  private static ResourceBundle resource = null;

    public static ResourceBundle getBundle(String resourcePath) {

        if (resource == null) {
            resource = ResourceBundle.getBundle(resourcePath);
            return resource;
        }
        return resource;
    }
}

Note:  Singleton is the Design Pattern, which returns only one object of this class when we trying to create objects of this class.

3. Creating the DBConnectionUtils class, which returns the Connection object based on the database type which is passed to the getConnection method(DBConnectionUtil.java):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

public class DBConnectionUtil {

    private static final Logger logger = Logger.getLogger(DBConnectionUtil.class);
    static ResourceBundle resource = CSProperties.getBundle("CSPROPERTIES");
    private static String url;
    private static String driver;
    private static String username;
    private static String password;

    public static Connection getConnection(String dbType) throws DBConnectionException {
        Connection connection = null;

        if (logger.isInfoEnabled()) {
            logger.info("--  Entered into DBConnectionUtil#getConnection() method.");
        }

        if (StringUtils.equalsIgnoreCase(dbType, "SQL")) {
            url = resource.getString("database.sql.url");
            driver = resource.getString("database.sql.driver");
            username = resource.getString("database.sql.username");
            password = resource.getString("database.sql.password");
        } else if (StringUtils.equalsIgnoreCase(dbType, "MySQL")) {
            url = resource.getString("database.mysql.url");
            driver = resource.getString("database.mysql.driver");
            username = resource.getString("database.mysql.username");
            password = resource.getString("database.mysql.password");
        }

        try {
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException ex) {
                logger.error("-- Exception has occured int the DBConnectionUtil#getConnection() method.", ex);
                throw new DBConnectionException("Driver class '" + driver + "' is missing in classpath.", ex);
            }

            connection = DriverManager.getConnection(url, username, password);

        } catch (SQLException ex) {
            logger.error("-- Exception has occured int the DBConnectionUtil#getConnection() method.", ex);
        }
        if (logger.isInfoEnabled()) {
            logger.info("--  Exited from DBConnectionUtil#getConnection() method.");
        }
        return connection;
    }
}

4. Creating Database specific Exception class to provide the custom Exception message to the exception class(DBConnectionException.java):

public class DBConnectionException extends RuntimeException {

    /**
     * Constructs a DBConnectionException with the given detail message.
     *
     * @param message The detail message of the DAOException.
     */
    public DBConnectionException(String message) {
        super(message);
    }

    /**
     * Constructs a DBConnectionException with the given root cause.
     *
     * @param cause The root cause of the DAOException.
     */
    public DBConnectionException(Throwable cause) {
        super(cause);
    }

    /**
     * Constructs a DBConnectionException with the given detail message and root
     * cause.
     *
     * @param message The detail message of the DAOException.
     * @param cause The root cause of the DAOException.
     */
    public DBConnectionException(String message, Throwable cause) {
        super(message, cause);
    }
}

5. Creating the DatabaseUtil class to handle the resource cleanup and taking care of some generic methods(DatabaseUtil.java):

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class DatabaseUtil {

    private DatabaseUtil() {
    }

    public static Date toSqlDate(java.util.Date date) {
        return (date != null) ? new Date(date.getTime()) : null;
    }

    public static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException e) {
                System.err.println("Closing Connection failed: " + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    public static void close(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
                statement = null;
            } catch (SQLException e) {
                System.err.println("Closing Statement failed: " + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
                resultSet = null;
            } catch (SQLException e) {
                System.err.println("Closing ResultSet failed: " + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection connection, Statement statement) {
        close(statement);
        close(connection);
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(resultSet);
        close(statement);
        close(connection);
    }
}

6. The final class to test the Database Connection using simple Java class:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestingConnection {

    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = DBConnectionUtil.getConnection("MySQL");
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT * FROM user");
            while (rs.next()) {
                System.out.println("First Prameter:  " + rs.getString(1));
                System.out.println("Second Prameter:  " + rs.getString(2));
                System.out.println("Third Prameter:  " + rs.getString(3));
                System.out.println("--------------------------");
            }
        } catch (SQLException se) {
            se.printStackTrace();
        } finally {
            DatabaseUtil.close(con, stmt, rs);
        }
    }
}

Output:

First Prameter:  1
Second Prameter:  Pramod
Third Prameter:  Ganta
--------------------------
First Prameter:  2
Second Prameter:  Suman
Third Prameter:  Gorintla
--------------------------