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.

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;
   }
}