Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
6K+ Views
