In JPA, the methods EntityManager#createStoredProcedureQuery() takes the procedure name and return an instance of the interface StoredProcedureQuery which has various methods to set parameter names/types and get returned values . For example:
StoredProcedureQuery procedureQuery = entityManager.createStoredProcedureQuery("myProcedureName");
procedureQuery.registerStoredProcedureParameter("param1", Integer.class, ParameterMode.IN);
procedureQuery.registerStoredProcedureParameter("param2", Boolean.class, ParameterMode.OUT);
.......
procedureQuery.setParameter("param1", (int) theIntValue);
procedureQuery.execute();
Object resultValue = procedureQuery.getOutputParameterValue("param2");
Example
Entity
@Entity
public class Person {
@Id
@GeneratedValue
private long id;
private String firstName;
private String lastName;
private String address;
.............
}
Store Procedure Script
We are using HSQLDB in this example, following is the example stored-procedure and a related table which is not mapped to any of our entities:
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
/;
Above procedure simply transfers a person row from PERSON table to PERSON_HISTORY.
Hibernate (the JPA provider in our example), cannot load store-procedures scripts by default, that's why we need to include a custom implementation of ImportSqlCommandExtractor class (see the project browser below).
Following is our persistence.xml which specifies the script location via javax.persistence.sql-load-script-source property.
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.schema-generation.database.action" value="create"/>
<property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbc.JDBCDriver"/>
<property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:dataSource"/>
<property name="javax.persistence.sql-load-script-source" value="procedure.sql" />
<property name="hibernate.hbm2ddl.import_files_sql_extractor"
value="com.logicbig.common.CustomHibernateSqlExtractor" />
</properties>
</persistence-unit>
</persistence>
Creating and executing StoredProcedureQuery
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
nativeQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC'");
nativeQuery("SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'PUBLIC'");
persistPersons();
findAllEmployeeEntities();
movePersonToHistoryById(1);
findAllEmployeeEntities();
nativeQuery("select * from PERSON_HISTORY");
} finally {
entityManagerFactory.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();
}
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();
}
public static void nativeQuery(String s) {
System.out.printf("---------------------------%n'%s'%n", s);
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createNativeQuery(s);
List list = query.getResultList();
for (Object o : list) {
System.out.println(Arrays.toString((Object[]) o));
}
}
}---------------------------
'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC''
[PUBLIC, PUBLIC, PERSON, BASE TABLE, null, null, null, null, null, YES, NO, null]
[PUBLIC, PUBLIC, PERSON_HISTORY, BASE TABLE, null, null, null, null, null, YES, NO, null]
---------------------------
'SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'PUBLIC''
[PUBLIC, PUBLIC, MOVE_TO_HISTORY_10102, PUBLIC, PUBLIC, MOVE_TO_HISTORY, PROCEDURE, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, SQL, CREATE PROCEDURE PUBLIC.MOVE_TO_HISTORY(IN PERSON_ID_IN INTEGER,OUT STATUS_OUT BOOLEAN) SPECIFIC MOVE_TO_HISTORY_10102 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE TEMP_COUNT INTEGER;SET TEMP_COUNT=-1;SET STATUS_OUT=FALSE;SELECT COUNT(*)INTO TEMP_COUNT FROM PUBLIC.PERSON P WHERE P.ID=PERSON_ID_IN;IF TEMP_COUNT>-1 THEN SET STATUS_OUT=TRUE;INSERT INTO PUBLIC.PERSON_HISTORY(ID,ADDRESS,FIRSTNAME,LASTNAME)(SELECT P.ID,P.ADDRESS,P.FIRSTNAME,P.LASTNAME FROM PUBLIC.PERSON P WHERE P.ID=PERSON_ID_IN);DELETE FROM PUBLIC.PERSON P WHERE P.ID=PERSON_ID_IN;END IF;END, null, null, null, NO, MODIFIES SQL DATA, null, null, YES, 0, null, null, DEFINER, null, null, null, null, null, null, YES, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null]
-- 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
-- finding Person entities - --
Person{id=2, firstName='Robin', lastName='Cash', address='64 Zella Park'}
Person{id=3, firstName='Chary', lastName='Mess', address='112 Yellow Hill'}
---------------------------
'select * from PERSON_HISTORY'
[1, 464 Gorsuch Drive, Dana, Whitley]
Also see how to make queries involving Database Information Schema.
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
|
|
