JDBC

JDBC : Function Execution Example

Leave a comment

A SQL stored function is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.

SQL stored function called a user function or user-defined function.
See Also :

Types of parameters of functions

  • IN: A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  • OUT: A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.\
  • INOUT: A parameter that provides both input and output values. You *bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Example :

In this example will focus on how to execute user defined function by JDBC instead of writing of user defined function.
Table and Function implementationn : Here is small function getAverageStudentAge based on student name.


Create Table
CREATE TABLE `facingissuesonitdb`.`student` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(25) NULL,
'age' INT,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC));

Insert Records  
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('1', 'Saurabh Gupta','25');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('2', 'Rajesh Gupta','30');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('3', 'Ramesh Gupta','35');

Create User defined function

USE `facingissuesonitdb`;
DROP function IF EXISTS `getAverageStudentAge`;

DELIMITER $
USE `facingissuesonitdb`$
CREATE FUNCTION getAverageStudentAge (name_prefix VARCHAR(25))
  RETURNS NUMERIC
   DETERMINISTIC
    BEGIN
     DECLARE avg_age NUMERIC;
        select avg(s.age) into avg_age from student as s where s.name like '%name_prefix%';
     RETURN avg_age;
    END$

DELIMITER ;

Java code to execute user defined function by JDBC.

package procedure_functions;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import connection.MyConnection;

public class FunctionTest {

public static void main(String[] args) {
try {
   Connection conn = MyConnection.getConnectionMain("MySql");

   CallableStatement stmt = conn.prepareCall("{?= call getAverageStudentAge(?)}");
   stmt.setString("Saurabh Gupta");
   stmt.registerOutParameter(1, Types.INTEGER);
   stmt.execute();

   System.out.println(stmt.getInt(1));
} catch (SQLException ex) {
	ex.printStackTrace();
}

}

}




Output:



MySql Connection created successfully !!
MySql Connection Creation end !!
25.



More on JDBC


Follow below links to know more on JDBC and solving JDBC issues :