Last Updated on
This post demonstrates how to call Stored Procedure using Spring Data and @Procedure.
Contents
Requirements
Stuff used in this post.
- IntelliJ IDEA Ultimate 2016.3
- Java 8
- Windows 10 64bit
- Spring Boot 1.5.6.RELEASE
- Spring Initialzr
- MySQL and MySQL Workbench
Spring Initialzr in IntelliJ IDEA
[wp_ad_camp_1]
Database Table
First, we need to create a database table.
|
1 2 3 4 5 6 |
CREATE TABLE `person` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ; |
Create Stored Procedure
|
1 2 3 4 5 6 |
DELIMITER $$ CREATE PROCEDURE `addPerson`(personName varchar(50)) BEGIN INSERT INTO PERSON(NAME) VALUES(personName); END$$ DELIMITER ; |
Entity and Repository
Then, we create our entity and repository.
Person entity
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
package com.turreta.springboot.springdata.sp; import javax.persistence.*; @Entity @Table(name = "person") public class Person { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Integer id; private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } } |
PersonRepository
[wp_ad_camp_2]
|
1 2 3 4 5 6 7 8 9 10 |
package com.turreta.springboot.springdata.sp; import org.springframework.data.jpa.repository.query.Procedure; import org.springframework.data.repository.CrudRepository; public interface PersonRepository extends CrudRepository<Person, Integer> { @Procedure void addPerson(String name); } |
Note the method name here. It is the same as our Stored Procedure. @Procedure maps this method to that actual stored procedure.
application.properties
Before we can access our database, we need a valid user account and the following properties set up in application.properties file.
|
1 2 3 4 |
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/jpahibernatedemo spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.username=root spring.datasource.password=password |
Testing
The following codes are from our main Spring Boot class.
[wp_ad_camp_3]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
package com.turreta.springboot.springdata.sp; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.ApplicationContext; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; @SpringBootApplication public class ComTurretaSpringbootSpringdataSpApplication { public static void main(String[] args) { ApplicationContext c = SpringApplication.run(ComTurretaSpringbootSpringdataSpApplication.class, args); PersonRepository repo = c.getBean(PersonRepository.class); repo.addPerson("KL"); repo.addPerson("SG"); } } |
Output
[wp_ad_camp_4]
Download the codes
https://github.com/Turreta/Call-Stored-Procedure-using-Spring-Data-and-Procedure
[wp_ad_camp_5]
