JDBC - CallableStatement with Input Parameter(Mysql)

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

🔔 Subscribe to Ram N Java on YouTube

Calling MySQL Stored Procedures with Input Parameters

In real-world database development, stored procedures frequently accept IN (input) parameters to filter records dynamically on the database server.

With JDBC's CallableStatement, passing input arguments to a MySQL procedure is as intuitive as using a PreparedStatement. You define placeholder markers (?), set values using standard setter methods like setString(), and execute the procedure to process the resulting rows.

The MySQL Stored Procedure (get_cities)

Below is the stored procedure created in the MySQL world database. It takes a country code as an input argument and returns all matching cities:

DELIMITER $$

CREATE PROCEDURE `get_cities`(
    IN city_country_code_input CHAR(3)
)
BEGIN
    SELECT * 
    FROM city 
    WHERE CountryCode = city_country_code_input;
END$$

DELIMITER ;

Complete Java Source Code Example

The following Java program connects to MySQL, uses Scanner to read country code inputs from the console, passes them into the procedure using CallableStatement, and iterates over the returned ResultSet:

package com.ram.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
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 Country Code (e.g., USA, IND, NLD) or 'exit': ");
        String countryCode = scanner.next();

        while (!"exit".equalsIgnoreCase(countryCode)) {
            demo.getCitiesInformation(countryCode);

            System.out.print("\nEnter Country Code or 'exit': ");
            countryCode = scanner.next();
        }
        scanner.close();
    }

    public void getCitiesInformation(String countryCode) {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

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

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

            // Step 3: Prepare the procedure call with placeholder
            String sql = "{call get_cities(?)}";
            cstmt = conn.prepareCall(sql);

            // Step 4: Bind the IN parameter
            cstmt.setString(1, countryCode);

            // Step 5: Execute the stored procedure
            rs = cstmt.executeQuery();

            System.out.println("ID | Name | CountryCode | District | Population");
            System.out.println("-------------------------------------------------");
            while (rs.next()) {
                int id = rs.getInt("ID");
                String name = rs.getString("Name");
                String cCode = rs.getString("CountryCode");
                String district = rs.getString("District");
                int population = rs.getInt("Population");

                System.out.println(id + " | " + name + " | " + cCode + " | " + district + " | " + population);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // Step 6: Close resources safely
            try {
                if (rs != null) rs.close();
                if (cstmt != null) cstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Key Steps to Remember

• Use conn.prepareCall("{call get_cities(?)}") with SQL escape braces to specify the procedure and input parameter.
• Set input arguments using cstmt.setString(1, value) before calling execution methods.
• Because the procedure executes a SELECT query, invoke cstmt.executeQuery() to receive the resulting ResultSet directly.
• Always close the ResultSet, CallableStatement, and Connection in the finally block to prevent database resource leaks.

Related Tutorials You Might Enjoy

• MySQL CallableStatement Example in JDBC: Stored Procedures
• JDBC CallableStatement with Multiple In-Out Parameters for MySQL Stored Procedure
• Java Program to Connect to MySQL Database and Retrieve Information | JDBC Demo

---

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

Click the below Image to Enlarge

JDBC - CallableStatement with Input Parameter(Mysql)

JDBC - CallableStatement with Input Parameter(Mysql)

Create Stored Procedure