How to Call Stored Functions and Stored Procedures using JDBC?

JDBC or Java Database Connectivity is a Java API to connect and execute the query with the database. It is a specification from Sun microsystems that provides a standard abstraction(API or Protocol) for java applications to communicate with various databases. It provides the language with java database connectivity standards. It is used to write programs required to access databases.

Stored Procedure

Stored procedure is a set of SQL statements stored together as a single block of code in the database which can be reused multiple times without having to write the queries again. A stored procedure may provide multiple output values and accepts input as well as output parameters.

Stored Function

Stored function is a set of SQL statements that perform a particular task. It is similar to a stored procedure except that it returns a single value and accepts only input parameters. 

Advantages of using Stored Procedures and Stored Functions

• Stored procedures and functions are stored in the database and can be called as and when required.
• Business and database logic can be stored in the database itself which can be used by multiple applications.
• Reduces traffic. No need to send the set of queries over the network. Rather procedures and functions are stored in the database and a call to these stored procedures and functions can be made to fetch the results.
• This leads to fast execution as they are compiled once and used multiple times.

Stored Procedures vs Stored Functions

Syntax of stored procedures and functions

Stored Procedure:

DELIMITER $$
CREATE PROCEDURE <procedure_name> ([IN|OUT|INOUT] <parameter_name> DATATYPE,...)
BEGIN
   --statements--
END$$
DELIMITER ;

Stored Function:

DELIMITER $$
CREATE FUNCTION <function_name>(<parameter_name> DATATYPE,...) RETURNS RETURN_TYPE
[NOT] DETERMINISTIC
BEGIN
   --statements--
END$$
DELIMITER ;

Types of parameters

1. IN parameter: It is the default parameter type used to provide input values to a procedure or a function.
2. OUT parameter: It is used to fetch output values from a procedure. No initial value is provided to an OUT parameter. Its value is set by the procedure and returned.
3. INOUT parameter: It is similar to the OUT parameter except that it is provided with an initial value that is modified by the procedure and returned.

Calling stored procedures and functions using JDBC

Callable Statements:

Callable Statements in JDBC are used to call stored procedures and functions from the database. CallableStatement is an interface present in java.sql package. Following method of java.sql.Connection is used to get the object of CallableStatement.

CallableStatement prepareCall(String sql) throws SQLException

Given below are some useful methods of CallableStatement:

• boolean execute() throws SQLException
• int executeUpdate() throws SQLException
• int getInt(int parameterIndex) throws SQLException
• void registerOutParameter(int parameterIndex, int sqlType) throws SQLException
• void setInt(String parameterName, int x) throws SQLException

Example 1:

createEmp.sql

drop table if exists emp;
create table emp(emp_id int primary key,emp_name varchar(20),salary decimal(10,2));
insert into emp values(101,"Ansh",20000);
insert into emp values(102,"Danish",16000);
insert into emp values(103,"Priya",30000);

 

updateEmpSalary.sql

drop procedure if exists updateEmpSalary;
DELIMITER $$
CREATE PROCEDURE updateEmpSalary(IN id INT,IN incRate DECIMAL(4,2))
BEGIN
UPDATE emp 
   SET salary = salary*(1+incRate)
   WHERE emp_id=id;
END$$ 
DELIMITER ;

UpdateEmpSalary.java

Output:

 

Example 2:

countEmp.sql

DROP PROCEDURE IF EXISTS countEmp;
DELIMITER $$
CREATE PROCEDURE countEmp (OUT count INT)
BEGIN
DECLARE id INT;
   DECLARE name VARCHAR(20);
   DECLARE salary DECIMAL(10,4);
   
DECLARE finished INT DEFAULT 0;
   DECLARE c CURSOR FOR SELECT * FROM emp;
   DECLARE CONTINUE HANDLER FOR NOT FOUND
   BEGIN
 SET finished=1;
END;
SET count=0;
   OPEN c;
   l:LOOP
 FETCH c INTO id,name,salary;
       IF finished=1 THEN
  LEAVE l;
 END IF;
       SET count=count+1;
END LOOP;
   CLOSE c;
END$$
DELIMITER ;
CALL countEmp(@count);
select @count

CountEmp.java

Output:

 

Example 3:

incCounter.sql

DROP PROCEDURE IF EXISTS incCounter;
DELIMITER $$
CREATE PROCEDURE incCounter(INOUT counter INT)
BEGIN
SET counter=counter+1;
END$$
DELIMITER ;

Output:

 

Example 4:

isPrime.sql

DROP FUNCTION IF EXISTS isPrime;
DELIMITER $$
CREATE FUNCTION isPrime(x INT) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
DECLARE n INT DEFAULT 2;
   loop1:WHILE n<=SQRT(x) DO
 IF x%n=0 THEN
  RETURN FALSE;
 END IF;
       SET n=n+1;
END WHILE;
   RETURN TRUE;
END$$
DELIMITER ;

IsPrime.java

Output: