java hibernate调用oracle存储过程,如何使用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 = "{? = call

my_function(:empName) }", resultClass = Employee.class, hints = {

@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })

对于Oracle存储过程

使用sys_refcursor第一个OUT参数创建过程

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||'%');

在实体类中将过程定义为

@javax.persistence.NamedNativeQuery(name = "getProc", query = "{ call

my_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();

谢谢

2020-06-20