In the last example we saw how to call a stored procedure involving Oracle's REF_CUSOR. In following example we will use HSQLDB database. We will call HSQLDB's store procedure with cursor output.

HSQLDB cursor is used to return a result set from a procedure. As HSQLDB is written in Java, the getResultSet() method of JDBC CallableStatement is used to retrieve the JDBC ResultSet.

Example

src/main/resources/procedure.sql

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

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


CREATE PROCEDURE FETCH_PERSON_HISTORY()
READS SQL DATA DYNAMIC RESULT SETS 1
 BEGIN ATOMIC
  DECLARE history_cursor CURSOR FOR SELECT * FROM PERSON_HISTORY;
  open history_cursor;
 END
 /;

Above procedure 'MOVE_TO_HISTORY' transfers a record from PERSON table to PERSON_HISTORY table. The procedure 'FETCH_PERSON_HISTORY' returns a cursor of rows fetched from PERSON_HISTORY table.

A custom implementation of ImportSqlCommandExtractor of Hibernate is used to load above sql script.

JPA Entity

@Entity
public class Person {
    @Id
    @GeneratedValue
    private long id;
    private String firstName;
    private String lastName;
    private String address;
    .............
}

Calling Store procedures

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

    public static void main(String[] args) {
        try {
            persistPersons();
            findAllEmployeeEntities();
            movePersonToHistoryById(1);
            movePersonToHistoryById(2);
            fetchPersonHistory();
        } finally {
            entityManagerFactory.close();
        }
    }

    private static void fetchPersonHistory() {
        System.out.println("-- Fetching person History --");
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        StoredProcedureQuery procedureQuery = entityManager
                .createStoredProcedureQuery("FETCH_PERSON_HISTORY", Person.class);
        boolean execute = procedureQuery.execute();
        while (!execute && procedureQuery.hasMoreResults()) {
            execute = procedureQuery.execute();
        }
        if (!execute) {
            System.err.println("Cannot find result set");
            return;
        }
        List resultList = procedureQuery.getResultList();
        resultList.forEach(System.out::println);
        entityManager.close();
    }

    private static void movePersonToHistoryById(long personId) {
        System.out.printf("-- Moving person to history table id: %s --%n", personId);
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        StoredProcedureQuery procedureQuery = entityManager
                .createStoredProcedureQuery("MOVE_TO_HISTORY");
        procedureQuery.registerStoredProcedureParameter("person_id_in", Integer.class, ParameterMode.IN);
        procedureQuery.registerStoredProcedureParameter("status_out", Boolean.class, ParameterMode.OUT);
        entityManager.getTransaction().begin();
        procedureQuery.setParameter("person_id_in", (int) personId);
        procedureQuery.execute();
        Object status_out = procedureQuery.getOutputParameterValue("status_out");
        System.out.println("Out status: " + status_out);
        entityManager.getTransaction().commit();
        entityManager.close();
    }

    public static void persistPersons() {
        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");
        EntityManager em = entityManagerFactory.createEntityManager();
        em.getTransaction().begin();
        em.persist(person1);
        em.persist(person2);
        em.persist(person3);
        em.getTransaction().commit();
        em.close();
    }

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

-- finding Person entities -  --
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'}
-- Moving person to history table id: 1 --
Out status: true
-- Moving person to history table id: 2 --
Out status: true
-- Fetching person History --
Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Person{id=2, firstName='Robin', lastName='Cash', address='64 Zella Park'}

Example Project

Dependencies and Technologies Used:

• hsqldb 2.4.1: HSQLDB - Lightweight 100% Java SQL Database Engine.
• hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• JDK 1.8
• Maven 3.5.4