Close

JPA - Using @NamedStoredProcedureQuery to call database stored procedures

[Last Updated: Sep 19, 2018]

In JPA, @NamedStoredProcedureQuery can be used on entities to declare reuseable database stored procedures.

@NamedStoredProcedureQuery definition snippet:

package javax.persistence;
 .....
@Repeatable(NamedStoredProcedureQueries.class)
@Target({TYPE}) 
@Retention(RUNTIME)
public @interface NamedStoredProcedureQuery { 
    String name();//name used for referencing
    String procedureName();//The database stored procedure name
    StoredProcedureParameter[] parameters() default {}; // parameter information
    Class[] resultClasses() default {}; //result set classes
    String[] resultSetMappings() default {};//result set mappings
    QueryHint[] hints() default {};//query properties and hints
}

StoredProcedureParameter definition snippet:

package javax.persistence; 
 ......
@Target({}) 
@Retention(RUNTIME)
public @interface StoredProcedureParameter { 
    String name() default ""; //parameter name as defined in the database procedure
    ParameterMode mode() default ParameterMode.IN; //one of IN, INOUT, OUT, or REF_CURSOR parameters.
    Class type();//parameter type
}

Following method of EntityManager is used to create StoredProcedureQuery instance for a named query.

public StoredProcedureQuery createNamedStoredProcedureQuery(String name);

Example

In this example we are using Oracle database. Check out this tutorial to know how to setup Oracle database and call stored procedures using EntityManager.createStoredProcedureQuery().

Stored procedures

Run following script in Oracle SQL Developer:

src/main/resources/db.sql

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

JPA entity

@NamedStoredProcedureQuery(
      name = Person.NamedQuery_MoveToHistory,
      procedureName = "MOVE_TO_HISTORY",
      parameters = {
              @StoredProcedureParameter(name = "person_id_in", type = Long.class, mode = ParameterMode.IN),
              @StoredProcedureParameter(name = "msg_out", type = String.class, mode = ParameterMode.OUT),
      }
)
@NamedStoredProcedureQuery(
      name = Person.NamedQuery_FetchFromHistory,
      procedureName = "FETCH_PERSON_HISTORY",
      resultClasses = {Person.class},
      parameters = {
              @StoredProcedureParameter(name = "history_cursor", type = void.class,
                      mode = ParameterMode.REF_CURSOR)
      }
)
@Entity
public class Person {
  public static final String NamedQuery_MoveToHistory = "moveToHistory";
  public static final String NamedQuery_FetchFromHistory = "fetchFromHistory";
  @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;
    .............
}

Main class

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.createNamedStoredProcedureQuery(Person.NamedQuery_FetchFromHistory);
      procedureQuery.execute();
      @SuppressWarnings("unchecked")
      List<Person> resultList = procedureQuery.getResultList();
      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
              .createNamedStoredProcedureQuery(Person.NamedQuery_MoveToHistory);
      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("-- all 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();
  }
}
-- all Person entities -  --
Person{id=76, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Person{id=77, firstName='Robin', lastName='Cash', address='64 Zella Park'}
Person{id=78, firstName='Chary', lastName='Mess', address='112 Yellow Hill'}
Person{id=79, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}
Person{id=80, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}
-- Moving person to history table name: Dana --
Out msg= Person with id: 76 moved to History table. Update count: 1
-- Moving person to history table name: Mike --
Out msg= Person with id: 80 moved to History table. Update count: 1
-- Fetching person History --
Person{id=76, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Person{id=80, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}

Example Project

Dependencies 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

@NamedStoredProcedureQuery Example Select All Download
  • jpa-named-stored-procedure-query
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • Person.java
          • resources
            • META-INF

    See Also