java 使用本机 SQL Hibernate 从存储过程中检索值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17347409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving a value from Stored Procedure using Native SQL Hibernate
提问by user182944
Below is the stored procedure:
下面是存储过程：
create or replace procedure
proc_emp_name(v_emp out emp.emp_name%TYPE, v_empid in emp.emp_id%TYPE)
is
begin
select emp_name into v_emp from emp where emp_id = v_empid;
dbms_output.put_line('Emp Name: ' || v_emp);
dbms_output.put_line('Procedure created successfully!!!');
end;
I want to invoke this using Native SQL, followed this link but not sure how to retrieve the OUTparameter from the Procedure.
我想使用本机 SQL 调用它，遵循此链接，但不确定如何OUT从过程中检索参数。
http://www.mkyong.com/hibernate/how-to-call-store-procedure-in-hibernate/
http://www.mkyong.com/hibernate/how-to-call-store-procedure-in-hibernate/
Kindly let me know the simplest way to invoke the procedure and get the results out of it.
请让我知道调用该程序并从中获取结果的最简单方法。
EDIT
编辑
As suggested, checking the docs, I modified the Proc having first parameter as a SYS_REFCURSORas follows:
按照建议，检查文档，我修改了具有第一个参数的 ProcSYS_REFCURSOR如下：
create or replace procedure
proc_empname_refcursor(v_empname OUT SYS_REFCURSOR, v_deptid in emp.emp_id%type)
is
begin
open v_empname for select * from dept where dept_id = v_deptid;
end;
I am able to invoke it using NamedQuerybut I don't want to add anything in the mapping files because of some other restrictions. I tried the below code for invoking the proc without using NamedQuerybut it did not worked out:
我可以使用它来调用它，NamedQuery但由于其他一些限制，我不想在映射文件中添加任何内容。我尝试了以下代码来调用 proc 而不使用，NamedQuery但没有成功：
Query query = session.createSQLQuery(
"CALL proc_empname_refcursor(?, :deptId)")
.addEntity(Dept.class)
.setParameter("deptId", new Integer(2));
List<Dept> departments = query.list();
for(int i=0; i<departments.size(); i++){
Dept department = (Dept)departments.get(i);
System.out.println("Dept Id: " + department.getDeptId());
System.out.println("Dept Name: " + department.getDeptName());
}
I am getting the exception:
我收到异常：
org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [] [CALL proc_empname_refcursor(?, :deptId)]
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:319)
at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:201)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:145)
at com.jdbc.HibernateStartup.main(HibernateStartup.java:70)
Kindly let me know how to resolve this.
请让我知道如何解决这个问题。
回答by vincentks
I've managed to get an out parameter from a stored procedure using the following code in Hibernate and MS SQL Server:
我已经设法在 Hibernate 和 MS SQL Server 中使用以下代码从存储过程中获取输出参数：
@Override
public Serializable generate(SessionImplementor session, Object object) throws HibernateException {
Connection connection = session.connection();
CallableStatement callable = null;
try {
callable = connection.prepareCall("execute [procedure] ?");
callable.registerOutParameter(1, Types.INTEGER);
callable.execute();
int id = callable.getInt(1);
return id;
} catch (SQLException e) {
(...)
} finally {
(...)
}
}
回答by user1573133
From Hibernate Docs:
从休眠文档：
You cannot use stored procedures with Hibernate unless you follow some procedure/function rules.
除非您遵循某些过程/函数规则，否则您不能在 Hibernate 中使用存储过程。
For Oracle, A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set.
对于 Oracle，一个函数必须返回一个结果集。过程的第一个参数必须是返回结果集的 OUT。
