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
Can we call functions using Callable Statements? Explain with an example in JDBC?
Like procedures, you can also create function in a database and store them.
Syntax
Following is the syntax of creating a function in a(MySQL) database:
CREATE FUNCTION Function_Name(input_arguments) RETURNS output_parameter BEGIN declare variables; statements . . . . . . . . . . return data_type; END
Example
Suppose we have 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 | +--------+------------+----------------+
Below Given is an example of creating a function. Here, we are creating a function named getDob() which accepts the name of the employee, retrieves and returns the value of DOB column(s).
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
Calling a function using JDBC
You can call a function using CallableStatement object just like stored procedures, to call a function using 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)
cstmt.registerOutParameter(1, Types.DATE);
Set the value to the input parameter using the setString() method. (since getDoc() function accepts a value of VARCHAR type).
Example
Following JDBC program executes the 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
2K+ Views
