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