I'm trying to execute a simple stored procedure with Spring/Hibernate using Annotations.
Here are my code snippets:
DAO class:
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.
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.
