EJB3.0 and Stored Procedure

[vignesh kumar]

What is the advantage of using Stored Procedure over EJB3.0?.We are calling Stored procedure using Native Query method.In EJB3.0 Framework already having persistent mechanism over tables like em.persist(obj),em.merge(obj),etc.I am concerned that by using native query method bypassing the persistence support provided by the EJB3 framework.So What is the different between native queries and Persistent mechanism?Can any one please elobrate.

[Chandrasekhar Elindram]

Sometimes there are no choices but to use Stored procedures to leverage the benifits provided by the database vendors. In certain cases it is worth keeping the business logic in the stored procedures, on top of that they are faster(precompiled), secure and maintainable.

However, I think it may not be good idea making calls to stored procedures or writing native queries from ORM based entities. We had JDBC based DAOs with native SQLs and stored procedures calls, but not in the JPA based Entities. Not having native queries in JPA(any ORM) entities will guarantee your protability between databases.

Can someone elaborate more on this, with some practical pros & cons?

[Uli Hofstoetter]

I guess it's time for the often used answer "it depends".

I had a customer with various applications based on different technologies (Powerbuilder, Java, Crystal Enterprise, ...) where some of the business logic was kept in stored procedures.

[Jerwin Louise Uy]

Native Queries will take advantage of any vendor specific implementations.

As such, it is preferred for very large queries but not for transactional functions because JPA provider is not aware of state changes and may result to inconsistent state.

Regards.