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))
        SELECT employeeName INTO emp_name FROM employee WHERE employeeId = emp_id;

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

    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 -> {
                EmployeeName result = new EmployeeName();
                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.

    public class EmployeeController {
        private final EmployeeRepository employeeRepository;
        public EmployeeController(EmployeeRepository employeeRepository) {
            this.employeeRepository = employeeRepository;
        public String getEmployeeName(@PathVariable int empId) {
            return employeeRepository.getEmployeeName(empId);

Facing issues? Have Questions? Post them here! I am happy to answer!

Author Info:

Rakesh (He/Him) has over 14+ years of experience in Web and Application development. He is the author of insightful How-To articles for Code2care.

Follow him on: X

You can also reach out to him via e-mail:

Copyright © Code2care 2024 | Privacy Policy | About Us | Contact Us | Sitemap