Java Database connectivity with MYSQL using JDBCDataSource

In this Java web application development tutorial. Let’s see Java Database connectivity with MYSQL using JDBCDataSource. Complete step by step setup of MySQL then will move to define all the basic required classes and methods to perform database transactions.

What is JDBCDataSource

JDBCDataSource is an interface that extends CommonDataSource and Wrapper Class. DataSource interface contains two methods
getConnection() and getConnection(string username, string password). JDBCData Source is an alternative of DriverManager used to create a pool connection, set a maximum or minimum time for a pool.

What is JDBC DataSource Connection pool

A pool is a group of resources that is ready to provide the services. The biggest advantage of pooling is the reusability of resources that help to improve performance.

JDBC DataSource Connection pool is a group predefine ready to use connection objects. If we required to communicate with the database multiple time then it is not recommended to create a separate connection object every time. Because creating and destroying connection object every time will affect the performance of the application. To overcome this performance problem we use the DataSource Connection pooling.

If we want to create a Database connection then we request to Connection pool to provide a connection, By using this Connection we can communicate with the database. After completing the work we can return the connection to the pool instead of destroying it.

Advantage of the JDBC DataSource connection is we can use the same connection multiple times that definitely going to improve the performance of the application.

How to Install MYSQL

Below is the link of the video that will help you to install the Mysql and MySQL workbench in your machine.

[embedyt] https://www.youtube.com/watch?v=QRFPCZPoZXI[/embedyt]

System.properties file

Create a property file to define username, password or to manage the database connection parameters.

 
#Database connection Parameters
url=jdbc:mysql://localhost:3306/javawebapp
driver=com.mysql.jdbc.Driver
username=root
password=root
#DATABASE=JDBC
#service=javaBean
acquireIncrement = 10
initialPoolSize = 10
maxPoolSize = 100
minPoolSize = 10
timeout = 10

Add Maven dependency for Mysql in pom.xml

<dependency>
    groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.0.8</version>
</dependency>

Create a class JDBCDataSource.java under the utility package.

The method of JDBCDataSource class will help to read data from system.properties file and create a connection pool that we can use in the project at any place.

  • Get Connection
  • Close Connection
  • Transaction Rollback
package com.javawebapp.utility;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ResourceBundle;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCDataSource
{
  /**
     * JDBC Database connection pool ( DCP )
     */
    private static JDBCDataSource datasource;
    private JDBCDataSource() {
    }
    private ComboPooledDataSource cpds = null;
    /**
     * Create instance of Connection Pool
     *
     * @return
     */
    public static JDBCDataSource getInstance() {
        if (datasource == null) {
            ResourceBundle rb = ResourceBundle.getBundle("com.javawebapp.bundle.system");
            datasource = new JDBCDataSource();
            datasource.cpds = new ComboPooledDataSource();
            try {
                datasource.cpds.setDriverClass(rb.getString("driver"));
            } catch (Exception e) {
                e.printStackTrace();
            }
            datasource.cpds.setJdbcUrl(rb.getString("url"));
            datasource.cpds.setUser(rb.getString("username"));
            datasource.cpds.setPassword(rb.getString("password"));
            datasource.cpds.setInitialPoolSize(new Integer((String) rb .getString("initialPoolSize")));
            datasource.cpds.setAcquireIncrement(new Integer((String) rb.getString("acquireIncrement")));
            datasource.cpds.setMaxPoolSize(new Integer((String) rb.getString("maxPoolSize")));
            datasource.cpds.setMaxIdleTime(DataUtility.getInt(rb.getString("timeout")));
            datasource.cpds.setMinPoolSize(new Integer((String) rb.getString("minPoolSize")));
        }
        return datasource;
    }
    /**
     * Gets the connection from ComboPooledDataSource
     *
     * @return connection
     */
    public static Connection getConnection() throws Exception {
        return getInstance().cpds.getConnection();
    }
    /**
     * Closes a connection4
     *
     * @param connection
     * @throws Exception
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
            }
        }
    }
    
}