executing stored procedure from Spring-Hibernate using Annotations

Question

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.

Solution

That's because of the bug in the hibernate. I've modified the stored procedure to fetch all the columns and it worked well.