JDBC Stored Procedure (Oracle) - Playlist

Click here to watch in Youtube : 
https://www.youtube.com/watch?v=QZmZ8hYBJgk&list=PLmCsXDGbJHdhdfmg-2pwGVQbmPd7yhyjy

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

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) 

JDBC - CallableStatement Cursor and multiple out param(Oracle) 

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

To Download JDBCCallableStatementDemoOracleCursorMultiOutApp Project Click the below link
https://sites.google.com/site/javaee4321/jdbc/JDBCCallableStatementDemoOracleCursorMultiOutApp.zip?attredirects=0&d=1

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

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)

JDBC - CallableStatement Cursor(Oracle)

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

To Download JDBCCallableStatementDemoOracleCursorApp Project Click the below link
https://sites.google.com/site/javaee4321/jdbc/JDBCCallableStatementDemoOracleCursorApp.zip?attredirects=0&d=1

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

JDBC - CallableStatement In Out Parameters (Oracle)

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

Stored Procedure :

CREATE OR REPLACE PROCEDURE GET_COUNTRY_NAME 
(
  COUNTRY_ID_INPUT IN VARCHAR2 
, COUNTRY_NAME_OUTPUT OUT VARCHAR2 
) AS 
BEGIN
  SELECT COUNTRY_NAME INTO COUNTRY_NAME_OUTPUT FROM COUNTRIES WHERE COUNTRY_ID=COUNTRY_ID_INPUT;
END GET_COUNTRY_NAME;

Click the below Image to Enlarge

JDBC - CallableStatement In Out Parameters (Oracle) 

JDBC - CallableStatement In Out Parameters (Oracle) 

JDBC - CallableStatement In Out Parameters (Oracle) 

JDBCCallableStatementDemo.java

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

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 CountryId :");
            String countryId = scanner.nextLine();

            if (countryId.equals("exit"))
            {
                break;
            }

            jdbcCallableStatementDemo.getCountryName(countryId);

        }
        scanner.close();
    }

    private void getCountryName(String countryId)
    {
        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_COUNTRY_NAME(?,?)}";

            callableStatement = connection.prepareCall(plSql);

            /*
             * Bind IN parameter first, then bind OUT parameters
             */

            callableStatement.setString(1, countryId);

            /*
             * Register OUT Parameters
             */
            callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

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

            callableStatement.execute();

            /*
             * Retrieve countryName with getXXX method
             */
            String countryName = callableStatement.getString(2);

            System.out.println("countryName : " + countryName);

        }
        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 CountryId :AR
countryName : Argentina
Enter the CountryId :IN
countryName : India
Enter the CountryId :exit

To Download JDBCCallableStatementDemoOracleINOUTApp Project Click the below link
https://sites.google.com/site/javaee4321/jdbc/JDBCCallableStatementDemoOracleINOUTApp.zip?attredirects=0&d=1

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

JDBC Stored Procedure (Mysql) - Playlist

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

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

C3PO Connection Pooling - Playlist

Click here to watch in Youtube : 
https://www.youtube.com/watch?v=5gSn9zt5qrA&list=PLmCsXDGbJHdjYeEUg5cwksuJiTZq4FLQc

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

DBCP Connection Pooling - Playlist

Click here to watch in Youtube : 
https://www.youtube.com/watch?v=OqBXwAC6MSM&list=PLmCsXDGbJHdj2AOJzzLs41gU7uz6uX-dP

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

Bone CP Connection Pooling - Playlist

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

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

JDBC : CallableStatement Introduction.

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

Click the below Image to Enlarge

JDBC : CallableStatement Introduction.

JDBC : CallableStatement Introduction.

JDBC : CallableStatement Introduction.

JDBC : CallableStatement Introduction.

JDBC : CallableStatement Introduction.

Click here to know more about CallableStatment methods:

https://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

JDBC - CallableStatement Multiple In Out Parameters (Mysql)

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

Click the below Image to Enlarge

JDBC - CallableStatement Multiple In Out Parameters (Mysql)

JDBC - CallableStatement Multiple In Out Parameters (Mysql)

Create Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `world`.`getCityInfo` $$
CREATE PROCEDURE `world`.`getCityInfo` 
   (IN CITY_ID_INPUT INT,
    IN CITY_POPULATION_INPUT INT,
    OUT CITY_NAME_OUT VARCHAR(255),
    OUT CITY_COUNTRY_CODE_OUT VARCHAR(255),
    OUT CITY_DISTRICT_OUT  VARCHAR(255),
    OUT CITY_POPULATION_OUT INT(11))
BEGIN
   SELECT Name,CountryCode,District,Population INTO CITY_NAME_OUT,CITY_COUNTRY_CODE_OUT,CITY_DISTRICT_OUT, 
   CITY_POPULATION_OUT
   FROM city
   WHERE ID = CITY_ID_INPUT AND Population > CITY_POPULATION_INPUT;
END $$

DELIMITER ;

Call Stored Procedure

set @CITY_ID_INPUT=1;
set @CITY_POPULATION_INPUT=1000;
call getCityInfo(@CITY_ID_INPUT,@CITY_POPULATION_INPUT,@CITY_NAME_OUT,@CITY_COUNTRY_CODE_OUT,
     @CITY_DISTRICT_OUT,@CITY_POPULATION_OUT);
select @CITY_NAME_OUT,@CITY_COUNTRY_CODE_OUT,@CITY_DISTRICT_OUT,@CITY_POPULATION_OUT;

JDBCCallableStatementDemo.java

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

public class JDBCCallableStatementDemo
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL      = "jdbc:mysql://localhost:3306/world";

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

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

        Scanner scanner = new Scanner(System.in);
        while( true )
        {
            System.out.print("Enter City Id :");
            int cityId = scanner.nextInt();
            System.out.print("Enter City population :");
            int population = scanner.nextInt();

            if( cityId == 0 && population == 0)
            {
                break;
            }

            jdbcCallableStatementDemo.getCityInfo(cityId,population);

        }
        scanner.close();
    }

    private void getCityInfo( int cityId , int population )
    {
        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 getCityInfo (?,?,?,?,?,?)}";

            callableStatement = connection.prepareCall(plSql);

            /*
             * Bind IN parameter first, then bind OUT parameters
             */

            callableStatement.setInt(1, cityId);
            callableStatement.setInt(2, population);

            /*           
             * Register OUT Parameters            
             */
            callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(4, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(6, java.sql.Types.INTEGER);

            /*
             * Use execute method to run the stored procedure.
             */
            callableStatement.execute();

            /*
             * Retrieve cityName,countryCode,district and cityPopulation with getXXX method
             */
            String cityName = callableStatement.getString(3);
            String countryCode = callableStatement.getString(4);
            String district = callableStatement.getString(5);
            int cityPopulation = callableStatement.getInt(6);

            System.out.println("city Name : " + cityName+", countryCode : " +countryCode 
                    +", district : "+district+", population : "+cityPopulation);

        }
        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 City Id :1
Enter City population :1000
city Name : Kabul, countryCode : AFG, district : Kabol, population : 1780000
Enter City Id :0
Enter City population :0

To Download JDBCCallableStatementDemoMultipleInOutParamsMysqlApp Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCCallableStatementDemoMultipleInOutParamsMysqlApp.zip?attredirects=0&d=1

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial