Create and Call Procedure using Spring Boot with JPA

Project Structure:

mysql> create database students;

mysql> use students;

#Create a Procedure for adding student in mysql use below statment:

mysql> DELIMITER $$
mysql> create procedure addStudent(IN id int, IN name varchar(35), IN marks float)
    -> BEGIN
    -> insert into student values(id,marks,name);
    -> END $$
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;

#Create a Procedure for fetching student in mysql use below statment:

mysql> DELIMITER $$
mysql> create procedure fetchStudents()
    -> BEGIN
    -> select * from student;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

ProcedureTutorialApplication.java

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ProcedureTutorialApplication {

	public static void main(String[] args) {
		SpringApplication.run(ProcedureTutorialApplication.class, args);
	}

}

application.properties

server.port=8081
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.url=jdbc:mysql://localhost:3306/students
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

StudentController.java

package com.example.demo.controller;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.example.demo.model.Student;
import com.example.demo.service.StudentService;

@RestController
public class StudentController {

	@Autowired
	private StudentService studentService;

	@PostMapping("/addstudent")
	public Integer addStudent(@RequestBody Student student) {
		return studentService.addStudent(student);
	}

	@GetMapping("/students")
	public List<Student> fetchStudents() {
		return studentService.fetchStudents();
	}

}

Student.java

package com.example.demo.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 org.springframework.stereotype.Component;

@Component
@Entity
@NamedStoredProcedureQueries({
		@NamedStoredProcedureQuery(name = "addStudent", procedureName = "addStudent", parameters = {
		@StoredProcedureParameter(mode = ParameterMode.IN, name = "id", type = Integer.class),
		@StoredProcedureParameter(mode = ParameterMode.IN, name = "name", type = String.class),
		@StoredProcedureParameter(mode = ParameterMode.IN, name = "marks", type = Float.class) }),

		@NamedStoredProcedureQuery(name = "fetchStudents", procedureName = "fetchStudents") })
public class Student {
	@Id
	private int id;
	private String name;
	private float marks;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public float getMarks() {
		return marks;
	}

	public void setMarks(float marks) {
		this.marks = marks;
	}

}

StudentRepository.java

package com.example.demo.repo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.example.demo.model.Student;

@Repository
public interface StudentRepository extends JpaRepository<Student, Integer>{

	
}

StudentService.java

package com.example.demo.service;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.example.demo.model.Student;
import com.example.demo.repo.StudentRepository;

@Service
public class StudentService {

	@Autowired
	@PersistenceContext
	private EntityManager entityManager;

	@Transactional
	public int addStudent(Student student) {
		return entityManager.createNamedStoredProcedureQuery("addStudent").setParameter("id", student.getId())
				.setParameter("name", student.getName()).setParameter("marks", student.getMarks()).executeUpdate();
	}

	@Transactional
	@SuppressWarnings("unchecked")
	public List<Student> fetchStudents() {
		return entityManager.createNamedStoredProcedureQuery("fetchStudents").getResultList();
	}

}

pom.xml

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<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>

Output Screens:

---