

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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)
- Related Questions & Answers
- How to create a database in MySQL using a JDBC API?
- How to create a table in a database using JDBC API?
- How to create a Stored procedure in a database using JDBC API?
- How to drop a database using JDBC API?
- How to create a Stored procedure in Oracle database using JDBC API?
- How to call an existing function in a database using JDBC API?
- How to drop a table from a database using JDBC API?
- How to drop a table from Oracle database using JDBC API?
- How to insert a record into a table in a database using JDBC API?
- How to call an existing stored procedure in a database using JDBC API?
- How to select or, shift to another database in MySQL using a JDBC API?
- How to retrieve a record from an existing table in a database using JDBC API?
- How to remove a record from an existing table in a database using JDBC API?
- How to delete a column from an existing table in a database using JDBC API?
- How to drop constraint on a column of a table in a database using JDBC API?