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

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

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

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.

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

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

Download the codes

https://github.com/Turreta/Call-Stored-Procedure-using-Spring-Data-and-Procedure

Call Stored Procedure using Spring Data and @Procedure

Requirements

Spring Initialzr in IntelliJ IDEA

Database Table

Create Stored Procedure

Entity and Repository

Person entity

PersonRepository

application.properties

Testing

Output

Download the codes

Karl San Gabriel

Recent Comments

Requirements

Spring Initialzr in IntelliJ IDEA

Database Table

Create Stored Procedure

Entity and Repository

Person entity

PersonRepository

application.properties

Testing

Output

Download the codes

Related posts:

Karl San Gabriel

Recent Comments