This post demonstrates how to call Stored Procedure using Spring Data and @Procedure.

Contents

1 Requirements
- 1.1 Spring Initialzr in IntelliJ IDEA
2 Database Table
3 Create Stored Procedure
4 Entity and Repository
- 4.1 Person entity
- 4.2 PersonRepository
5 application.properties
6 Testing
- 6.1 Output
7 Download the codes

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.

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
;

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

DELIMITER $$
CREATE PROCEDURE `addPerson`(personName varchar(50))
BEGIN
    INSERT INTO PERSON(NAME) VALUES(personName);
END$$
DELIMITER ;

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

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

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]

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);
}

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.

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

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]

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");
	}
}