spring

Spring JDBC StoredProcedure Class Example

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.

Spring JDBC StoredProcedure Class -  Application Structure
Fig. 1: Application Structure

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.

Spring JDBC StoredProcedure Class - Maven Project
Fig. 2: Create a 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.

Spring JDBC StoredProcedure Class - Project Details
Fig. 3: Project Details

Select the Maven Web App archetype from the list of options and click next.

Fig. 4: Archetype Selection

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.

Spring JDBC StoredProcedure Class - Archetype Parameters
Fig. 5: Archetype Parameters

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.

Spring JDBC StoredProcedure Class - Database
Fig. 6: Database and Table

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.

Fig. 7: Stored Procedures

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.

Fig. 8: Run the 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.

Download
You can download the full source code of this example here: Spring JDBC StoredProcedure Class Example

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jose
Jose
4 years ago

Thanks, but what happened when the doamin is not a table (maybe a join from three tables like mi procedure)?
Regards, goo tutorial

Back to top button