How to get a result set in Java code from a stored procedure in plsql

Question

I use the MVC pattern. I have two tables: Employee and Address

let's say an employee is like

------------------- Id | Name | DeptId ------------------- 101 | Jake | 501 102 | Donald | 502 

and I have one department table for example

 ----------------------------- DeptId | Name | Description ----------------------------- 501 | IT | software assistance 502 | HR | Human resources 

Now when I use MVC, these tables map to classes as

 @Table(name="Employee") Class Employee{ @Id @Column(name="Id") private Long id; @Column(name="Name") private String name; @Column(name="DeptId") private Long deptId; @ManyToOne @JoinColumn(name="DeptId", referencedColumnName="id", insertable=false,updatable=false) private Department dept; //getters and setters go here } 

and another class (compared with the Department table)

 @Table(name="Department") Class Department{ @Id @Column(name="Id") private Long id; @Column(name="Name") private String name; @Column(name="Description") private String description; //getters and setters go here } 

note that the Employee class has a reference to an object of the Department class. These annotations @ManyToOne and @JoinColumn help us automatically get the corresponding department object along with the employee object

It’s easy with queries directly in the code, but how can this be done if I use only procedures or functions in my code? I tried different methods, but it doesn’t seem to help

Sometimes I get an error something like Cannot return resultset from a stored procedure in oracle 10g

Can anyone clarify. Also I have to use JNDI

Can I get the result from the procedure / function in such a way that it returns to me a List<Employee> (and not the original result set, which I myself must sort by objects). Should it be possible to use hibernate no?

thanks

Answer 1

Your PLSQL should return a ref cursor .

JPA 2.1 supports binding of CallableStatement cursors to Entities. See this answer .

Answer 2

I am writing a pseudo code (not a working example.) But it should solve your problem.

Oracle

 CREATE OR REPLACE PROCEDURE getEmployees(deptName varchar(20)) BEGIN SELECT * FROM Employee where DeptId in ( select DeptId from Department where Name =deptName ); END; / 

Hibernate

 Query query = session.createSQLQuery( "CALL GetStocks(:getEmployees)") .addEntity(Employee.class) .setParameter("deptName", "Production"); List result = query.list(); for(int i=0; i<result.size(); i++){ Employee emp = (Employee)result.get(i); System.out.println(emp.toString()); }