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); } }
Facing issues? Have Questions? Post them here! I am happy to answer!
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: rakesh@code2care.org
- Get the current timestamp in Java
- Java Stream with Multiple Filters Example
- Java SE JDBC with Prepared Statement Parameterized Select Example
- Fix: UnsupportedClassVersionError: Unsupported major.minor version 63.0
- [Fix] Java Exception with Lambda - Cannot invoke because object is null
- 7 deadly java.lang.OutOfMemoryError in Java Programming
- How to Calculate the SHA Hash Value of a File in Java
- Java JDBC Connection with Database using SSL (https) URL
- How to Add/Subtract Days to the Current Date in Java
- Create Nested Directories using Java Code
- Spring Boot: JDBCTemplate BatchUpdate Update Query Example
- What is CA FE BA BE 00 00 00 3D in Java Class Bytecode
- Save Java Object as JSON file using Jackson Library
- Adding Custom ASCII Text Banner in Spring Boot Application
- [Fix] Java: Type argument cannot be of primitive type generics
- List of New Features in Java 11 (JEPs)
- Java: How to Add two Maps with example
- Java JDBC Transition Management using PreparedStatement Examples
- Understanding and Handling NullPointerException in Java: Tips and Tricks for Effective Debugging
- Steps of working with Stored Procedures using JDBCTemplate Spring Boot
- Java 8 java.util.Function and BiFunction Examples
- The Motivation Behind Generics in Java Programming
- Get Current Local Date and Time using Java 8 DateTime API
- Java: Convert Char to ASCII
- Deep Dive: Why avoid java.util.Date and Calendar Classes
- eclipse maven m2e : Cannot complete the install - Eclipse
- M365 service Europe outage - AADSTS90033 A transient error has occurred. Please try again. - Microsoft
- How to install Node using Brew on Mac - MacOS
- Closest Alternate to Notepad on Mac - MacOS
- Java Code to check if Twitter app is installed on Android device - Android
- List of PowerShell Cmdlet Commands for Mac - Powershell
- Compare two lists in Python and return matches - Python
- How to turn off autocomplete in input fields in HTML Form - Html