JAVA EE: JDBC - CallableStatement Cursor(Oracle)

Tuesday 14 July 2015

JDBC - CallableStatement Cursor(Oracle)

Click here to watch in Youtube :
https://www.youtube.com/watch?v=G4chXa8n6rA&list=UUhwKlOVR041tngjerWxVccw

Stored Procedure:

create or replace 
PROCEDURE GET_ALL_COUNTRIES 
(
   country_cursor OUT SYS_REFCURSOR
) AS 
BEGIN
  OPEN country_cursor FOR 
   SELECT * FROM COUNTRIES ;
END GET_ALL_COUNTRIES;

Click the below Image to Enlarge

JDBC - CallableStatement Cursor(Oracle)

JDBCCallableStatementDemo.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

public class JDBCCallableStatementDemo
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";

    // Database credentials
    static final String USERNAME = "hr";
    static final String PASSWORD = "hr";

    public static void main(String[] args)
    {
        JDBCCallableStatementDemo jdbcCallableStatementDemo = new JDBCCallableStatementDemo();
        jdbcCallableStatementDemo.getAllCountryInformation();
    }

    private void getAllCountryInformation()
    {
        Connection connection = null;
        CallableStatement callableStatement = null;
        try
        {
            /*
             * Register the JDBC driver in DriverManager
             */

            Class.forName(JDBC_DRIVER);

            /*
             * Establish connection to the Database using DriverManager
             */

            connection = DriverManager
                    .getConnection(DB_URL, USERNAME, PASSWORD);

            String plSql = "{call GET_ALL_COUNTRIES(?)}";

            callableStatement = connection.prepareCall(plSql);
            
            callableStatement.registerOutParameter(1,  OracleTypes.CURSOR);

            /*
             * Use execute method to run the stored procedure.
             */

            callableStatement.executeQuery();

            ResultSet rs=(ResultSet)callableStatement.getObject(1);
            
            while(rs.next())
            {
                System.out.print("COUNTRY_ID : "+rs.getString(1));
                System.out.print(", COUNTRY_NAME : "+rs.getString(2));
                System.out.println(", REGION_ID : "+rs.getString(3));
            }

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

    }
}

Output

COUNTRY_ID : AR, COUNTRY_NAME : Argentina, REGION_ID : 2
COUNTRY_ID : AU, COUNTRY_NAME : Australia, REGION_ID : 3
COUNTRY_ID : BE, COUNTRY_NAME : Belgium, REGION_ID : 1
COUNTRY_ID : BR, COUNTRY_NAME : Brazil, REGION_ID : 2
COUNTRY_ID : CA, COUNTRY_NAME : Canada, REGION_ID : 2
COUNTRY_ID : CH, COUNTRY_NAME : Switzerland, REGION_ID : 1
COUNTRY_ID : CN, COUNTRY_NAME : China, REGION_ID : 3
COUNTRY_ID : DE, COUNTRY_NAME : Germany, REGION_ID : 1
COUNTRY_ID : DK, COUNTRY_NAME : Denmark, REGION_ID : 1
COUNTRY_ID : EG, COUNTRY_NAME : Egypt, REGION_ID : 4
COUNTRY_ID : FR, COUNTRY_NAME : France, REGION_ID : 1
COUNTRY_ID : IL, COUNTRY_NAME : Israel, REGION_ID : 4
COUNTRY_ID : IN, COUNTRY_NAME : India, REGION_ID : 3
COUNTRY_ID : IT, COUNTRY_NAME : Italy, REGION_ID : 1
COUNTRY_ID : JP, COUNTRY_NAME : Japan, REGION_ID : 3
COUNTRY_ID : KW, COUNTRY_NAME : Kuwait, REGION_ID : 4
COUNTRY_ID : ML, COUNTRY_NAME : Malaysia, REGION_ID : 3
COUNTRY_ID : MX, COUNTRY_NAME : Mexico, REGION_ID : 2
COUNTRY_ID : NG, COUNTRY_NAME : Nigeria, REGION_ID : 4
COUNTRY_ID : NL, COUNTRY_NAME : Netherlands, REGION_ID : 1
COUNTRY_ID : SG, COUNTRY_NAME : Singapore, REGION_ID : 3
COUNTRY_ID : UK, COUNTRY_NAME : United Kingdom, REGION_ID : 1
COUNTRY_ID : US, COUNTRY_NAME : United States of America, REGION_ID : 2
COUNTRY_ID : ZM, COUNTRY_NAME : Zambia, REGION_ID : 4
COUNTRY_ID : ZW, COUNTRY_NAME : Zimbabwe, REGION_ID : 4