java 使用注释从 Spring-Hibernate 执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2943497/
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
executing stored procedure from Spring-Hibernate using Annotations
提问by jai
I'm trying to execute a simple stored procedure with Spring/Hibernate using Annotations. Here are my code snippets:
我正在尝试使用注释通过 Spring/Hibernate 执行一个简单的存储过程。这是我的代码片段：
DAO class:
DAO类：
public class UserDAO extends HibernateDaoSupport {
public List selectUsers(final String eid){
return (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws
HibernateException, SQLException
{
Query q = session.getNamedQuery("SP_APPL_USER");
System.out.println(q);
q.setString("eid", eid);
return q.list();
}
});
}
}
my entity class:
我的实体类：
@Entity
@Table(name = "APPL_USER")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorFormula(value = "SUBSCRIBER_IND")
@DiscriminatorValue("N")
@NamedQuery(name = "req.all", query = "select n from Requestor n")
@org.hibernate.annotations.NamedNativeQuery(name = "SP_APPL_USER",
query = "call SP_APPL_USER(?, :eid)", callable = true, readOnly = true, resultClass = Requestor.class)
public class Requestor {
@Id
@Column(name = "EMPL_ID")
public String getEmpid() {
return empid;
}
public void setEmpid(String empid) {
this.empid = empid;
}
@Column(name = "EMPL_FRST_NM")
public String getFirstname() {
return firstname;
}
...
}
public class Test {
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"applicationContext.xml");
APFUser user = (APFUser)ctx.getBean("apfUser");
List selectUsers = user.getUserDAO().selectUsers("EMP456");
System.out.println(selectUsers);
}
}
and the stored procedure:
和存储过程：
create or replace PROCEDURE SP_APPL_USER (p_cursor out sys_refcursor, eid in varchar2)
as
empId varchar2(8);
fname varchar2(50);
lname varchar2(50);
begin
empId := null;
fname := null;
lname := null;
open p_cursor for
select l.EMPL_ID, l.EMPL_FRST_NM, l.EMPL_LST_NM
into empId, fname, lname
from APPL_USER l
where l.EMPL_ID = eid;
end;
If i enter invalid EID, its returning empty list which is OK.
如果我输入无效的 EID，它返回的空列表是可以的。
But when record is there, following exception is thrown:
但是当记录存在时，会抛出以下异常：
Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [call SP_APPL_USER(?, ?)]; nested exception is java.sql.SQLException: Invalid column name
Do I need to modify the entity(Requestor.class) ? How will the REFCURSOR be converted to the List? The stored procedure is expected to return more than one record.
我需要修改实体(Requestor.class) 吗？REFCURSOR 将如何转换为 List？预计存储过程将返回多条记录。
