Close

JPA - Calling Stored Procedure With Ref Cursor Output Parameter

[Updated: Sep 12, 2018, Created: Sep 11, 2018]

This example shows how to call Oracle stored procedures with ref_cursor out parameter.

Following method of EntityManager can be used to create an instance of StoredProcedureQuery with resultClasses argument to map records returned by a database cursor:

StoredProcedureQuery createStoredProcedureQuery(String procedureName, Class... resultClasses)

Quick example

CREATE OR REPLACE PROCEDURE myStoredProcedureName(my_cursor OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN my_cursor FOR SELECT * FROM MY_TABLE;
END;
 StoredProcedureQuery procedureQuery = 
                        entityManager.createStoredProcedureQuery("myStoredProcedureName", ResultType.class);
 procedureQuery.registerStoredProcedureParameter("my_cursor", void.class, ParameterMode.REF_CURSOR);
 procedureQuery.execute();
 List<ResultType> resultList = procedureQuery.getResultList();

Example

Follow this guide to install Oracle and SQL Developer if you already don't have it. (Also see this example of how to connect oracle database in JPA).

pom.xml

<project .....>
<modelVersion>4.0.0</modelVersion>

<groupId>com.logicbig.example</groupId>
<artifactId>jpa-stored-procedure-with-oracle-ref-cursor</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.3.6.Final</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ora-jdbc</artifactId>
<version>7</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>

</project>

You also have to install Oracle JDBC driver locally for above ora-jdbc dependency to resolve.

Creating database tables and procedures

Copy paste following script to Oracle SQL Developer and execute it.

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;

The stored procedure 'MOVE_TO_HISTORY' moves a person row from 'PERSON' table to 'PERSON_HISTORY' table. The procedure 'FETCH_PERSON_HISTORY' populates the history_cursor with the records fetched from PERSON_HISTORY.

Creating JPA application

src/main/resources/META-INF/persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="example-unit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
            <property name="javax.persistence.jdbc.user" value="system"/>
            <property name="javax.persistence.jdbc.password" value="1111"/>
        </properties>
    </persistence-unit>
</persistence>

Entity

@Entity
public class Person {
  @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;
    .............
}

Calling stored procedures

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          reset();
          persistEntities();
          findAllEmployeeEntities();
          movePersonToHistoryByName("Dana");
          movePersonToHistoryByName("Mike");
          fetchPersonHistory();

      } finally {
          entityManagerFactory.close();
      }
  }

  //calling procedure with ref_cursor
  private static void fetchPersonHistory() {
      System.out.println("-- Fetching person History --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      StoredProcedureQuery procedureQuery = entityManager.createStoredProcedureQuery("FETCH_PERSON_HISTORY",
              Person.class);//PERSON_HISTORY table also has same fields
      procedureQuery.registerStoredProcedureParameter("history_cursor", void.class, ParameterMode.REF_CURSOR);
      procedureQuery.execute();
      @SuppressWarnings("unchecked")
      List<Person> resultList = procedureQuery.getResultList();
      //without Person.class arg above list of object array will be returned
      resultList.forEach(System.out::println);
  }

  private static void movePersonToHistoryByName(String name) {
      System.out.printf("-- Moving person to history table name: %s --%n", name);
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      //get person id
      TypedQuery<Long> query = entityManager
              .createQuery("SELECT p.id FROM Person p WHERE p.firstName = :firstName", Long.class);
      query.setParameter("firstName", name);
      Long personId = query.getSingleResult();
      //stored procedure
      StoredProcedureQuery procedureQuery = entityManager
              .createStoredProcedureQuery("MOVE_TO_HISTORY");
      procedureQuery.registerStoredProcedureParameter("person_id_in", Long.class, ParameterMode.IN);
      procedureQuery.registerStoredProcedureParameter("msg_out", String.class, ParameterMode.OUT);
      entityManager.getTransaction().begin();
      procedureQuery.setParameter("person_id_in", personId);
      procedureQuery.execute();
      Object msg_out = procedureQuery.getOutputParameterValue("msg_out");
      System.out.println("Out msg= " + msg_out);
      entityManager.getTransaction().commit();
  }

  private static void findAllEmployeeEntities() {
      System.out.println("-- finding Person entities -  --");
      EntityManager em = entityManagerFactory.createEntityManager();
      TypedQuery<Person> query = em.createQuery("SELECT p from Person p", Person.class);
      List<Person> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void persistEntities() {
      Person person1 = Person.create("Dana", "Whitley", "464 Gorsuch Drive");
      Person person2 = Person.create("Robin", "Cash", "64 Zella Park");
      Person person3 = Person.create("Chary", "Mess", "112 Yellow Hill");
      Person person4 = Person.create("Rose", "Kantata", "2736 Kooter Lane");
      Person person5 = Person.create("Mike","Togglie", "111 Cool Dr");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(person1);
      em.persist(person2);
      em.persist(person3);
      em.persist(person4);
      em.persist(person5);
      em.getTransaction().commit();
      em.close();
  }

  private static void reset() {
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      Query query = em.createQuery("DELETE FROM Person");
      query.executeUpdate();
      query = em.createNativeQuery("DELETE from PERSON_HISTORY");
      query.executeUpdate();
      em.getTransaction().commit();
  }
}
-- finding Person entities -  --
Person{id=66, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Person{id=67, firstName='Robin', lastName='Cash', address='64 Zella Park'}
Person{id=68, firstName='Chary', lastName='Mess', address='112 Yellow Hill'}
Person{id=69, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}
Person{id=70, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}
-- Moving person to history table name: Dana --
Out msg= Person with id: 66 moved to History table. Update count: 1
-- Moving person to history table name: Mike --
Out msg= Person with id: 70 moved to History table. Update count: 1
-- Fetching person History --
Person{id=66, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Person{id=70, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}

Example Project

Dependencies and Technologies Used:

  • hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • ora-jdbc 7: POM was created from install:install-file.
  • JDK 1.8
  • Maven 3.5.4

Calling Oracle Stored Procedure involving Ref Cursor Select All Download
  • jpa-stored-procedure-with-oracle-ref-cursor
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also