Seam + stored procs: what's the recommendation?

[asookazian]

Just wondering what the official Seam recommendation/stance is regarding using sprocs with Seam web apps?

Other than being tied down to a particular DB vendor due to differences in the SQL syntax, we are aware of this problem.

I've read Gavin King and Martin Fowler stating a good reason to use sprocs is for maximum performance. Assume that this is not an issue for us.

I've done this with a simple use case recently and found resultsets cumbersome to use (dataTables and looping, etc) esp. with dynamic columns. Also, you can't use the Hibernation Validator annotations on the getter methods of the entity bean class (and in JSF using s:validate or s:validateAll tags). Any other Seam/JSF/EJB3 advantages we'd lose out on? Yes, I know about caching and lazy-loading.

The three Seam books I have did not mention sprocs or resultsets at all, as I recall.

So is it basically recommended to convert legacy sprocs to entity beans or POJOs? Not to create new sprocs at all for Seam app use?

And obviously, thanks to a reply on here, we learned it's best to use DataSource (connection-pooled) rather than DriverManager to get DB connections to minimize overhead.

thx.

[pmuir]

Have you read http://www.hibernate.org/hib_docs/reference/en/html/querysql.html?

[asookazian]

If you're referring specifically to section 16.2.2. Using stored procedures for querying, that looks interesting.

Since we're using JPA and not Hibernate 3 (Hibernate as the persistence provider), and as I understand, JPA is a subset of Hibernate API's, I'm not sure if this would even be an option for us.

But hypothetically if it is, will it only work with one return class, in this case Employment? What if the sproc resultset returns columns from a join (n tables)? I can see this being possible, but not if the resultset is a dynamic list of columns...

It looks like this technique will call setter methods on the Employment, and thereafter you can reference/use the Employment class rather than the resultset.