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 create a function in a database using JDBC API?
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
To create a function in a database using JDBC API you need to:
Register the driver: class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.
Establish a connection: Connect ot the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.
Create Statement: Create a Statement object using the createStatement() method of the Connection interface.
Execute the Query: Execute the query using the execute() method of the Statement interface
Assume we have a table named EmployeeDetails with the following description:
+----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | Name | varchar(255) | YES | | NULL | | | DOB | date | YES | | NULL | | | Location | varchar(255) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+
Following JDBC program establishes connection with MySQL database, and creates a function named getDob().
This functions accepts a parameter of VARCHAR type representing the name of the employee and returns a date object representing the date of birth of the specified employee.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreatingFunctionsExample {
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......");
//Creating the Statement
Statement stmt = con.createStatement();
//Query to create a function
String query = "CREATE FUNCTION getDob(emp_name VARCHAR(50)) RETURNS DATE " +
"BEGIN " +
" declare dateOfBirth DATE;" +
" select DOB into dateOfBirth from employeedetails where Name = emp_name;" +
" return dateOfBirth;" +
"END";
//Executing the query
stmt.execute(query);
System.out.println("Function Created......");
}
}Output
Connection established...... Function Created......
The SHOW CREATE FUNCTION function_name command displays the source code of the specified function, if it not exist, you will get an error.
Verify whether the function named getDob created in MySQL database, using this command as:
mysql> SHOW CREATE FUNCTION getDob; +----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | +----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | getDob | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `getDob`(emp_name VARCHAR(50)) RETURNS date BEGIN declare dateOfBirth DATE; select DOB into dateOfBirth from employeedetails where Name = emp_name; return dateOfBirth; END | utf8 | utf8_general_ci | utf8_general_ci | +----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec)
869 Views
