How to call Oracle Function or Procedure using Hibernate (EntityManager) or JPA

by

Oracle function or a stored procedure can be called using EntityManager in the following manner.

For Oracle Function

Create a function with sys_refcursor as return type

CREATE OR REPLACE FUNCTION my_function
(p_val IN varchar2)
    RETURN SYS_REFCURSOR
  AS
    my_cursor SYS_REFCURSOR;
  BEGIN
    OPEN my_cursor FOR SELECT emp_name FROM employees
    WHERE lower(emp_name) like lower(p_val||'%');
    RETURN my_cursor;    
  END;

In Entity class, define function as

@javax.persistence.NamedNativeQuery(name = "getFunc", query = "{? =  call
my_function(:empName) }", resultClass = Employee.class, hints = {
@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })

For Oracle Stored Procedure

Create procedure with sys_refcursor as first OUT parameter

CREATE OR REPLACE PROCEDURE myProcedure(p_cursor out sys_refcursor,
     p_val  in varchar2
)
 AS
BEGIN
     OPEN o_cursor FOR
          SELECT     emp_name 
             FROM     employees 
            WHERE     LOWER (emp_name) LIKE lower(p_val||'%');

In Entity class define procedure as

@javax.persistence.NamedNativeQuery(name = "getProc", query = "{ call
my_procedure(?,:empName) }", resultClass = Employee.class, hints = {
@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })

and finally in DAO class, call function or procedure as

Query query = entityManager.createNamedQuery("getFunc"); // if procedure then getProc 
query.setParameter("empName","smith"); 
query.getResultList();

Thanks

More Related Contents:

  1. having an exception when saving oracle type object in some entity
  2. Hibernate native query – char(3) column
  3. Mapping PostgreSQL JSON column to a Hibernate entity property
  4. Error creating bean with name ‘entityManagerFactory’ defined in class path resource : Invocation of init method failed
  5. How to choose the id generation strategy when using JPA and Hibernate
  6. JPQL Create new Object In Select Statement – avoid or embrace?
  7. Spring Boot + JPA : Column name annotation ignored
  8. Hibernate, @SequenceGenerator and allocationSize
  9. What is the difference between Unidirectional and Bidirectional JPA and Hibernate associations?
  10. JPA/Hibernate Native Queries do not recognize Parameters
  11. What is the easiest way to ignore a JPA field during persistence?
  12. Default fetch type for one-to-one, many-to-one and one-to-many in Hibernate
  13. @Transactional method called from another method doesn’t obtain a transaction
  14. @PreUpdate and @Prepersist in hibernate/JPA (using session)
  15. Hibernate bidirectional @ManyToOne, updating the not owning side not working
  16. JPA how to make composite Foreign Key part of composite Primary Key
  17. Spring boot JPA insert in TABLE with uppercase name with Hibernate
  18. Getting [SQLITE_BUSY] database file is locked with select statements
  19. Silently ignored remove()
  20. Hibernate/JPA DB Schema Generation Best Practices
  21. How to set a default query timeout with JPA and Hibernate?
  22. Change database schema during runtime based on logged in user
  23. Hibernate: “Field ‘id’ doesn’t have a default value”
  24. How to map sql native query result into DTO in spring jpa repository?
  25. Hibernate Criteria for Dates
  26. Persisting UUID in PostgreSQL using JPA
  27. Batch inserts with JPA/EJB3
  28. How to force Hibernate to return dates as java.util.Date instead of Timestamp?
  29. Spring, JPA, and Hibernate – how to increment a counter without concurrency issues
  30. How to map one class to different tables using hibernate/jpa annotations

Leave a Comment