JDBC - Using IN Parameter in Stored Procedure

Following is the example, which makes use of the CallableStatement along with the following getStudent() MySQL stored procedure. This procedure returns student details 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`.`getStudent` $$
CREATE PROCEDURE `TUTORIALSPOINT`.`getStudent` 
   (IN STUDENT_ID INT)
BEGIN
   SELECT STUDENTID, FIRSTNAME, LASTNAME, DEPT
   FROM STUDENTS 
   WHERE STUDENTID = STUDENT_ID;
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 IN parameter.

Now using CallableStatement.executeQuery(), we've run the query and stored the result in a resultset object. Lastly resultset is iterated and student details are printed.

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 getStudent(?)}";

   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);

         System.out.println("Executing stored procedure..." );
         ResultSet rs = cstmt.executeQuery();

         while( rs.next()){
            System.out.println("StudentID: " + rs.getInt("StudentID"));
            System.out.println("FirstName: " + rs.getString("FirstName"));
            System.out.println("LastName: " + rs.getString("LastName"));
            System.out.println("Dept: " + rs.getString("Dept"));
         }
         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
FirstName: Bonny
LastName: Agarwal
Dept: Mathematics

C:\>