JAVA EE: JDBC : JDBC DataSource [Oracle DB]

Saturday, 6 September 2014

JDBC : JDBC DataSource [Oracle DB]

🚀 Master Java database programming, JDBC DataSource, and Oracle DB!

Why Use JDBC DataSource Instead of DriverManager?

In modern Java enterprise applications, the standard javax.sql.DataSource interface is preferred over DriverManager. DataSource provides complete abstraction of database connection details, enables connection pooling, and integrates smoothly with JNDI and enterprise application servers.

For Oracle databases, Oracle provides its own implementation class called OracleDataSource (located in the oracle.jdbc.pool package inside ojdbc.jar), allowing you to configure connection properties using clean getter and setter methods.

Database Table Structure (HR.EMPLOYEES)

This demonstration queries the standard EMPLOYEES table inside Oracle's HR sample schema:

SELECT employee_id, first_name, last_name, email, phone_number 
FROM employees;

Complete Java Source Code Example

The following Java program configures an OracleDataSource instance, retrieves a connection, and reads employee records:

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 oracle.jdbc.pool.OracleDataSource;

public class JDBCDataSourceDemo {

    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
    static final String USER = "hr";
    static final String PASS = "hr";

    // Factory method to configure OracleDataSource
    public static DataSource getOracleDataSource() throws SQLException {
        OracleDataSource oracleDS = new OracleDataSource();
        oracleDS.setURL(DB_URL);
        oracleDS.setUser(USER);
        oracleDS.setPassword(PASS);
        return oracleDS;
    }

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

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

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

            // Step 2: Get Connection from DataSource
            conn = dataSource.getConnection();

            // Step 3: Create Statement and execute SQL query
            stmt = conn.createStatement();
            String sql = "SELECT employee_id, first_name, last_name, email, phone_number FROM employees";
            rs = stmt.executeQuery(sql);

            // Step 4: Process the ResultSet
            System.out.println("ID | Name | Email | Phone");
            System.out.println("----------------------------------------");
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String firstName = rs.getString("first_name");
                String lastName = rs.getString("last_name");
                String email = rs.getString("email");
                String phone = rs.getString("phone_number");

                System.out.println(empId + " | " + firstName + " " + lastName + " | " + email + " | " + phone);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Step 5: Clean up database resources safely
            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 OracleDataSource: Instantiate OracleDataSource and configure it with setURL(), setUser(), and setPassword().
Program to Interfaces: Return javax.sql.DataSource so your application code is decoupled from vendor-specific classes.
Connection Acquisition: Simply invoke dataSource.getConnection() without needing explicit driver class loading calls.
Resource Cleanup: Always close the ResultSet, Statement, and Connection inside a finally block to prevent database resource leaks.

Related Tutorials You Might Enjoy

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

Click the below Image to Enlarge

JDBC DataSource [Oracle DB]

OracleDataSource.java

import javax.sql.DataSource;

public class OracleDataSource
{

    public static DataSource getOracleDataSource()
    {

        oracle.jdbc.pool.OracleDataSource oracleDS = null;
        try
        {
            oracleDS = new oracle.jdbc.pool.OracleDataSource();
            oracleDS.setURL("jdbc:oracle:thin:@localhost:1521:xe");
            oracleDS.setUser("hr");
            oracleDS.setPassword("hr");
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        return oracleDS;
    }

}

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.getEmployeeInformation();

    }

    private void getEmployeeInformation()
    {
        Connection connection = null;
        Statement stmt = null;
        /*
         * Get the OracleDataSource
         */
        DataSource dataSource = OracleDataSource.getOracleDataSource();
        try
        {
            /*
             *  Get connection from the DataSource
             */
            connection = dataSource.getConnection();
            
            /*
             *  Execute the Query
             */
            
            stmt = connection.createStatement();
            String sql = "select employee_id,first_name,last_name,email,phone_number from employees";
            ResultSet rs = stmt.executeQuery(sql);

            /*
             *  Iterate the ResultSet and get each row
             *  Information.
             */
            while( rs.next() )
            {
                /*
                 * Retrieve by column name
                 */
                int id = rs.getInt("employee_id");
                String firstName = rs.getString("first_name");
                String lastName = rs.getString("last_name");
                String email = rs.getString("email");
                String phoneNumber = rs.getString("phone_number");

                /*
                 * Display values
                 */
                System.out.print("employee_id: " + id);
                System.out.print(", first_name: " + firstName);
                System.out.print(", last_name: " + lastName);
                System.out.print(", email: " + email);
                System.out.println(", phone_number: " + phoneNumber);
            }
            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

employee_id: 100, first_name: Steven, last_name: King, email: SKING, phone_number: 515.123.4567
employee_id: 101, first_name: Neena, last_name: Kochhar, email: NKOCHHAR, phone_number: 515.123.4568
employee_id: 102, first_name: Lex, last_name: De Haan, email: LDEHAAN, phone_number: 515.123.4569
employee_id: 103, first_name: Alexander, last_name: Hunold, email: AHUNOLD, phone_number: 590.423.4567
employee_id: 104, first_name: Bruce, last_name: Ernst, email: BERNST, phone_number: 590.423.4568
employee_id: 105, first_name: David, last_name: Austin, email: DAUSTIN, phone_number: 590.423.4569
employee_id: 106, first_name: Valli, last_name: Pataballa, email: VPATABAL, phone_number: 590.423.4560
employee_id: 107, first_name: Diana, last_name: Lorentz, email: DLORENTZ, phone_number: 590.423.5567
employee_id: 108, first_name: Nancy, last_name: Greenberg, email: NGREENBE, phone_number: 515.124.4569
employee_id: 109, first_name: Daniel, last_name: Faviet, email: DFAVIET, phone_number: 515.124.4169