A stored procedure is a group of transact SQL statements. If you have a situation where you write the same query over and over again, then you can save that specific query as a stored procedure and call it just by calling its name. Stored procedures are a block of SQL statements that are stored as basic objects within your database.
Let's take our Employee table that has columns as EmpId
, Name
, and Age
. Let's say that we need the name and age of an employee, we will write the query as Select Name, Age from employee
. So every time we need the name and age of the employee, we will need to write this query. Instead, we can add this query to the stored procedure and call that stored procedure rather than writing this query again and again.
The advantages and disadvantages of using the stored procedure are as follows:
An instance of the SimpleJdbcCall
class is that of a multithreaded and reusable object, representing a call to a stored procedure. It provides the metadata processing to simplify the code required for accessing the basic stored procedure. While executing a call, you only have to provide the name of the stored procedure. The names of the supplied parameters are matched with the in and out parameters, specified during the declaration of a stored procedure. Here, we will discuss the calling of a stored procedure and a stored function using the SimpleJdbcCall
class.
The SimpleJdbcCall
class takes the advantage of the metadata present in the database to look up the names of the IN
and OUT
parameters, and thereby there is no need to explicitly declare the parameters. However, you can still declare them if you have the parameters that don't have the automatic mapping of the class, such as the array parameters.
In MYSQL
, we declare a stored procedure named getEmployee
, which contains an IN
parameter ID and two OUT
parameter IDs, named Emp_Name
and Emp_Age
. The query lies between BEGIN
and END
:
IN MYSQL DROP PROCEDURE IF EXISTS getEmployee CREATE PROCEDURE getEmployee ( IN id INTEGER, OUT Emp_Name VARCHAR(20), OUT Emp_Age INTEGER ) BEGIN SELECT Name, Age INTO Emp_Name, Emp_Age FROM employee where EmpId = id; END;
In the preceding code snippet, three parameters were specified. First was the IN
parameter id
, containing the ID of the employee. The remaining parameters were the OUT
parameters, which were used for returning the data retrieved from table.
In Apache Derby
, we declare a stored procedure named getEmployee
as shown here:
IN Apache Derby CREATE PROCEDURE getEmployee(IN id INTEGER, OUT name varchar(30)) LANGUAGE JAVA EXTERNAL NAME 'org.packt.Spring.chapter5.JDBC.dao.EmployeeDaoImp.getEmployee' PARAMETER STYLE JAVA;
The CREATE PROCEDURE
statement, as shown in aforementioned code snippet, allows us to create the Java stored procedures that can be called by using the CALL PROCEDURE
statement. The getEmployee
is a procedure name that is created in the database. The LANGUAGE JAVA
makes the database manager call the procedure as a public static method in a Java class. The EXTERNAL NAME 'package.class_name.method_name'
makes the method_name
method to be called when the procedure is executed. Here, the EXTERNAL NAME 'org.packt.Spring.chapter5.JDBC.dao.EmployeeDaoImp.getEmployee'
makes the getEmployee
method get called during the execution of the procedure. The Java method created org.packt.Spring.chapter5.JDBC.dao.EmployeeDaoImp.getEmployee
is specified as the EXTERNAL NAME
.
Now, let's discuss the implementation of SimpleJdbcCall
for calling the getEmployee
stored procedure. The following code snippet shows us how to read the getEmployee
stored procedure.
The following code snippet gives the EmployeeDaoImpl.java
class:
package org.packt.Spring.chapter5.JDBC.dao; import java.util.Map; import javax.sql.DataSource; import org.packt.Spring.chapter5.JDBC.model.Employee; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcCall; import org.springframework.stereotype.Repository; @Repository public class EmployeeDaoImpl implements EmployeeDao { @Autowired private DataSource dataSource; @Autowired private JdbcTemplate jdbcTemplate; private SimpleJdbcCall jdbcCall; public void setJdbcTemplateObject(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Autowired public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; this.jdbcCall = new SimpleJdbcCall(this.dataSource) .withProcedureName("getEmployee"); } @Override public Employee getEmployee(Integer id) { SqlParameterSource in = new MapSqlParameterSource().addValue("id", id); Map<String, Object> simpleJdbcCallResult = jdbcCall.execute(in); Employee employee = new Employee(id, (String) simpleJdbcCallResult.get("name")); return employee; } }
In the preceding code snippet, the instance of the SqlParameterSource
interface was created, which contained the parameters that must match the name of the parameter declared in the stored procedure. The execute()
method accepts the IN
parameter as an argument and returns a map containing the OUT
parameters, keyed by the name, as specified in the stored procedure. Here the OUT
parameter is name
. The retrieved value is set to the employee instance of employee.