Functions in hibernate

Posted: April 11, 2012 in Hibernate

1.how 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

as

mycursor sys_refcursor;

begin

open mycursor for

select * from employee where eno>=no;

return mycursor;

end;

/ 

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)}

</sql-query>

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();

while(iterator.hasNext()){

Employee employee=(Employee)iterator.next();

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

}

session.close();

}

}

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s