In Spring Data JPA, @Procedure annotation can be used to map a user defined repository method to database stored procedure.

@Procedure definition snippet:

package org.springframework.data.jpa.repository.query;
 .......
@Target({ ElementType.METHOD, ElementType.ANNOTATION_TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface Procedure {
	String value() default "";//database procedure name short for procedureName()
	String procedureName() default ""; //database procedure name
	String name() default "";//name of the JPA @NamedStoredProcedureQuery
	String outputParameterName() default "";//output parameter name
}

As seen above there are basically two ways to call a stored procedure:

• By directly mapping to a database stored procedure by its name. In this case we need to specify either @Procedure#value or @Procedure#procedureName (If neither is configured and just @Procedure is used, then the database procedure is attempted to be mapped by the repository method name).
• By mapping to a named stored procedure declared via @NamedStoredProcedureQuery. In this case we need to specify @Procedure#name attribute.

In following example we will use the the first method i.e. directly mapping to stored procedure by name.

Example

In this example we are using Hsqldb.

Database scripts

src/main/resources/create-tables.sql

CREATE TABLE PERSON(
ID BIGINT PRIMARY KEY,
FIRST_NAME VARCHAR(50),
LAST_NAME VARCHAR(50),
ADDRESS VARCHAR(255)
)
/;

CREATE TABLE PERSON_HISTORY AS (SELECT * FROM PERSON) WITH NO DATA;

src/main/resources/procedure.sql

CREATE PROCEDURE MOVE_TO_HISTORY(IN person_id_in INT, OUT status_out BOOLEAN)
 MODIFIES SQL DATA
 BEGIN ATOMIC

 DECLARE temp_count INTEGER;
 SET temp_count = -1;
 SET status_out = FALSE;

  select count(*) into temp_count from PERSON p where p.id = person_id_in;
  if temp_count > -1  THEN
      SET status_out = TRUE;
      insert into PERSON_HISTORY (select * from PERSON p where p.id = person_id_in);
      delete from PERSON p where p.id = person_id_in;
 end if;
 END
 /;

JPA entity

@Entity
public class Person {
  @Id
  @GeneratedValue
  private long id;
  @Column(name = "FIRST_NAME")
  private String firstName;
  @Column(name = "LAST_NAME")
  private String lastName;
  private String address;
    .............
}

Repository

public interface PersonRepository extends CrudRepository<Person, Long> {

  @Transactional
  @Procedure(procedureName = "MOVE_TO_HISTORY")
  boolean movePersonToHistory(@Param("person_id_in") int personId);
}

JavaConfig

@EnableJpaRepositories
@ComponentScan
@Configuration
public class AppConfig {

  @Bean
  public DataSource dataSource() {
      return new EmbeddedDatabaseBuilder()
              .setType(EmbeddedDatabaseType.HSQL)
              .addScript("create-tables.sql")
              .addScript("procedure.sql")
              .setSeparator("/;")
              .build();
  }

  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
      LocalContainerEntityManagerFactoryBean factory =
              new LocalContainerEntityManagerFactoryBean();
      factory.setPersistenceProviderClass(HibernatePersistenceProvider.class);
      factory.setDataSource(dataSource());
      Properties properties = new Properties();
      properties.setProperty("hibernate.hbm2ddl.auto", "update");
      factory.setJpaProperties(properties);
      return factory;
  }

  @Bean
  public PlatformTransactionManager transactionManager() {
      JpaTransactionManager txManager = new JpaTransactionManager();
      txManager.setEntityManagerFactory(entityManagerFactory().getObject());
      return txManager;
  }
}

Example Client

@Component
public class ExampleClient {

  @Autowired
  private PersonRepository repo;

  public void run() {
      List<Person> persons = createPersons();
      repo.saveAll(persons);

      System.out.println(" -- getting all persons --");
      Iterable<Person> iterable = repo.findAll();
      List<Person> allPersons = StreamSupport.stream(iterable.spliterator(), false)
                                             .collect(Collectors.toList());
      allPersons.forEach(System.out::println);

      Person person = allPersons.get(0);
      System.out.printf("-- moving person with id %s to history --%n", person.getId());
      boolean b = repo.movePersonToHistory((int) person.getId());
      System.out.println("output status: " + b);

      person = allPersons.get(1);
      System.out.printf("-- moving person with id %s to history --%n", person.getId());
      b = repo.movePersonToHistory((int) person.getId());
      System.out.println("output status: " + b);

      System.out.println("-- getting all persons again --");
      repo.findAll().forEach(System.out::println);
  }

  private List<Person> createPersons() {
      return Arrays.asList(Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
              Person.create("Robin", "Cash", "64 Zella Park"),
              Person.create("Chary", "Mess", "112 Yellow Hill"),
              Person.create("Rose", "Kantata", "2736 Kooter Lane"),
              Person.create("Mike", "Togglie", "111 Cool Dr"));
  }
}

Main class

public class ExampleMain {

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      ExampleClient exampleClient = context.getBean(ExampleClient.class);
      exampleClient.run();
      EntityManagerFactory emf = context.getBean(EntityManagerFactory.class);
      emf.close();
  }
}

 -- getting all persons --
Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Person{id=2, firstName='Robin', lastName='Cash', address='64 Zella Park'}
Person{id=3, firstName='Chary', lastName='Mess', address='112 Yellow Hill'}
Person{id=4, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}
Person{id=5, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}
-- moving person with id 1 to history --
output status: true
-- moving person with id 2 to history --
output status: true
-- getting all persons again --
Person{id=3, firstName='Chary', lastName='Mess', address='112 Yellow Hill'}
Person{id=4, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}
Person{id=5, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}

Also check out how to call stored procedure in pure JPA.

Example Project

Dependencies and Technologies Used:

• spring-data-jpa 2.0.10.RELEASE: Spring Data module for JPA repositories.
  Uses org.springframework:spring-context version 5.0.9.RELEASE
• hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• hsqldb 2.4.1: HSQLDB - Lightweight 100% Java SQL Database Engine.
• JDK 1.8
• Maven 3.5.4