This example shows how to use Spring Data @Procedure annotation on repository methods to map JPA @NamedStoredProcedureQuery.

We are going to use Oracle database. One of our example procedures will involve Oracle Ref Cursor.

Also the combination of Spring Data JPA (2.0.10.RELEASE) and Hibernate (5.3.6.Final) does not seem to work with ref cursor so we are going to use EclipseLink as JPA provider.

Example

Setting up oracle database

Follow this guide to install Oracle database and SQL Developer if you already don't have them. Also follow this to install oracle JDBC driver to local maven repository.

Execute following script in Oracle SQL Developer.

src/main/resources/db.sql

DROP TABLE PERSON;
DROP SEQUENCE SQ_PERSON;

CREATE TABLE PERSON(
 ID NUMBER(19),
 FIRST_NAME VARCHAR(255),
 LAST_NAME VARCHAR(255),
 ADDRESS VARCHAR(255),
 PRIMARY KEY (ID)
);

CREATE SEQUENCE SQ_PERSON MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
DROP TABLE PERSON_HISTORY;
CREATE TABLE PERSON_HISTORY AS SELECT * FROM PERSON WHERE 1=0;

CREATE OR REPLACE PROCEDURE MOVE_TO_HISTORY(person_id_in IN NUMBER, msg_out OUT VARCHAR2)
IS
 temp_count NUMBER := -1;
BEGIN
   select count(*) into temp_count from PERSON p where p.id = person_id_in;
  IF temp_count > -1  THEN
      insert into PERSON_HISTORY (select * from PERSON where id = person_id_in);
      msg_out := 'Person with id: ' || person_id_in || ' moved to History table. Update count: ' || sql%Rowcount;
      delete from PERSON p where p.id = person_id_in;
  ELSE
   msg_out := 'No Person Exists with id: '|| person_id_in;
 END IF;
END;
/

CREATE OR REPLACE PROCEDURE FETCH_PERSON_HISTORY(history_cursor OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN history_cursor FOR SELECT * FROM PERSON_HISTORY;
END;

JPA entity

@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(
      name = Person.NamedQuery_MoveToHistory,
      procedureName = "MOVE_TO_HISTORY",
      parameters = {
              @StoredProcedureParameter(type = Long.class, mode = ParameterMode.IN),
              @StoredProcedureParameter(type = String.class, mode = ParameterMode.OUT),
      }
),
@NamedStoredProcedureQuery(
      name = Person.NamedQuery_FetchFromHistory,
      procedureName = "FETCH_PERSON_HISTORY",
      resultClasses = Person.class,
      parameters = {
              @StoredProcedureParameter(type = void.class, mode = ParameterMode.REF_CURSOR)
      }
)})

@Entity
public class Person {
  public static final String NamedQuery_MoveToHistory = "moveToHistory";
  public static final String NamedQuery_FetchFromHistory = "fetchFromHistory";
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SQ_PERSON")
  @SequenceGenerator(sequenceName = "SQ_PERSON", allocationSize = 1, name = "SQ_PERSON")
  private Long id;
  @Column(name = "FIRST_NAME")
  private String firstName;
  @Column(name = "LAST_NAME")
  private String lastName;
  private String address;
    .............
}

Spring repository

@Transactional
public interface PersonRepository extends CrudRepository<Person, Long> {

  @Procedure(name = Person.NamedQuery_MoveToHistory)
  String movePersonToHistory(@Param("person_id_in") long personId);

  @Procedure(name = Person.NamedQuery_FetchFromHistory)
  List<Person> fetchPersonHistory();
}

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());
      String b = repo.movePersonToHistory(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(person.getId());
      System.out.println("output status: " + b);

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

      System.out.println("-- fetching from  person history --");
      repo.fetchPersonHistory().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=13, firstName='Chary', lastName='Mess', address='112 Yellow Hill'}
Person{id=12, firstName='Robin', lastName='Cash', address='64 Zella Park'}
Person{id=15, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}
Person{id=14, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}
Person{id=11, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
-- moving person with id 13 to history --
output status: Person with id: 13 moved to History table. Update count: 1
-- moving person with id 12 to history --
output status: Person with id: 12 moved to History table. Update count: 1
-- getting all persons again --
Person{id=15, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}
Person{id=14, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}
Person{id=11, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
-- fetching from  person history --
Person{id=13, firstName='Chary', lastName='Mess', address='112 Yellow Hill'}
Person{id=12, firstName='Robin', lastName='Cash', address='64 Zella Park'}

Also check out how to do the same thing in pure JPA + Hibernate.

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
• eclipselink 2.6.5: EclipseLink build based upon Git transaction b3d05bd.
  Related JPA version: org.eclipse.persistence:javax.persistence version 2.1.1
• ora-jdbc 7: POM was created from install:install-file.
• JDK 1.8
• Maven 3.5.4