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.sqlCREATE 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 ProjectDependencies 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
|