Spring Boot - JDBC Template for calling Stored Procedures
Mon, Nov 16, 2020
Read in 1 minutes
For calling the stored procedure ,we are going to use “SimpleJdbcCall” class.
For this blog,I am going to use mysql as database server.
Application.Properties file:
spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
Pom.xml:
In pom.xml add the springboot dependencies for mysql ,starterweb and springboot data jdbc
dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
I have created a stored procedure in MYSQL .
Stored Procedure: get_countries -This stored procedure will return all the data in the countries table.
BEGIN
select * from countries;
END
HelloController.java
I have just created a controller to call the DAO methods and display the result of the stored procedcure .
@RestController
public class HelloController {
@Autowired
CountryDAOImpl countryImpl;
@GetMapping("/hello")
public Map<String, Object> sayHello() {
countryImpl.execute();
return countryImpl.execute();
}
}
CountryDAOImpl.java
In this DAOImpl class , we will call stored procedure using ‘SimpleJdbcCall’
@Component
public class CountryDAOImpl {
@Autowired
private JdbcTemplate jdbcTemplate;
public Map<String, Object> execute() {
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("get_countries");
//Map<String, Object> inParamMap = new HashMap<String, Object>();
//inParamMap.put("firstName", "FValue");
//inParamMap.put("lastName", "LValue");
SqlParameterSource in = new MapSqlParameterSource(null);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
System.out.println(simpleJdbcCallResult);
return simpleJdbcCallResult;
}
}
Output:
When you hit http://localhost:8080/hello ,you will get the result of the stored procedure .
