This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Stored proc insert/update/del in hibernate? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Stored proc insert/update/del in hibernate?" Watch "Stored proc insert/update/del in hibernate?" New topic
Author

Stored proc insert/update/del in hibernate?

Ben Ethridge
Ranch Hand

Joined: Jul 28, 2003
Posts: 108
Hi.

Similar to the lack of documentation I'm finding on stored proc queries in my other reply on this thread, I'm finding a bit of a lack of documentation on insert/update/delete in hibernate using straight sql calls and stored procedures here:

http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html

Skip down to section 16.4. There it discusses how to set up the mapping file and the stored proc (actually function again), but it doesn't explain how to set up the call in java.

Hopefully I'm just not looking in the right place, or it's something really obvious, and I can't see the forest for the trees... but I've looked through the above URL, plus the Hibernate In Action book and the Hibernate Quickly book and I can't find any examples of how to do this yet, or get my own to work.

Could someone perhaps point me to the example, or post a simple example for the updatePerson stored function in the above URL?

Ben
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

I think because it was someone elses thread that we didn't see it. Sorry.

Well, I will give you a better answer tomorrow when I am at work. But I believe in the named queries xml file, you post the stored procedure name and the parameters and map it to a Java class file that you have. The Java Class file should be mapped to either a database table or the ref cursor info that comes out of that first OUT parameter. If you have no out parameters then there is no need to map to a Java class, just write a named query in the mapping file. I will give you an example tomorrow.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

OK, I see something better.

For Oracle the following rules apply:

*

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type, see Oracle literature.

So for stored procedures in Oracle, they must be functions.

In their example in that section you pointed out it just returns the rowcount of the update, insert, or delete.

Have you rtied something like this.



Mark
Ben Ethridge
Ranch Hand

Joined: Jul 28, 2003
Posts: 108
Not yet, coz I don't understand what the java source code say, i.e. if the first parameter is an OUT and need to be a SYS_REF_CURSOR, what would the java-hibernate type be, and how would you send it as a parameter to the stored proc?

I tried ArrayList, using setParameter, but it didn't like that.

Ben
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Here is sample code calling a named query calling a procedure



That gets us the records.

Mark
Ben Ethridge
Ranch Hand

Joined: Jul 28, 2003
Posts: 108
Thanks. There's still one piece I don't see, however. Where's the SYS_REF_CURSOR variable?...or is the sample you are showing for a function, instead of a stored proc?

If it's a stored proc, wouldn't the first variable (i.e. the first setParameter() need to hold the SYS_REF_CURSOR? or am I missing something?

Ben
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2921
    
    5
Reading "between the lines" of the documentation Chapter 16. Native SQL - I'd go with the assumption that Hibernate treats.

procname( p0 OUT, p1 IN, p2 IN, ... )

as

p0 = procname( p1 IN, p2 IN, ... ).

Get a sample working with a stored function - then change the function to a procedure by adding the first OUT parameter to carry the return value - and get rid of the function return value. Based on any complaints you get you might need to tweak the mapping to get it to work. But I wouldn't be surprised if that first OUT parameter is simply modelled as the function return value in the Java code.
Ben Ethridge
Ranch Hand

Joined: Jul 28, 2003
Posts: 108
Yes, I made that assumption too. However, as I said above, this did not work when I tried to do such, using ArrayList as that first setParameter.

I'm looking for a simple working example for stored proc (as opposed to stored function), showing the java, the map file and the stored proc, if anyone has one, or can quicky create one.

Ben
Ben Ethridge
Ranch Hand

Joined: Jul 28, 2003
Posts: 108
No, wait. I think I see what you are saying. I got confused by your use of the word function at first. I'll try that and get back to the forum with the results...

Thanks,
Ben
Ben Ethridge
Ranch Hand

Joined: Jul 28, 2003
Posts: 108
We just dropped hibernate, so I won't be able to do this now. Apologies to anyone who was waiting with bated breath ...or is that baited breath
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Originally posted by Ben Ethridge:
We just dropped hibernate, so I won't be able to do this now. Apologies to anyone who was waiting with bated breath ...or is that baited breath


Why are you dropping Hibernate? What other ORM tool will you use?

Mark
Ben Ethridge
Ranch Hand

Joined: Jul 28, 2003
Posts: 108
Just Spring itself now, i.e. its StoredProcedure class, as shown in 11.4.4 here:

http://static.springframework.org/spring/docs/1.2.x/spring-reference.pdf

Ben
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored proc insert/update/del in hibernate?
 
Similar Threads
How to call function from hibernate??
getting data from 2 tables using 2 DTOs
Native SQL in Mapping File For a Property
hibernate stored procedures
Howto Map Complex Objects from PL/SQL routines without JDeveloper?