JDBC - Using OUT Parameter in Stored Procedure

Following is the example, which makes use of the CallableStatement along with the following getStudentId() MySQL stored procedure. This procedure returns next student id based on provided id.

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

DELIMITER $$

DROP PROCEDURE IF EXISTS `TUTORIALSPOINT`.`getStudentId` $$
CREATE PROCEDURE `TUTORIALSPOINT`.`getStudentId` 
   (INOUT STUDENT_ID INT)
BEGIN
   SET STUDENT_ID = STUDENT_ID + 1;
END $$

DELIMITER ;

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

Getting Student details Using Stored Procedure with IN Parameter Example

In this example, we've four static strings containing a dababase connection url, username, password a QUERY which calls the stored procedure with a placeholder. 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 INOUT parameter and using CallableStatement.registerOutParameter(), we've prepared the OUT variable to store the result of called stored procedure.

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

import java.sql.*;

// STORED PROCEDURE example having only IN parameter
public class JDBCStoredProcedureExample {

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

   public static void main(String args[]) {

      Connection conn = null;
      CallableStatement cstmt = null;
      try{
         conn = DriverManager.getConnection(DB_URL,USER,PASS);
         System.out.println("Connection to db  established.");

         cstmt = conn.prepareCall(QUERY);
         cstmt.setInt(1, 1000);
		 cstmt.registerOutParameter(1, java.sql.Types.INTEGER);

         System.out.println("Executing stored procedure..." );
         cstmt.execute();
         
         // Display updated student id
         System.out.println(cstmt.getInt(1));
         cstmt.close();
         conn.close();
      }catch(SQLException sqle){
         sqle.printStackTrace();
      }        
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCStoredProcedureExample.java
C:\>

When you run JDBCStoredProcedureExample, it produces the following result −

C:\>java JDBCStoredProcedureExample
Connection to db  established.
Executing stored procedure...
StudentID: 1000
1001

C:\>