I'm looking for information regarding the usage of stored procedures instead and/or within the persistence framework. The application has many complex stored procedures, I mean, each one has between 10 and 20 complex queries with 4 to 8 joined tables, and we don't like to move all that code to the application server and coded in Java.
So far I'm almost decided to go the way of a code generator, for example sql2java. I don't see the benefits of using persistence, since our application is in-house and we don't need to support different database products, and even if we change brand, we could still rewrite the stored procedures and it would still be easier than using persistence. Our developers know well how to do complex SPs and have almost no experience with persistence. I'm mostly sure scalability is not an issue also. (I'm pointing issues I've seen as important in this respect in other forums and articles across the web.)
Before going further, I decided to ask in this forum for advice supporting or against that way of doing the work. In case it matters, we are planning to use Netbeans 5.5 with Visual Web Pack against a SQL Server database.

Thanks in advance !!!

Daniel

There is no reason you cannot call SP's via JDBC directly or using the Native SQL interfaces and features. See http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html#sp_query
as a start.

There are two possible ways we would like to do our work based on stored procedures.
One is with a tool that, atarting with an already implemented database, can create the stored procedures to do the CRUD operations generating them to a sql script file, and all the entity classes needed to persist the objects should use those stored procedures. I acknowledge this would mean for the generation process to understand all the different vendors' propietary syntax for SPs so I don't ask that much, but it would be pretty.
The other is, given the database with all the CRUD stored procedures already implemented, the tool should retrieve the tables and associated stored procedures information and generate the entity classes from that.
That is what I could get from sql2java, provided that I change the templates replacing the SQL statements for procedure calls, and that I name my stored procedures in a consistent way using the base table name and some preffixes or suffixes.
In Netbeans I can easily generate Entity Classes from the database with the already included TopLink framework, or installing Hibernate, but they generate SQL statements.
I see in the link you provided, section 16.3, that I can customize the SQL for generating the CRUD operations. That would solve half my concerns and would be very pretty imho.
So I have another question, do I need to manually map each and every other (non CRUD) stored procedure manually, or is there any way of automagically retrieving them from the database ??

Thanks for your help !!!

Daniel