Friday 8 May 2015

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
  • 2 comments:

    1. get mobile application and website application with well tested

      ReplyDelete
    2. You are doing a good job and sharing your knowledge to others! it was one of the good post to read and useful to improve the knowledge as updated one, keep doing the good work.
      Spring Boot Training in Electronic City Bangalore
      Spring Boot Course in Electronic City Bangalore
      Spring Boot Training Institutes in Electronic City Bangalore

      ReplyDelete