Native Stored Proc v/s Hibernate

Question

I am a newbie in Hibernate.

I am working on a cloud service data access layer.

Currently we are using Hibernate for OR mapping and as data access layer using Hibernate annotations. But lately i have been asked to implement Hibernate/Data Access layer in such a way that my stored procedures be in HQL and we can change our DB at a short notice and port our entire code.

The closest i can think in this regard is by using Named queries , where stored procedures are at DB side and my hibernate is resolving the stored procedure calls using named queries.

The reason for all that is the notion that since stored procedures are precompiled therefore they give good performance and security optimization for a large cloud service implementation.

currently i am using java , hibernate and Mysql.

Can anybody examine my assumptions and validate or give/suggest some better alternatives.

Performance and security are top priority.

Answer 1

I think the approach you outlined is great.

That is exactly what I would do if I were in your position. (I'm on Hibernate backed by MySql also, and have considered doing this if needed for performance reasons.)

Answer 2

Since parsing and optimizing statements is fast with the most DBMSes, I prefer not to use stored procedures if my application is the 'owner' of the catalogue(s).

With stored procedures, migration and maintainance can become more difficult which outweights the little tiny performance profits.

Cases, where I see the benefits of stored procedures:

• I'm not the owner of the database. Access to data is provided by database developers / maintainers (like you find in Datawarehouses often). So stored procedures are an interface to the data.
• Statements are complex and the runtime is unpredictable or should have no affects on my application (like triggering long running transactions or batches).

Hope, that'll help you with your decision.