How to Call Stored Functions and Stored Procedures using JDBC?
Last Updated :
31 Oct, 2022
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
|
Stored Procedure
|
Stored Function
|
| A stored procedure can return zero or more values. |
A stored function returns a single value. |
| A stored procedure accepts IN, OUT, and INOUT parameters |
A stored function accepts IN parameters only. |
| A stored procedure can call a stored function within its body. |
A stored function cannot call a stored procedure. |
| A stored procedure can use all DML statements within its body. |
A stored function can only use the SELECT statement. |
| A stored procedure can use try-catch blocks for exception handling. |
A stored function cannot use try-catch blocks. |
| A stored procedure cannot be embedded into SELECT/WHERE/HAVING etc. clauses. |
A stored function can be embedded into SELECT/WHERE/HAVING etc. clauses. |
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
- IN parameter: It is the default parameter type used to provide input values to a procedure or a function.
- 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.
- 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
Java
import java.sql.*;
public class UpdateEmpSalary {
public static void main(String[] args) {
Connection con=null;
try
{
CallableStatement cs=con.prepareCall("{call updateEmpSalary(?,?)}");
cs.setInt(1,101);
cs.setDouble(2,0.1);
cs.executeUpdate();
}
catch (Exception e)
{
System.out.println(e);
}
finally {
try
{
con.close();
}
catch (SQLException e)
{
System.out.println(e);
}
}
}
}
|
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
Java
import java.sql.*;
public class CountEmp {
public static void main(String[] args) {
Connection con=null;
try
{
CallableStatement cs=con.prepareCall("{call countEmp(?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println("Number of employees : "+cs.getInt(1));
}
catch (Exception e)
{
System.out.println(e);
}
finally {
try
{
con.close();
}
catch (SQLException e)
{
System.out.println(e);
}
}
}
}
|
Output:
Example 3:
incCounter.sql
DROP PROCEDURE IF EXISTS incCounter;
DELIMITER $$
CREATE PROCEDURE incCounter(INOUT counter INT)
BEGIN
SET counter=counter+1;
END$$
DELIMITER ;
Java
import java.sql.*;
public class IncCounter {
public static void main(String[] args) {
Connection con=null;
try
{
CallableStatement cs=con.prepareCall("{call incCounter(?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setInt(1,3);
cs.execute();
System.out.println("Counter : "+cs.getInt(1));
}
catch (Exception e)
{
System.out.println(e);
}
finally {
try
{
con.close();
}
catch (SQLException e)
{
System.out.println(e);
}
}
}
}
|
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
Java
import java.sql.*;
public class IsPrime {
public static void main(String[] args) {
Connection con=null;
try
{
CallableStatement cs=con.prepareCall("{?=call isPrime(?)}");
cs.registerOutParameter(1, Types.BOOLEAN);
cs.setInt(2,5);
cs.execute();
System.out.println("Result : "+cs.getInt(1));
}
catch (Exception e)
{
System.out.println(e);
}
finally {
try
{
con.close();
}
catch (SQLException e)
{
System.out.println(e);
}
}
}
}
|
Output:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...