JDBC - CallableStatement Object Example


Advertisements

Following is the example, which makes use of the CallableStatement along with the following getEmpName() MySQL stored procedure −

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.

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 JDBCExample {
   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();
      } 
   }
}

Now let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

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

Useful Video Courses


Video

Java Date and Time Online Training

16 Lectures 2 hours

Malhar Lathkar

Video

Java Servlet Online Training

19 Lectures 5 hours

Malhar Lathkar

Video

JavaScript Online Training

25 Lectures 2.5 hours

Anadi Sharma

Video

Java Online Training

Most Popular

126 Lectures 7 hours

Tushar Kale

Video

Java Essential Training

119 Lectures 17.5 hours

Monica Mittal

Video

Java Essentials Online Training

76 Lectures 7 hours

Arnab Chakraborty

Advertisements