Steps of working with Stored Procedures using JDBCTemplate Spring Boot

Spring Boot makes working with stored procedures using JdbcTemplate very easy.

Below is a Step-By-Step Guide:

• Step 1:

  Let's create our simple stored procedure.

  CREATE PROCEDURE get_employee_name (IN emp_id INT, OUT emp_name VARCHAR(50))
  
  BEGIN
      SELECT employeeName INTO emp_name FROM employee WHERE employeeId = emp_id;
  END
  

  Our stored procedure named get_employee_name takes an employee ID as input and returns the corresponding employee name as output.

• Step 2:

  Let's create our POJO class to map the stored procedure parameters and outputs. In our example, we need a class with two fields: empId and empName.

  public class EmployeeName {
  
      private int empId;
      private String empName;
  
      // getters and setters
  }
  

• Step 3:

  Create a CallableStatementCreator to execute our stored procedure. This class is responsible for creating the CallableStatement object that will call the stored procedure.

  public class EmployeeNameCallableStatementCreator implements CallableStatementCreator {
      private final int empId;
  
      public EmployeeNameCallableStatementCreator(int empId) {
          this.empId = empId;
      }
  
      @Override
      public CallableStatement createCallableStatement(Connection connection) throws SQLException {
          CallableStatement cs = connection.prepareCall("{call get_employee_name(?, ?)}");
          cs.setInt(1, empId);
          cs.registerOutParameter(2, Types.VARCHAR);
          return cs;
      }
  }
  

• Step 4:

  Crete our @Repository class.

  @Repository
  public class EmployeeRepository {
      private final JdbcTemplate jdbcTemplate;
  
      public EmployeeRepository(JdbcTemplate jdbcTemplate) {
          this.jdbcTemplate = jdbcTemplate;
      }
  
      public String getEmployeeName(int empId) {
          EmployeeNameCallableStatementCreator callableStatementCreator = new EmployeeNameCallableStatementCreator(empId);
          EmployeeName employeeName = jdbcTemplate.execute(callableStatementCreator, (CallableStatementCallback<EmployeeName>) cs -> {
              cs.execute();
              EmployeeName result = new EmployeeName();
              result.setEmpId(empId);
              result.setEmpName(cs.getString(2));
              return result;
          });
          return employeeName.getEmpName();
      }
  }
  

• Step 5:

  Finally, we can call the getEmployeeName() method from your service or controller class to get the employee name for a given employee ID.

  @RestController
  @RequestMapping("/employee")
  public class EmployeeController {
      private final EmployeeRepository employeeRepository;
  
      public EmployeeController(EmployeeRepository employeeRepository) {
          this.employeeRepository = employeeRepository;
      }
  
      @GetMapping("/{empId}/name")
      public String getEmployeeName(@PathVariable int empId) {
          return employeeRepository.getEmployeeName(empId);
      }
  }