So I'm being using Hibernate in my last few projects with Stored Procedures for every call. I have to create:

<sql-query name="getSomething" callable="true">
<return class="MyClass">
<return-property name="id" column="id"/>
<return-property name="name" column="name"/>
{ call myproc(?,?,?) }
</sql-query>

and it's fine, but I'm only using about 10% of hibernate. I want the other 90%. I work at a no-raw-sql police state company so there is no getting away from stored procedures. The conversation usually goes like this:

Me: Lots and lots of other companies are using hibernate with raw-sql successfully. Why are they able to handle it but not us?

DBA: they must not care about their data the way we care about our data.

Me: But some of these companies are running very data intensive businesses.

DBA: if these other companies jumped off a bridge, would you jump too?

Me: But...

DBA: NO RAW SQL!

So, my question is, how feasible would it be to modify Hibernate to generate all the sql it's going to need into procs? That is, during developement I'd like to run Hibernate normally with raw-sql and then when I'm ready to move to production, run a script that will make all the procs I need and change all the mapping files to use procs. Has anyone tried this? Seems like the biggest problem would be updates and inserts. Those cannot be called from hibernate currently I believe...

hibernate can update and insert in hibernate 3.1

so this "No-raw-sql-police" will accept autogenerated sql as long as it is done in stored procedures ? weird ;)

anyhow, what you are talking about is doable in some sense; but noone have done it.

It would require enumerating all the generated sql and generate the apropriate CRUD for it, but it probably will not be optimal...

'no raw sql police' is no relational databases police
you can't work with relational databases without sql - you can do work easier with autogenerate sql (hibernate do it), but you can't do it without sql

Yes, their logic is this:

1) They can review the sql in stored procs before pushing them live so never will any sql they haven't looked at ever be run. i.e. they've gotten burned before from a bad select statement that did a table scan and blocked other requests.

2) They can monitor which user is running which proc. To monitor raw sql they'd have to turn logging on and degrade performance.

It's all about CYA. They don't want to get yelled at by the CTO when the DB goes down at 3:00 AM and we loose tons of money. The only way they can make sure the DB stays up 24/7 is to not allow raw sql.

Hey the fact that I can do updates in 3.1 and you think this is possible is great! I'll get the source to Hibernate and try this next time I have some free time. Thanks.

btw. if you enable full debug for the persisters you get all the prepared sql hibernate will use for any DML operation (insert,update, delete etc.)

Very good idea and if your needs are small that is your fastest approach.

Its not actually that difficult to write some utility to sweep through and generate all the additional stored procedures. I auto-generate all my hibernate mappings, create the dbase, then sweep through again and add triggers to all the tables for logging/history/external-updates. (Actually, only the latter right now, but the others are coming along with minor effort.)

The best way to do this is instantiate a Configuration and step through it. It contains the contents of the hibernate-mapping, although substantially transformed. What you need is there, but you'll have to learn as you go. You're supposed to use the SessionFactory, but that requires PITA nonstandard hacking to get what you need. Private attributes and no access methods where you need them.

BTW, another reason DBAs require sprocs instead of raw SQL is for security. Its a PITA for all, but they have some valid ponts.

Thanks. How would you recommend I handle the HQL specific stuff like:

Query q = s.createQuery("from foo in class Foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean has getName() and getSize()
List foos = q.list();

should I run a filter on the code to replace the call with:

Query q = s.getNamedQuery("newSPname");
q.setString(0, "test");
q.setString(1, "test etc");
List foos = q.list();

or actually change how Hibernate would execute the orignal code and make it call the SP behind the scenes?

I got version 3.1rc3 and tried:

Query q = s.getNamedQuery("addNewRow");
q.executeUpdate();

but I still get:

"java.lang.UnsupportedOperationException: Update queries only supported through HQL"

How do I call insert/update stored procs in 3.1?

Thanks.

via session.connection()

Ah, sorry, I mis-understood what the previous poster meant by "in 3.1 hibernate can handle updates." Actually, the session.connection method is how I'm doing updates now in 3.0.