JDBC - CallableStatement Multiple In Out Parameters (Mysql)

🚀 Master Java database programming, JDBC, and MySQL stored procedures!

🔔 Subscribe to Ram N Java on YouTube

Executing MySQL Stored Procedures with Multiple Parameters

In database-driven enterprise applications, stored procedures encapsulate database business logic. Often, a stored procedure requires multiple inputs to filter data and returns multiple output parameters back to the client application.

Java's CallableStatement interface allows you to execute these stored procedures cleanly by setting multiple IN parameters and registering multiple OUT parameters by their index positions.

The MySQL Stored Procedure (get_city_info)

Here is the stored procedure created in the MySQL world database. It accepts two input parameters (City ID and Minimum Population) and populates four output parameters:

DELIMITER $$

CREATE PROCEDURE `get_city_info`(
    IN  city_id_input         INT,
    IN  city_population_input INT,
    OUT city_name_out         VARCHAR(35),
    OUT country_code_out      CHAR(3),
    OUT district_out          VARCHAR(20),
    OUT population_out        INT
)
BEGIN
    SELECT Name, CountryCode, District, Population
    INTO city_name_out, country_code_out, district_out, population_out
    FROM city
    WHERE ID = city_id_input AND Population > city_population_input;
END$$

DELIMITER ;

Complete Java Source Code Example

The following Java program connects to MySQL, prepares the procedure call with 6 parameter placeholders, binds the inputs, registers all four output parameter types, and displays the results:

package com.ram.jdbc;

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

public class JDBCCallableStatementDemo {

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/world";
    static final String USER = "root";
    static final String PASS = "root";

    public static void main(String[] args) {
        JDBCCallableStatementDemo demo = new JDBCCallableStatementDemo();
        Scanner scanner = new Scanner(System.in);

        System.out.print("Enter City ID (e.g., 1 or 0 to exit): ");
        int cityId = scanner.nextInt();

        System.out.print("Enter Population threshold (e.g., 1000): ");
        int population = scanner.nextInt();

        if (cityId > 0) {
            demo.getCityInfo(cityId, population);
        }
        scanner.close();
    }

    public void getCityInfo(int cityId, int population) {
        Connection conn = null;
        CallableStatement cstmt = null;

        try {
            // Step 1: Register MySQL Driver
            Class.forName(JDBC_DRIVER);

            // Step 2: Establish connection
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // Step 3: Prepare the procedure call (2 IN, 4 OUT parameters)
            String sql = "{call get_city_info(?, ?, ?, ?, ?, ?)}";
            cstmt = conn.prepareCall(sql);

            // Step 4: Set IN parameters
            cstmt.setInt(1, cityId);
            cstmt.setInt(2, population);

            // Step 5: Register OUT parameter types
            cstmt.registerOutParameter(3, Types.VARCHAR);
            cstmt.registerOutParameter(4, Types.VARCHAR);
            cstmt.registerOutParameter(5, Types.VARCHAR);
            cstmt.registerOutParameter(6, Types.INTEGER);

            // Step 6: Execute the procedure
            cstmt.execute();

            // Step 7: Retrieve OUT parameter values
            String cityName = cstmt.getString(3);
            String countryCode = cstmt.getString(4);
            String district = cstmt.getString(5);
            int cityPopulation = cstmt.getInt(6);

            System.out.println("\n--- City Information ---");
            System.out.println("City Name    : " + cityName);
            System.out.println("Country Code : " + countryCode);
            System.out.println("District     : " + district);
            System.out.println("Population   : " + cityPopulation);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // Step 8: Clean up resources
            try {
                if (cstmt != null) cstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Key Steps to Follow

• Prepare the Call: Use conn.prepareCall("{call PROCEDURE_NAME(?, ?, ...)}") with question marks matching the total number of arguments (both IN and OUT).
• Bind Inputs First: Use setInt(1, ...), setString(2, ...) to supply your input parameters.
• Register Output Types: Call registerOutParameter(index, Types.TYPE) for each output position before invoking execute().
• Fetch Output Values: Read the returned values with getter methods matching the registered types (e.g., getString(3), getInt(6)).

Related Tutorials You Might Enjoy

• JDBC CallableStatement Tutorial: In-Out Parameters in Oracle DB Stored Procedures
• Java Program to Connect to MySQL Database and Retrieve Information | JDBC Demo
• JDBC Database Connection: How to Connect Java to a Database

---

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