JAVA EE: JDBC : JDBC DataSource [Mysql DB]

Saturday, 6 September 2014

JDBC : JDBC DataSource [Mysql DB]

🚀 Master Java database programming, JDBC DataSource, and MySQL!

Why Use JDBC DataSource with MySQL?

In standard Java database applications, obtaining connections through the javax.sql.DataSource interface is preferred over using DriverManager. DataSource objects decouple database connection details and credentials from your application logic, making your code easier to maintain, configure, and integrate with enterprise containers.

MySQL provides the MysqlDataSource class (located in the com.mysql.jdbc.jdbc2.optional package within mysql-connector-java.jar), which implements the DataSource interface and provides clean setter methods to configure database connection properties.

The Database Table (world.city)

In this demonstration, we query the standard city table available in MySQL's sample world database:

SELECT ID, Name, CountryCode, District, Population 
FROM city;

Complete Java Source Code Example

Here is the complete Java implementation showing how to configure MysqlDataSource, acquire a connection, execute a Statement, and iterate through the ResultSet:

package com.ram.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class JDBCDataSourceDemo {

    static final String DB_URL = "jdbc:mysql://localhost:3306/world";
    static final String USER = "root";
    static final String PASS = "root";

    // Factory method to create and configure MysqlDataSource
    public static DataSource getMySQLDataSource() {
        MysqlDataSource mysqlDS = new MysqlDataSource();
        mysqlDS.setURL(DB_URL);
        mysqlDS.setUser(USER);
        mysqlDS.setPassword(PASS);
        return mysqlDS;
    }

    public static void main(String[] args) {
        JDBCDataSourceDemo demo = new JDBCDataSourceDemo();
        demo.getCityInformation();
    }

    public void getCityInformation() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // Step 1: Obtain DataSource instance
            DataSource dataSource = getMySQLDataSource();

            // Step 2: Acquire connection from DataSource
            conn = dataSource.getConnection();

            // Step 3: Create Statement and execute query
            stmt = conn.createStatement();
            String sql = "SELECT ID, Name, CountryCode, District, Population FROM city";
            rs = stmt.executeQuery(sql);

            // Step 4: Process ResultSet
            System.out.println("ID | Name | CountryCode | District | Population");
            System.out.println("-------------------------------------------------");
            while (rs.next()) {
                int id = rs.getInt("ID");
                String name = rs.getString("Name");
                String countryCode = rs.getString("CountryCode");
                String district = rs.getString("District");
                int population = rs.getInt("Population");

                System.out.println(id + " | " + name + " | " + countryCode + " | " + district + " | " + population);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Step 5: Clean up database resources
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Key Steps & Best Practices

Configure MysqlDataSource: Instantiate MysqlDataSource and populate the URL, username, and password via its setter methods.
Program to the DataSource Interface: Return javax.sql.DataSource to keep your client code generic and decoupled from vendor-specific driver classes.
Simpler Connection Setup: With dataSource.getConnection(), you eliminate the need for explicit Class.forName() driver registration calls.
Resource Cleanup: Always close the ResultSet, Statement, and Connection inside a finally block to avoid leaking system resources.

Related Tutorials You Might Enjoy

Click here to watch in Youtube : https://www.youtube.com/watch?v=Bj1V1JNBTn8

Click the below Image to Enlarge

JDBC DataSource [Mysql DB]

MyqlDataSource.java

import javax.sql.DataSource;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class MyqlDataSource
{

    public static DataSource getMySQLDataSource()
    {

        MysqlDataSource mysqlDS = null;
        try
        {
            mysqlDS = new MysqlDataSource();
            mysqlDS.setURL("jdbc:mysql://localhost:3306/world");
            mysqlDS.setUser("root");
            mysqlDS.setPassword("root");
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        return mysqlDS;
    }

}

JDBCDataSourceDemo.java

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

import javax.sql.DataSource;

public class JDBCDataSourceDemo
{

    public static void main( String[] args )
    {
        JDBCDataSourceDemo jdbcDataSourceDemo = new JDBCDataSourceDemo();
        jdbcDataSourceDemo.getCityInformation();

    }

    private void getCityInformation()
    {
        Connection connection = null;
        Statement stmt = null;
        /*
         * Get the MysqlDataSource
         */
        DataSource dataSource = MyqlDataSource.getMySQLDataSource();
        try
        {
            /*
             *  Get connection from the DataSource
             */
            connection = dataSource.getConnection();
            
            /*
             *  Execute the Query
             */
            
            stmt = connection.createStatement();
            String sql = "select ID,Name,CountryCode,District,Population from city";
            ResultSet rs = stmt.executeQuery(sql);

            /*
             *  Iterate the ResultSet and get each row
             *  Information.
             */
            while( rs.next() )
            {
                /*
                 * Retrieve by column name
                 */
                int id = rs.getInt("ID");
                String name = rs.getString("Name");
                String countryCode = rs.getString("CountryCode");
                String district = rs.getString("District");
                int population = rs.getInt("Population");

                /*
                 * Display values
                 */
                System.out.print("ID: " + id);
                System.out.print(", Name: " + name);
                System.out.print(", CountryCode: " + countryCode);
                System.out.print(", District: " + district);
                System.out.println(", Population: " + population);
            }
            rs.close();
        }
        catch( SQLException se )
        {
            se.printStackTrace();
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if( stmt != null )
                {
                    stmt.close();
                }
            }
            catch( SQLException sqlException )
            {
                sqlException.printStackTrace();
            }
            try
            {
                if( connection != null )
                {
                    connection.close();
                }
            }
            catch( SQLException sqlException )
            {
                sqlException.printStackTrace();
            }

        }

    }
}

Output

ID: 2, Name: Qandahar, CountryCode: AFG, District: Qandahar, Population: 237500
ID: 3, Name: Herat, CountryCode: AFG, District: Herat, Population: 100
ID: 4, Name: Mazar-e-Sharif, CountryCode: AFG, District: Balkh, Population: 127800
ID: 5, Name: Amsterdam, CountryCode: NLD, District: Noord-Holland, Population: 50000
ID: 6, Name: Rotterdam, CountryCode: NLD, District: Zuid-Holland, Population: 4000
ID: 7, Name: Haag, CountryCode: NLD, District: Zuid-Holland, Population: 440900
ID: 8, Name: Utrecht, CountryCode: NLD, District: Utrecht, Population: 234323
ID: 9, Name: Eindhoven, CountryCode: NLD, District: Noord-Brabant, Population: 201843
ID: 10, Name: Tilburg, CountryCode: NLD, District: Noord-Brabant, Population: 193238
ID: 11, Name: Groningen, CountryCode: NLD, District: Groningen, Population: 172701