如何使用Hibernate（EntityManager）或JPA调用Oracle Function或Procedure

可以按以下方式使用EntityManager调用Oracle函数或存储过程。

对于Oracle功能

创建一个以sys_refcursor作为返回类型的函数

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;

在实体类中，将函数定义为

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

对于Oracle存储过程

使用

sys_refcursor

CREATE OR REPLACe PROCEDURE myProcedure(p_cursor out sys_refcursor,     p_val  in varchar2) ASBEGIN     OPEN o_cursor FOR          SELECT     emp_name   FROM     employees  WHERe     LOWER (emp_name) LIKE lower(p_val||'%');

在实体类中将过程定义为

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

最后在DAO类中调用函数或过程为

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

谢谢