JDBC - CallableStatement Object Examples

---

---

Following is the example, which makes use of the CallableStatement along with the following getEmpName() MySQL stored procedure. This procedure returns one value, first name of employee based on provided id.

Make sure you have created this stored procedure in your EMP Database. You can use MySQL Query Browser to get it done.

DELIMITER $$

DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName` 
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END $$

DELIMITER ;

This sample code has been written based on the environment and database setup done in the previous chapters.

Getting Employee Name Using Stored Procedure Example

In this example, we've four static strings containing a dababase connection url, username, password a QUERY which calls the stored procedure with placeholders. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a CallableStatement object using connection.prepareCall() method. Using CallableStatement.setInt(), we've set the IN parameter and using CallableStatement.registerOutParameter(), we've prepared an OUT variable to store the result of called stored procedure.

Now using CallableStatement.execute(), we've run the query. Lastly using statement.getString() method, we've retrieved the name of the employee and result is printed.

Copy and paste the following example in JDBCCallableStatementExample.java, compile and run as follows −

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

public class JDBCCallableStatementExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "{call getEmpName (?, ?)}";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         CallableStatement stmt = conn.prepareCall(QUERY);
      ) {		      
         // Bind values into the parameters.
         stmt.setInt(1, 102);  // This would set ID
         // Because second parameter is OUT so register it
         stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
         //Use execute method to run stored procedure.
         System.out.println("Executing stored procedure..." );
         stmt.execute();
         //Retrieve employee name with getXXX method
         String empName = stmt.getString(2);
         System.out.println("Emp Name with ID: 102 is " + empName);
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCCallableStatementExample.java
C:\>

When you run JDBCCallableStatementExample, it produces the following result −

C:\>java JDBCCallableStatementExample
Executing stored procedure...
Emp Name with ID: 102 is Zaid
C:\>

Getting Student First Name and Last Name Using Stored Procedure Example

In this example, we're making use of the CallableStatement along with the following displayName() MySQL stored procedure. This procedure returns two values, first name and last name of the student based on provided id.

Make sure you have created this stored procedure in your EMP Database. You can use MySQL Query Browser to get it done.

DELIMITER //
 CREATE PROCEDURE displayName( IN StID int, OUT FName VARCHAR(255), OUT LName VARCHAR(255) )
     BEGIN
       SELECT FirstName, LastName 
       INTO FName,  LName
       FROM students 
       WHERE StudentID = StID;
END //

In this example, we've four static strings containing a dababase connection url, username, password a QUERY which calls the stored procedure with placeholders. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a CallableStatement object using connection.prepareCall() method. Using CallableStatement.setInt(), we've set the IN parameter and using CallableStatement.registerOutParameter(), we've prepared two OUT variables to store the results of called stored procedure.

Now using CallableStatement.execute(), we've run the query. Lastly using statement.getString() method, we've retrieved the first name and last name of the student and result is printed.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

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

public class JDBCCallableStatementExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "{call displayName (?,?,?)}";

   public static void main(String[] args) {
      // Open a connection
      try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         CallableStatement cstmt = conn.prepareCall(QUERY);
      ) {		      
         // Bind values into the parameters.
         cstmt.setInt(1, 1000);  // This would set stID
         // Because second parameter is OUT so register it
         cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
         cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
         //Use execute method to run stored procedure.
         System.out.println("Executing stored procedure..." );
         cstmt.execute();
         
        //Display First Name/ Last Name with getXXX method
          System.out.println(cstmt.getString(2));
          System.out.println(cstmt.getString(3));
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCCallableStatementExample.java
C:\>

When you run JDBCCallableStatementExample, it produces the following result −

C:\>java JDBCCallableStatementExample
Executing stored procedure...
Bonny
Agarwal  
C:\>