JDBC - CallableStatement

CallableStatement

This interface is used to execute SQL stored procedures.The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.

   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
   {call <procedure-name>[(<arg1>,<arg2>, ...)]} 

reference taken from:-oracle.com 

Demo example of JDBC CallableStatement (Providing configuration for MySQL DB)

JDBCCallableStatement.java

package com.gaurav.jdbcexamples;

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

public class JDBCCallableStatement {

    /** Configuraing a connection for MySQL databse */
    // MySQL Driver filename
    private static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static String USERNAME = "root";
    private static String PASSWORD = "root";
    // Database connecting URL for MySQL.
    private static String DATABASE_URL = "jdbc:mysql://localhost:3306/test";
                                                                               
    public static void main(String args[]) {
        Connection con = null;
        CallableStatement cstmt = null;
        try {
            Class.forName(JDBC_DRIVER);
            con = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
            cstmt = con.prepareCall("{call getStudentName(?,?)}");
            // Setting the IN parameter
            cstmt.setInt(1, 1);
            cstmt.registerOutParameter(2, Types.VARCHAR);
            System.out
                    .println("Executing stored procedure named getStudentName() :- ");
            cstmt.execute();
            String studentName = cstmt.getString(2);
            // As second parameter is the OUT parameter so we have to register it
            System.out.println("Student name is : - " + studentName);

        } catch (SQLException sqe) {
            sqe.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (cstmt != null)
                    cstmt.close();
                if (con != null)
                    con.close();
            } catch (Exception e) {
                e.getMessage();
            }
        }
    }
}

getStudentName procedure source code:-(Syntax supported in MySQL)

DELIMITER $$

DROP PROCEDURE IF EXISTS test.getStudentName $$
CREATE PROCEDURE test.getStudentName
   (IN STUD_ID INT, OUT STUDENTNAME VARCHAR(100))
BEGIN
   SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) INTO STUDENTNAME
   FROM STUDENT
   WHERE STUDENT_ID = STUD_ID;
END $$

DELIMITER ;

Result:-

Executing stored procedure named getStudentName()
Student name is : - Kumar Gaurav