What is CallableStatement in JDBC?

#CallableStatement The interface provides methods for executing stored procedures. Because the JDBC API provides stored procedure SQL escape syntax, you can call stored procedures in a single standard way for all RDBMSs.

Creating a CallableStatement

You can create a CallableStatement (interface) using the prepareCall() method of the Connection interface. This method accepts a string variable representing the query to call the stored procedure and returns a CallableStatement object.

Callable statements can have input parameters, output parameters, or both. To pass input parameters to a procedure call, you use placeholders and set values ​​for these parameters using the setter methods (setInt(), setString(), setFloat()) provided by the CallableStatement interface.

Assuming you have a procedure named myProcedure in your database, you can prepare a callable statement as follows:

//Preparing a CallableStatement
CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");

Set values ​​for input parameters

You can Use setter methods to set values ​​for input parameters of procedure calls.

They accept two parameters, one of which is an integer value representing the placement index of the input parameter, and the other is an int or, String or, float, etc... representing the value you need to pass Enter parameters into the process.

Note: In addition to the index, you can also pass parameter names in string format.

cstmt.setString(1, "Raghav");
cstmt.setInt(2, 3000);
cstmt.setString(3, "Hyderabad");

Execute a Callable Statement

After you create a CallableStatement object, you can use one of the execute() methods to execute it.

cstmt.execute();

Example

Suppose there is a table named Employee in the MySQL database, which contains the following data:

+---------+--------+----------------+
| Name    | Salary | Location       |
+---------+--------+----------------+
| Amit    | 30000  | Hyderabad      |
| Kalyan  | 40000  | Vishakhapatnam |
| Renuka  | 50000  | Delhi          |
| Archana | 15000  | Mumbai         |
+---------+--------+----------------+

We create a table named myProcedure to insert values ​​into the table, as shown below:

Create procedure myProcedure (IN name VARCHAR(30), IN sal INT, IN loc VARCHAR(45))
   -> BEGIN
   -> INSERT INTO Employee(Name, Salary, Location) VALUES (name, sal, loc);
   -> END //

Here is a JDBC example that uses a callable statement to call the procedure created above to insert new records into the Employee table.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class CallableStatementExample {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());

      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/testdb";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Preparing a CallableStateement
      CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");

      cstmt.setString(1, "Raghav");
      cstmt.setInt(2, 3000);
      cstmt.setString(3, "Hyderabad");

      cstmt.setString(1, "Kalyan");
      cstmt.setInt(2, 4000);
      cstmt.setString(3, "Vishakhapatnam");

      cstmt.setString(1, "Rukmini");
      cstmt.setInt(2, 5000);
      cstmt.setString(3, "Delhi");

      cstmt.setString(1, "Archana");
      cstmt.setInt(2, 15000);
      cstmt.setString(3, "Mumbai");

      cstmt.execute();
      System.out.println("Rows inserted ....");
   }
}

Output

Connection established......
Rows inserted ....

If you use a select query to retrieve the contents of the Employee table, you can observe the newly added records as follows:

mysql> select * from employee;
+---------+--------+----------------+
| Name    | Salary | Location       |
+---------+--------+----------------+
| Amit    | 30000  | Hyderabad      |
| Kalyan  | 40000  | Vishakhapatnam |
| Renuka  | 50000  | Delhi          |
| Archana | 15000  | Mumbai         |
| Raghav  | 3000   | Hyderabad      |
| Raghav  | 3000   | Hyderabad      |
| Kalyan  | 4000   | Vishakhapatnam |
| Rukmini | 5000   | Delhi          |
| Archana | 15000  | Mumbai         |
+---------+--------+----------------+
9 rows in set (0.00 sec)

The above is the detailed content of What is CallableStatement in JDBC?. For more information, please follow other related articles on the PHP Chinese website!