Stored Procedures are a set of compiled SQL statements residing in the database. In this tutorial, we will explore how to create a simple stored procedure in the MySQL database and call it in a spring boot application.
1. Introduction
- Spring Boot is a module that provides rapid application development feature to the spring framework including auto-configuration, standalone-code, and production-ready code
- It creates applications that are packaged as jar and are directly started using embedded servlet container (such as Tomcat, Jetty or Undertow). Thus, no need to deploy the war files
- It simplifies the maven configuration by providing the starter template and helps to resolve the dependency conflicts. It automatically identifies the required dependencies and imports them in the application
- It helps in removing the boilerplate code, extra annotations, and xml configurations
- It provides a powerful batch processing and manages the rest endpoints
- It provides an efficient jpa-starter library to effectively connect the application with the relational databases
1.1 Download and Install MySQL
You can watch this video in order to download and install the MySQL database on your Windows operating system.
Now, open the eclipse ide and let’s see how to implement this tutorial in spring boot.
2. Spring JDBC StoredProcedure Class Example
Here is a systematic guide for implementing this tutorial.
2.1 Tools Used
We are using Eclipse Kepler SR2, JDK 8, MySQL, and Maven.
2.2 Project Structure
In case you are confused about where you should create the corresponding files or folder, let us review the project structure of the spring boot application.
2.3 Project Creation
This section will demonstrate how to create a Java-based Maven project with Eclipse. In Eclipse IDE, go to File -> New -> Maven Project.
In the New Maven Project window, it will ask you to select a project location. By default, ‘Use default workspace location’ will be selected. Just click on the next button to proceed.
Select the Maven Web App archetype from the list of options and click next.
It will ask you to ‘Enter the group and the artifact id for the project’. We will input the details as shown in the below image. The version number will be by default: 0.0.1-SNAPSHOT.
Click on Finish and the creation of a maven project is completed. If you observe, it has downloaded the maven dependencies and a pom.xml file will be created. It will have the following code:
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.springboot.jdbc.storedprocedure</groupId> <artifactId>Springbootjdbcstoredproceduretutorial</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> </project>
Let’s start building the application!
3. Creating a Spring Boot application
Below are the steps involved in developing the application. But before starting we are assuming that developers have installed the MySQL database on their machine.
3.1 Database and Table Creation
The following script creates a database called sampledb with a table employee. Open MySQL terminal or workbench to execute this sql script.
CREATE DATABASE IF NOT EXISTS sampledb; USE sampledb; CREATE TABLE employee ( eid INT(50) NOT NULL AUTO_INCREMENT, ename VARCHAR(200) DEFAULT NULL, edesig VARCHAR(200) DEFAULT NULL, edept VARCHAR(100) DEFAULT NULL, esal INT(100) DEFAULT NULL, PRIMARY KEY (eid) ); INSERT INTO employee (eid, ename, edesig, edept, esal) VALUES (1, 'John Lark', 'Lead', 'Technology', 30000); INSERT INTO employee (eid, ename, edesig, edept, esal) VALUES (2, 'Natalie Atlas', 'Associate', 'Human Resource', 24000); INSERT INTO employee (eid, ename, edesig, edept, esal) VALUES (3, 'Daniel Brown', 'Associate', 'Technology', 27000); INSERT INTO employee (eid, ename, edesig, edept, esal) VALUES (4, 'Tom Hunt', 'Manager', 'Technology', 42000); INSERT INTO employee (eid, ename, edesig, edept, esal) VALUES (5, 'Edward Clark', 'Senior Manager', 'Human Resource', 55000); INSERT INTO employee (eid, ename, edesig, edept, esal) VALUES (6, 'Jason Bourne', 'Lead', 'Administration', 24000); SELECT * FROM employee;
If everything goes well, the table will be created, and the inserted records will be displayed.
3.2 Stored Procedures Creation
The following script creates three different stored procedures for the table employee. Open MySQL terminal or workbench to execute this sql script.
----- STORED PROCEDURE QUERY #1 ----- DELIMITER $ CREATE PROCEDURE findAllEmployees () BEGIN SELECT * FROM employee; END $ DELIMITER ; ----- STORED PROCEDURE QUERY #2 ----- DELIMITER $ CREATE PROCEDURE findEmployeeByDepartment (IN emp_department VARCHAR(200)) BEGIN SELECT * FROM employee emp WHERE emp.edept = emp_department; END $ DELIMITER ; ----- STORED PROCEDURE QUERY #3 ----- DELIMITER $ CREATE PROCEDURE findEmployeeCountByDesignation (IN emp_designation VARCHAR(200), OUT designation_count INT(50)) BEGIN SELECT COUNT(*) INTO designation_count FROM employee emp WHERE emp.edesig = emp_designation; END $ DELIMITER ;
If everything goes well, the stored procedures will be created as shown in Fig. 7.
3.3 Maven Dependencies
Here, we specify the dependencies for the Spring Boot and MySQL. Maven will automatically resolve the other dependencies. The updated file will have the following code.
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.springboot.jdbc.storedprocedure</groupId> <artifactId>Springbootjdbcstoredproceduretutorial</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>Springbootjdbcstoredproceduretutorial Maven Webapp</name> <url>http://maven.apache.org</url> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.3.RELEASE</version> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> </dependencies> <build> <finalName>Springbootjdbcstoredproceduretutorial</finalName> </build> </project>
3.4 Application Properties
Create a new properties file at the location: Springbootjdbcstoredproceduretutorial/src/main/resources/ and add the following code to it.
application.properties
# Application configuration.
server.port=8102
# Local mysql database configuration.
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/sampledb
spring.datasource.username = root
spring.datasource.password =
# Hibernate configuration.
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = validate
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
# Logging configuration.
logging.level.com.springboot.storedprocedure=DEBUG
logging.pattern.console= %d{yyyy-MM-dd HH:mm:ss} - %msg%n
3.5 Java Classes
Let’s write all the java classes involved in this application.
3.5.1 Implementation/Main class
Add the following code the main class to bootstrap the application from the main method. Always remember, the entry point of the spring boot application is the class containing @SpringBootApplication annotation and the static main method.
Myapplication.java
package com.springboot.storedprocedure;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* Main implementation class which serves two purpose in a spring boot application: Configuration and bootstrapping.
* @author yatin-batra
*/
@SpringBootApplication
public class Myapplication {
public static void main(String[] args) {
SpringApplication.run(Myapplication.class, args);
}
}
3.5.2 Model class
Add the following code to the employee model class.
Employee.java
package com.springboot.storedprocedure.model;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureParameter;
import javax.persistence.Table;
@Entity
@Table(name= "employee")
@NamedStoredProcedureQueries(value= {
@NamedStoredProcedureQuery(name= "procedure-one", procedureName= "findAllEmployees"),
@NamedStoredProcedureQuery(name= "procedure-two", procedureName= "findEmployeeByDepartment", parameters= {
@StoredProcedureParameter(mode= ParameterMode.IN, name= "emp_department", type= String.class)
}),
@NamedStoredProcedureQuery(name= "procedure-third", procedureName= "findEmployeeCountByDesignation", parameters= {
@StoredProcedureParameter(mode= ParameterMode.IN, name= "emp_designation", type= String.class),
@StoredProcedureParameter(mode= ParameterMode.OUT, name= "designation_count", type= Integer.class)
})
})
public class Employee {
@Id
private int eid;
private String ename;
private String edesig;
private String edept;
private int esal;
public int getEid() {
return eid;
}
public void setEid(int eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getEdesig() {
return edesig;
}
public void setEdesig(String edesig) {
this.edesig = edesig;
}
public String getEdept() {
return edept;
}
public void setEdept(String edept) {
this.edept = edept;
}
public int getEsal() {
return esal;
}
public void setEsal(int esal) {
this.esal = esal;
}
@Override
public String toString() {
return "Employee [eid=" + eid + ", ename=" + ename + ", edesig=" + edesig + ", edept=" + edept + ", esal="
+ esal + "]";
}
}
3.5.3 Data-Access-Object class
Add the following code to the Dao class that will handle the stored procedure queries.
Employeedao.java
package com.springboot.storedprocedure.repository;
import javax.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.springboot.storedprocedure.model.Employee;
@Repository
public class Employeedao {
@Autowired
private EntityManager em;
/**
* Method to fetch all employees from the db.
* @return
*/
@SuppressWarnings("unchecked")
public Iterable<Employee> getAllEmployees() {
return em.createNamedStoredProcedureQuery("procedure-one").getResultList();
}
/**
* Method to fetch employees on the basis of department.
* @param input
* @return
*/
@SuppressWarnings("unchecked")
public Iterable<Employee> getEmployeesByDepartment(String input) {
return em.createNamedStoredProcedureQuery("procedure-two").setParameter("emp_department", input).getResultList();
}
/**
* Method to fetch the employees count on the basis of designation.
* @param edesignation
* @return
*/
public Integer getEmployeesCountByDesignation(String input) {
return (Integer) em.createNamedStoredProcedureQuery("procedure-third").setParameter("emp_designation", input).getOutputParameterValue("designation_count");
}
}
3.5.4 Controller class
Add the following code to the controller class designed to handle the incoming requests. The class is annotated with the @RestController annotation where every method returns an object as a json response instead of a view.
Employeecontroller.java
package com.springboot.storedprocedure.controller;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.springboot.storedprocedure.model.Employee;
import com.springboot.storedprocedure.repository.Employeedao;
@RestController
@RequestMapping(value= "/api/employee")
public class Employeecontroller {
@Autowired
Employeedao edao;
private final Logger logger = LoggerFactory.getLogger(this.getClass());
/**
* Method to fetch all employees from the db.
* @return
*/
@GetMapping(value= "/getall")
public Iterable<Employee> getAll() {
logger.debug("Get all employees.");
return edao.getAllEmployees();
}
/**
* Method to fetch employees on the basis of department.
* @param department
* @return
*/
@GetMapping(value= "/department/{employee-department}")
public Iterable<Employee> getEmployeesByDepartment(@PathVariable(name= "employee-department") String department) {
logger.debug("Getting count for department= {}.", department);
return edao.getEmployeesByDepartment(department);
}
/**
* Method to fetch employees count on the basis of designation.
* @param designation
* @return
*/
@GetMapping(value= "/count/{employee-designation}")
public Integer getEmployeeCountByDesignation(@PathVariable(name= "employee-designation") String designation) {
logger.debug("Getting count for employee-designations= {}.", designation);
return edao.getEmployeesCountByDesignation(designation);
}
}
4. Run the Application
As we are ready with all the changes, let us compile the spring boot project and run the application as a java project. Right click on the Myapplication.java class, Run As -> Java Application.
Developers can debug the example and see what happens after every step. Enjoy!
5. Project Demo
Open the postman tool and hit the following urls to display the data in the json format.
// Get all employees http://localhost:8102/api/employee/getall // Get employees by department http://localhost:8102/api/employee/department/Technology // Get employee count by designation http://localhost:8102/api/employee/count/Lead
That is all for this tutorial and I hope the article served you whatever you were looking for. Happy Learning and do not forget to share!
6. Conclusion
In this section, developers learned how to create a Spring Boot application with MySQL and execute the stored procedure queries. Developers can download the sample application as an Eclipse project in the Downloads section.
7. Download the Eclipse Project
This was an example of executing the Stored Procedures queries with Spring Boot & MySQL.
You can download the full source code of this example here: Spring JDBC StoredProcedure Class Example
Thanks, but what happened when the doamin is not a table (maybe a join from three tables like mi procedure)?
Regards, goo tutorial