Close

JPA - Calling HSQLDB Stored Procedure involving Cursor to get result set

[Last Updated: Sep 18, 2018]

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

Getting result set from HSQLDB's cursor Select All Download
  • jpa-stored-procedure-cursor-with-result-set
    • src
      • main
        • java
          • com
            • logicbig
              • common
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also