JAVA EE: JDBC DBCP DataSource

Friday, 12 September 2014

JDBC DBCP DataSource - Oracle

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

Click the below Image to Enlarge

JDBC DBCP DataSource - Oracle

DbcpBasicDataSource.java

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;

public class DbcpBasicDataSource
{

    public static DataSource getDbcpBasicDataSource()
    {

        BasicDataSource basicDataSource = new BasicDataSource();
        try
        {
            basicDataSource.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
            basicDataSource.setUsername("hr");
            basicDataSource.setPassword("hr");
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        return basicDataSource;
    }

    
}

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 dbcp2 BasicDataSource
         */
        DataSource dataSource = DbcpBasicDataSource.getDbcpBasicDataSource();
        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

---
---