CallableStatement example with IN OUT parameters

CallableStatement in JDBC is used to execute Stored Procedures.

A Stored Procedure can have 3 types of parameters
1. IN – is input parameter to the procedure
2. OUT – is output parameter returning from procedure
3. INOUT – acts as both input and output parameter.

Create a Stored Procedure in Database

CREATE PROCEDURE employee_dtls_proc
(IN in_id INT,
OUT out_full_name VARCHAR(255))
BEGIN
  
  SELECT CONCAT(first_name,last_name) INTO out_full_name FROM employee_details
  WHERE id = in_id;
  
END

CREATE PROCEDURE employee_dtls_proc

(IN in_id INT,

OUT out_full_name VARCHAR(255))

BEGIN

SELECT CONCAT(first_name,last_name) INTO out_full_name FROM employee_details

WHERE id = in_id;

END

Steps to Call a Stored Procedure using CallableStatement

1. Create a CallableStatement Object
Syntax to call the store procedure is
{call (IN-OUT params)}

String callStoreProc = "{call employee_dtls_proc(?,?)}";
CallableStatement callableStatement = connection.prepareCall(callStoreProc);

1 2	String callStoreProc = "{call employee_dtls_proc(?,?)}"; CallableStatement callableStatement = connection.prepareCall(callStoreProc);

2. Set IN param values

callableStatement.setInt(1, 10);

1	callableStatement.setInt(1, 10);

3. Register the OUT params

callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

1	callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

Based on the output param type we need to set the appropriate java.sql.Types

4. Execute the CallableStatement

callableStatement.executeUpdate();

1	callableStatement.executeUpdate();

5. get the output from the CallableStatement

String fullName = callableStatement.getString(2);

1	String fullName = callableStatement.getString(2);

Full Example : CallableStatement example with IN OUT

package com.kscodes.sampleproject.jdbc;

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

public class CallableExample {

	public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
	public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql";
	public static String CONNECTION_USER = "kscodes";
	public static String CONNECTION_PASSWORD = "kscodes";

	public static void main(String[] args) {

		String callStoreProc = "{call employee_dtls_proc(?,?)}";

		try (Connection connection = getConnection();
				CallableStatement callableStatement = connection
						.prepareCall(callStoreProc)) {

			callableStatement.setInt(1, 10);
			callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

			callableStatement.executeUpdate();

			String fullName = callableStatement.getString(2);

			System.out.println("Full name after executing Stored Procedure is ::"
							+ fullName);

		} catch (SQLException e) {
			System.out.println("An exception occured while updating data in database. Exception is :: "
							+ e);
		}

	}

	public static Connection getConnection() {

		try {
			Class.forName(DRIVER_CLASS_NAME);
		} catch (ClassNotFoundException e) {
			System.out.println("Error while registering JDBC driver");
			return null;
		}

		Connection connection = null;
		try {
			connection = DriverManager.getConnection(CONNECTION_URL,
					CONNECTION_USER, CONNECTION_PASSWORD);

		} catch (SQLException e) {
			System.out.println("Failed to create Connection");
			return null;
		}
		System.out.println("Connection created Successfully.");

		return connection;
	}

}

package com.kscodes.sampleproject.jdbc;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class CallableExample {

public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";

public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql";

public static String CONNECTION_USER = "kscodes";

public static String CONNECTION_PASSWORD = "kscodes";

public static void main(String[] args) {

String callStoreProc = "{call employee_dtls_proc(?,?)}";

try (Connection connection = getConnection();

CallableStatement callableStatement = connection

.prepareCall(callStoreProc)) {

callableStatement.setInt(1, 10);

callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

callableStatement.executeUpdate();

String fullName = callableStatement.getString(2);

System.out.println("Full name after executing Stored Procedure is ::"

+ fullName);

} catch (SQLException e) {

System.out.println("An exception occured while updating data in database. Exception is :: "

+ e);

}

public static Connection getConnection() {

try {

Class.forName(DRIVER_CLASS_NAME);

} catch (ClassNotFoundException e) {

System.out.println("Error while registering JDBC driver");

return null;

}

Connection connection = null;

try {

connection = DriverManager.getConnection(CONNECTION_URL,

CONNECTION_USER, CONNECTION_PASSWORD);

} catch (SQLException e) {

System.out.println("Failed to create Connection");

return null;

}

System.out.println("Connection created Successfully.");

return connection;

}

Output

Connection created Successfully.
Full name after executing Stored Procedure is ::SmithYo

1 2	Connection created Successfully. Full name after executing Stored Procedure is ::SmithYo