Using Hibernate within a java stored procedure

jpoley · **Joined:** Fri Oct 24, 2003 9:23 pm **Posts:** 10

Has anyone out there used Hibernate within a Java Stored Procedure?
ans was wondering if they could share any thoughts / comments they had on it.

It should work, but it may need some modifications (not sure) because the means of getting a connection is different from within the oracle JVM.

thanks in advance,
Jason

michael · **Posted:** Tue Jun 22, 2004 10:32 am

The next level of questions will surely be if you can get another Hibernate instance on the Server to call the Stored Procedure which is using Hibernate ... sorry just joking :)

I don't know if this can be done, depends on the compatibility of the Oracle VM. Connection aquiring can be changed by writing a custom ConnectionProvider. Anyways sounds like a really sick thing to me. Why would you ever want to do something like that?

jpoley · **Joined:** Fri Oct 24, 2003 9:23 pm **Posts:** 10

Have a web application that has to do something in batch, and its already using hibernate (many tables). I don't think using hibernate in app server for a batch job of 1 million rows is a good idea. And I don't want to duplicate all my business logic in PL/SQL just to do batch, so that is why i want java stored procedures.

I don't see any reason why it wouldn't work. (with some modification)

-Jason

christian · **Posted:** Tue Jun 22, 2004 11:32 am

The reason why it "won't work" is that Java stored procedures are very much a vendor specific database extension. Loading a stored procedure in Oracle, for example, is a lot of work. You have to map the input and output parameters to PL/SQL types, you have to write a wrapper in PL/SQL that hides your Java SP, and so on. Bascially, writing PL/SQL instead of Java is usually easier.

This is also not related to Hibernate, as you would call a normal stored procedure using JDBC, no matter if its actually a Java SP in the backend. ORM is not there yet (hey, something to do in the next 3 years).

jpoley · **Joined:** Fri Oct 24, 2003 9:23 pm **Posts:** 10

I understand that java stored procedures are very vendor specific, but so is PL/SQL. Its an oracle JVM, but still its a JVM.

I think it will work, (with a modified connection)

java stored proc would have a signature like this

public void doWork(long key, String name)

and doWork would use hibernate to read many tables and do the work in quesiton, and then write back to a few tables.

the O/R stuff os abstracted behind the doWork.

so what about that (other than getting the connection) wouldn't work?

please let me know?

do you still think this is a crazy idea?

-jason

michael · **Posted:** Tue Jun 22, 2004 11:44 am

Why do you get the impression that doing exactly the same thing in a java stored procedure would be any faster?

christian · **Posted:** Tue Jun 22, 2004 11:50 am

Well, its a good idea to integrate data and application language, thats what SQLJ tried to do and what many vendors tried with their (mostly very weird) Java integration in the DBMS.

I never wrote Java stored procedures; more than one year on an Oracle PL/SQL application was more than I could take. At this time, mostly the tools where a problem and some of the basic concepts are of course fundamentally flawed. Today it looks much better, at least from the outside. The concepts are still funny, but the practical side might actually work. In fact, I just had a look at DB2 and Oracle Java integration support this weekend and I plan to check it again.

What didn't look very promising was that Oracle is apparently deprecating SQLJ in the next years. I don't know how long they will support Java SP. Still have to read up on the type system support...

jpoley · **Joined:** Fri Oct 24, 2003 9:23 pm **Posts:** 10

because returing 1 million rows to the same machines memory is must faster than putting it in tcp/ip packets and sending it out over the network, and back.

jpoley · **Joined:** Fri Oct 24, 2003 9:23 pm **Posts:** 10

I don't think Java Stored Procedures are going anywhere anytime soon, as they are adding new features for using them in 10g. If anything i'd see them phasing out pl/sql in favor of JavaStoredProcs. Yes I admit its a a bit of a non typical java envt, and slightly awkware to use/load.

but what you gain is a stronger programming language for business logic. (not PL/SQL crap) every try refactoring a huge stored procedure?

I think this will work, and I am still convinced it could work well.

michael · **Posted:** Tue Jun 22, 2004 12:17 pm

Well just try it, I told you what you will probably have to do.

craigduncan · **Joined:** Tue Dec 09, 2003 8:31 am **Posts:** 17

I am curious if the original poster had any luck with this?

johnlon · **Joined:** Fri Sep 14, 2007 6:40 pm **Posts:** 5

Here is my scenario.

Building out a new physical model alongside an old db.
Two schema's inside same Oracle instance.

We are building a new Java tier on top of the new schema using spring/hibernate.

To maintain sync with the old db we are building a load of PLSQL code that will be called by PLSQL stored procs on the old schema.

====

Now it occurred to me that if I could put a copy of my java tier in the database itself then the trigger code on the old schema could call a java stored proc wrapper which in turn called into my java tier.

This way I avoid duplication business logic in the db sync and the java middle tier. Basically I do away with the PLSQL sync code.

This clearly has advantages from an automated testing point of view too.

So very interested in how/if anyone has made this work.

Potential issues...
- JVM version (outside the db we use Sun Java 5 - inside the db I'm not sure what is used).
- How would spring access it's config files?