JAVA EE: JDBC - CallableStatement Cursor and multiple out param(Oracle)

Wednesday 15 July 2015

JDBC - CallableStatement Cursor and multiple out param(Oracle)

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

Stored Procedure :

create or replace PROCEDURE GET_COUNTRIES
(
  COUNTRY_CURSOR OUT SYS_REFCURSOR,
  REGION_ID_IN IN NUMBER,
  COUNTRY_ID_OUT OUT VARCHAR2, 
  COUNTRY_NAME_OUT OUT VARCHAR2,
  REGION_ID_OUT OUT VARCHAR2  
) AS 
BEGIN
  OPEN COUNTRY_CURSOR FOR 
  SELECT COUNTRY_ID,COUNTRY_NAME,REGION_ID INTO COUNTRY_ID_OUT,COUNTRY_NAME_OUT,REGION_ID_OUT
  FROM COUNTRIES WHERE REGION_ID=REGION_ID_IN;
END GET_COUNTRIES;

Click the below Image to Enlarge

JDBC - CallableStatement Cursor and multiple out param(Oracle)

JDBCCallableStatementDemo.java

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

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();
        Scanner scanner = new Scanner(System.in);
        while (true)
        {
            System.out.print("Enter the RegionId :");
            int regionId = scanner.nextInt();

            if (regionId == 0)
            {
                break;
            }

            jdbcCallableStatementDemo.getCountryInformation(regionId);

        }
        scanner.close();
    }

    private void getCountryInformation(int regionId)
    {
        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_COUNTRIES(?,?,?,?,?)}";

            callableStatement = connection.prepareCall(plSql);

            /*
             * Bind IN and OUT parameters
             */

            callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
            callableStatement.setInt(2, regionId);
            callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(4, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(5, java.sql.Types.INTEGER);

            /*
             * 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

Enter the RegionId :1
COUNTRY_ID : BE, COUNTRY_NAME : Belgium, REGION_ID : 1
COUNTRY_ID : CH, COUNTRY_NAME : Switzerland, REGION_ID : 1
COUNTRY_ID : DE, COUNTRY_NAME : Germany, REGION_ID : 1
COUNTRY_ID : DK, COUNTRY_NAME : Denmark, REGION_ID : 1
COUNTRY_ID : FR, COUNTRY_NAME : France, REGION_ID : 1
COUNTRY_ID : IT, COUNTRY_NAME : Italy, REGION_ID : 1
COUNTRY_ID : NL, COUNTRY_NAME : Netherlands, REGION_ID : 1
COUNTRY_ID : UK, COUNTRY_NAME : United Kingdom, REGION_ID : 1
Enter the RegionId :2
COUNTRY_ID : AR, COUNTRY_NAME : Argentina, REGION_ID : 2
COUNTRY_ID : BR, COUNTRY_NAME : Brazil, REGION_ID : 2
COUNTRY_ID : CA, COUNTRY_NAME : Canada, REGION_ID : 2
COUNTRY_ID : MX, COUNTRY_NAME : Mexico, REGION_ID : 2
COUNTRY_ID : US, COUNTRY_NAME : United States of America, REGION_ID : 2
Enter the RegionId :0