Functions in hibernate

Posted: April 11, 2012 in Hibernate to create Function in sql and how do we call this using hibernate?

procedure doesnt return any value But  function return a value.

Step 1:         

Here we are Querying on employee table.

SQL> create or replace function emp_function(no in number)return sys_refcursor


mycursor sys_refcursor;


open mycursor for

select * from employee where eno>=no;

return mycursor;



Step 2:

in mapping.xml file after </class> tag (employee.hbm.xml)

<sql-query name=”ram” callable=”true”>

<return class=”org.shal.Employee”></return>

{? = call emp_function(:p1)}


Step 3:

in main class:

public class CallProcedure {

public static void main(String[] args)throws Exception {

Session session=new Configuration().configure().buildSessionFactory().openSession();

Query query=session.getNamedQuery(“ram”); —–> ram is the name we have given for the sql-query

query.setInteger(“p1″,4); —> here we are passing number of the employee

List list=query.list();

Iterator iterator=list.iterator();


Employee employee=(Employee);

System.out.println(employee.getEno()+” Name is “+employee.getEname()+” Addr is “+employee.getEaddr());






Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s