Can we call a function using Callable statement? Can you explain it using JDBC example?

Like procedures, you can also create functions and store them in the database.

Syntax

The following is the syntax to create a function in the (MySQL) database:

Syntax

The following is the syntax to create a function in the (MySQL) database Syntax: p>

CREATE FUNCTION Function_Name(input_arguments) RETURNS output_parameter
BEGIN
   declare variables;
   statements . . . . . . . . . .
   return data_type;
   END

Example

Suppose there is a table named Emp in the database with the following content:

+--------+------------+----------------+
| Name   | DOB        | Location      |
+--------+------------+----------------+
| Amit   | 1970-01-08 | Hyderabad      |
| Sumith | 1970-01-08 | Vishakhapatnam |
| Sudha  | 1970-01-05 | Vijayawada     |
+--------+------------+----------------+

The creation function is given below example. Here, we create a function called getDob() which accepts the employee's name, retrieves and returns the value of the DOB column.

CREATE FUNCTION getDob(emp_name VARCHAR(50)) RETURNS DATE
BEGIN
declare dateOfBirth DATE;
select DOB into dateOfBirth from EMP where Name = emp_name;
return dateOfBirth;
END

Call a function using JDBC

You can call a function using a CallableStatement object just like a stored procedure to call a function using the JDBC program you need.

• Connect to the database.

• Create a PreparedStatement object and pass it to its constructor to call the function in string format.

• Sets the value as a placeholder.

• Execute the Callable statement.

The following is the query that calls the function from JDBC:

{? = call getDob(?)}

As you can observe, the query contains placeholders (?), just like prepared statements Same as callable statement.

In the above query, the first placeholder represents the return value of the function, and the second placeholder represents the input

You need to use registerOutParameter() of the CallableStatement interface The method registers a placeholder representing the return value as an output parameter. For this method, you need to pass an integer value representing the placeholder position and an integer variable representing the sql type (of the parameter)

cstmt.registerOutParameter(1, Types.DATE);

Use the setString() method to set the value to the input parameter. (Because the getDoc() function accepts values ​​of type VARCHAR).

Example

The following JDBC program executes function getDob and retrieves the results:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class CallingFunctionsUsingCallable2 {
   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/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");

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

      cstmt.registerOutParameter(1, Types.DATE);
      cstmt.setString(2, "Amit");
      cstmt.execute();

      System.out.print("Date of birth: "+cstmt.getDate(1));
   }
}

Output

Connection established......
Date of birth: 1970-01-08

The above is the detailed content of Can we call a function using Callable statement? Can you explain it using JDBC example?. For more information, please follow other related articles on the PHP Chinese website!