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); } }
Have Questions? Post them here!
- Create a Zip file using Java Code programmatically
- Eclipse : A java Runtime Environment (JRE) or Java Development kit (JDK) must be available
- How to Sort a LinkedList in Java
- Loading class com.mysql.jdbc.Driver. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver
- How to declare and initialize Array in Java Programming
- [Fix] java: integer number too large compilation error
- Java JDBC Connection with MySQL Driver in VS Code + Troubleshooting
- Reading .xls and .xlsx Excel file using Apache POI Java Library
- IntelliJ: Error: Could not find or load main class, java.lang.ClassNotFoundException
- How to get Client IP address using Java Code Example
- Truncate table using Java JDBC Example
- Struts 2 : There is no Action mapped for namespace [/] and action name [form] associated with context path [/proj]
- How to get file path in Idea IntelliJ IDE
- Java Generics explained with simple definition and examples
- Java SE 8 Update 301 available with various bug fixes and security improvements
- Java: Collect Stream as ArrayList or LinkedList
- Java JDBC Connection with PostgreSQL Driver Example
- How to check if Java main thread is alive
- How to fix Java nio NoSuchFileException wile reading a file
- Java 8+ get Day of the Week Examples with LocalDateTime, DateTime, ZonalDateTime and Instant classes
- Ways to Convert Integer or int to Long in Java
- [Java] How to throws Exception using Functional Interface and Lambda code
- [Fix] Spring Boot: mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
- Java: The value of the local variable string is not used
- Java JDBC: Insert Java 8 LocalDate and Time using PreparedStatement
- How to Change Text Size for Android ActionBar - Android
- Git Revision Questions Before the Interview - Git
- How to install brew on macOS Ventura - MacOS
- How to create a Task List in SharePoint Online - SharePoint
- How to resolve Failed to create interpreter PyCharm Error - Python
- Enable spell check in Sublime Text (macOS) - MacOS
- Fix: Mac Screenshot opens in Mail Application - MacOS
- How to know the version of Teams installed - Teams