How to get a resultset in java code, from a stored procedure in plsql

Question

I am using MVC pattern I have two tables : Employee and Address

say, Employee is like

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

and I have one Department table like

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

Now since I am using MVC these tables are mapped to classes like

@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 the other class Department (mapped to 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
    }

notice that Employee class has reference to an object of Department class. This @ManyToOne and @JoinColumn annotations helps us in automatically retrieving corresponding department object along with an employee object

Its easy with queries directly in code but how can this be done if I am to use only procedures or functions in my code I have tried different methods , but it doesn't seem to help

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

Can anyone please clarify. Also I have to use JNDI

Can I get my result from the procedure/function in a way that it returns me List<Employee> (not a raw resultset which I myself have to sort out into objects) . It should be possible using hibernate no ?

thanks

score 1 · Answer 1 · edited May 23 '17 at 12:07

1

Your PLSQL needs to return a ref cursor.

JPA 2.1 has support for mapping CallableStatement out cursors to Entities. See this answer.

edited May 23 '17 at 12:07

Community

1
1

answered Feb 23 '16 at 05:06

MarkOfHall

3,334
1
26
30

Nilesh Deshpande · Answer 2 · 2016-02-21T03:04:33.747

-1

I am writing pseudocode ( Not the 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());
}

edited Feb 21 '16 at 03:04

answered Feb 21 '16 at 02:45

Nilesh Deshpande

120
5

**Sorry ! I need to select Employees, not departments ! Each employee is mapped to a dept because of deptId column. I want corresponding object of Department in 'dept' field of employee. This is straightforward if using queries in java code, but not so easy in case of procedures I think** – Sarabjeet Feb 21 '16 at 02:48
Modified the pseudo code. You need to try that out – Nilesh Deshpande Feb 21 '16 at 03:04

How to get a resultset in java code, from a stored procedure in plsql

2 Answers2