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!
- Java equals method - Tutorial
- Unbound classpath container: JRE System Library [JavaSE-1.7]
- Spring Boot: @RequestBody not applicable to method
- Java 8: Steam map with Code Examples
- Java Program: Random Number Generator
- Java java.time.Clock class code examples [Java Date Time API]
- Fix: type argument is not within bounds of type-variable T
- [Fix] java.net.MalformedURLException: unknown protocol
- Java 7 addSuppression() and getSuppression() Exception Handling
- Convert Java Array to ArrayList Code Example
- How to Word-Warp Console logs in IntelliJ
- Ways Compare Dates in Java Programming with Examples
- Remove Trailing zeros BigDecimal Java
- CRUD operations in Spring Boot + JDBC
- [Java Threads] Should we extend Thread Class or implement Runnable interface
- Json Serialization and Deserialization using Java Jackson
- Create simple struts2 project using maven commands
- How to install Java OpenJDK 11 on Alpine Linux
- Unsupported major.minor version 52.0 in java
- Error: Can not find the tag library descriptor for
- Java: Convert String to Binary
- How to run Java Unit Test cases with Apache Maven?
- Java: Testing Private Methods in JUnit using reflection API Example
- Java JDBC Connection with MySQL Driver in VS Code + Troubleshooting
- Java Join Strings with Comma Separator
- [Solution] POI: Cannot get a NUMERIC value from a STRING cell - Java
- How To Change Mac Terminal Text Color - MacOS
- Get Current Local Date and Time using Java 8 DateTime API - Java
- Connect to 3270 host IBM Mainframe using Mac Terminal (c3270) - MacOS
- SharePoint List redirect user after submitting form NewForm.aspx - SharePoint
- How to change Ping TTL value on macOS - MacOS
- Mac Shortcut for Fullscreen mode for App Window - MacOS
- Get the total size and number of objects of a AWS S3 bucket and folders - AWS