Close

JPA - Calling Stored Procedures

[Updated: Sep 10, 2018, Created: Sep 10, 2018]

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

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

EntityManager#createStoredProcedureQuery() Example Select All Download
  • jpa-create-stored-procedure-example
    • src
      • main
        • java
          • com
            • logicbig
              • common
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also