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