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)