How to call an existing function in a database using JDBC API?

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

• Connect to the database.

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

• Set values to the place holders.

• Execute the Callable statement.

Following is the query to call a function from JDBC:

{? = call getDob(?)}

As you observe the query contains place holders (?) just like prepared and callable statements.

In the above query, the first place holder represents the return value of the function and the second placeholder represents the input parameter.

You need to register the place holder which represents return value, as an output parameter using the registerOutParameter() method(of the CallableStatement interface). To this method, you need to pass an integer value representing the position of the place holder and, an integer variable representing the SQL type (of the parameter)

Example

Assume we have a table named EmployeeDetails with the following content:

+--------+------------+----------------+
| Name   | DOB        | Location       |
+--------+------------+----------------+
| Amit   | 1989-09-26 | Hyderabad      |
| Sumith | 1989-09-01 | Vishakhapatnam |
| Sudha  | 1980-09-01 | Vijayawada     |
+--------+------------+----------------+

We have created a function named getDob() as shown below:

mysql> DELIMITER // ;
mysql> 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//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

This function accepts the name of the employee, retrieves and returns date of birth of the specified employee.

Following JDBC program establishes a connection with MySQL database, and calls the function named getDob(), by passing employee name as a parameter to it and, retrieves the date of birth value from the return value of the function.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class CallingFunctionsExample {
   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/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Preparing a CallableStatement to call a function
      CallableStatement cstmt = con.prepareCall("{? = call getDob(?)}");
      //Registering the out parameter of the function (return type)
      cstmt.registerOutParameter(1, Types.DATE);
      //Setting the input parameters of the function
      cstmt.setString(2, "Amit");
      //Executing the statement
      cstmt.execute();
      System.out.print("Date of birth: "+cstmt.getDate(1));
   }
}

Output

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