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?