Hibernate and Stored Procedure

[Alec Lee]

I'm learning Oracle PLSQL in order to write stored procedure to be called by JDBC. I know nothing about Hibernate and want to know if I use Hibernate in future project, will my stored procedure knowledge still useful?

I know about entity bean. Here we dont even need to write SQL (if cmp used) not to mention stored procedure. I am just thinking how to invest my time and effort in learning PLSQL and evaluate how it would be used.

Thanks for any information.

[Paul Sturrock]

PL/SQL is a useful skill to have regardless of whether you are going to use Hibernate or not. Hibernate 3 allows calling of Stored Procedures. Of course using them kind of invalidates one of the reasons you would use an ORm in the first place: that your code can be completely database independent. Ask yourself why you need stored procedures - if there is no compelling reason it is safer (in terms of portability and maintainability) not to use them.
[ June 07, 2005: Message edited by: Paul Sturrock ]

[Alec Lee]

Hi Paul,

The reason I use stored procedure is for performance. Currently, I am using JDBC calls to issue SQLs to Oracle. But this sometimes means many round trips of network calls for a single persistence logic (e.g. I have a table representing a tree, in order to select a node and some of its parent nodes, i need to issue many SELECT's). So, encapsulating a single PERSISTENCE logic in a single stored procedure call would improve the performance.

I appreciate the database independent benefits of O/R mapping but my bad experience with entity bean (performance) gives me a sense that O/R mapping => sacrificing performance.

This is why I want to clarify if Hibernate or O/R mapping can completely eliminates the need of stored procedure (hence the efforts to learn them).

Appreciate any further comment.

[Paul Sturrock]

Performance is usually the reason SP's appear in an application. After all, all ORM solutions are compromises, so will be slower than straight JDBC. Entity Beans are not a good example of ORM technologies - and their failings are well-know.

This is why I want to clarify if Hibernate or O/R mapping can completely eliminates the need of stored procedure (hence the efforts to learn them).

You will only know if Hibernate is performant enough for your needs if you implement your application with it and do some testing, no one can really answer that for you. What I can say is Hibernate is performant enough for the applications I have use it on. Learning Hibernate would not be wasted effort either, since it is becoming pretty ubiquitous.

[Andy Bentley]

I have been on projects where the "powers that be" demanded all sql be in PL/SQL. The stated reason was performance. While it is true that well written stored proceedures will out perform well written dynamic sql on the database server, the JDBC call CallableStatement which is used to call stored proceedures is much slower than PreparedStatement which is used within ORM's.

The performance hit taken by the ORM layer is about equal to the performance hit by the very slow "CallableStatement".

In my experiience, the real motivator for moving SQL into PL/SQL is that the "powers that be" in those shops are DB guys who want/need control of the project in thier realm.