JDBC - CallableStatement with Input and Output Parameters(Mysql)

Click here to watch in Youtube : https://www.youtube.com/watch?v=jrTKujzil-w&index=1&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge

JDBC - CallableStatement with Input and Output Parameters(Mysql)

JDBC - CallableStatement with Input and Output Parameters(Mysql)

Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `world`.`getCityName` $$
CREATE PROCEDURE `world`.`getCityName` 
   (IN CITY_ID INT, OUT CITY_NAME VARCHAR(255))
BEGIN
   SELECT Name INTO CITY_NAME
   FROM city
   WHERE ID = CITY_ID;
END $$

DELIMITER ;

------------------------------------------------------------

set @CITY_ID=1;
call getCityName(@CITY_ID,@CITY_NAME);
select @CITY_NAME;

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

            if (cityId == 0)
            {
                break;
            }

            jdbcCallableStatementDemo.getCityName(cityId);

        }
        scanner.close();
    }

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

            /*
             * Creates a CallableStatement object for calling database stored
             * procedures. The CallableStatement object provides methods for
             * setting up its IN and OUT parameters, and methods for executing
             * the call to a stored procedure.
             */

            callableStatement = connection.prepareCall(plSql);

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

            callableStatement.setInt(1, cityId);

            /*
             * Second parameter is OUT so register it
             */
            callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

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

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

            System.out.println("city Name : " + cityName);

        }
        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
city Name : Kabul
Enter City Id :5000
city Name : Bangalore
Enter City Id :5001
city Name : Channai
Enter City Id :0

To Download JDBCCallableStatementDemoINOUT_Mysql Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCCallableStatementDemoINOUT_Mysql.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