Spring JdbcTemplate equivalent in Seam?

[gonorrhea]

JPA 1.0 and 2.0 do not support stored procedures.  Hibernate does.

In a Seam 2.x app, if you are using JPA with Hibernate as your persistence provider and need to execute stored procedures (either legacy sprocs or due to security reasons), you must use JDBC.

Spring has a powerful and easy-to-use JDBC API.  The JdbcTemplate is the central class in Spring's JDBC core package.

In my sproc DAO class, I have a lot of boilerplate JDBC code that would be avoided if I had access to a Seam JDBC API similar to what Spring's JdbcTemplate and related support classes offer.

Is there a Seam equivalent to Spring's JDBC API and if not, are there plans to add this to Seam 3?

Otherwise, is it somehow possible to port this to Seam 2.x via Spring beans integration, etc.?

[swd847]

What is wrong with native queries? Also hibernate lets you map the results of stored procedures to entities, this should still work even if you are using JPA (it just locks you in to using hibernate annotation extensions).

[luxspes]

Stuart Douglas wrote on Jun 18, 2009 07:32:


What is wrong with native queries?

AFAIK no support for stored procedures in native queries...

Also hibernate lets you map the results of stored procedures to entities, this should still work even if you are using JPA (it just locks you in to using hibernate annotation extensions).

And also forces you to create stored procedures in a particular way, the problem is that generally when you want to access stored procedures it is because they were created a lot before your app, and you do not have a chance to alter them to fit your needs.

[luxspes]

To use stored procedures with Hibernate the procedures/functions have to follow some rules. If they do not follow those rules they are not usable with Hibernate. Read the rules here.

[gonorrhea]

Stuart Douglas wrote on Jun 18, 2009 07:32:


What is wrong with native queries? Also hibernate lets you map the results of stored procedures to entities, this should still work even if you are using JPA (it just locks you in to using hibernate annotation extensions).

1) re-usability (although if you use a DAO layer like I am, then this is not a problem anymore)

2) if you have reports writers (i.e. resources other than Seam developer) writing the queries, then it ends up being a stored proc usually.  we have a datawarehouse that we use in our Seam apps for read only and a local db for the CRUD operations.

3) isn't performance better using straight JDBC with sprocs?  I know that you don't have 1st or 2nd level cache (and actually in these cases, the 2nd level cache may be useful b/c it's read-only operations) with sprocs, but sql pre-compiled on the db server is tough to beat.

4) we use four-part naming convention for cross-server spanning queries (sometimes, yes performance may be slower).  I tried to do this with regular JPA query (not native queries) and it doesn't support more than one db per EntityManager and JBoss recommended a product 'metamatrix' which is not free.

[gonorrhea]

I did not know about those rules (isn't covered in JPA/hibernate book AFAIK).  Anyways, those rules don't seem too common and/or constraining...

[luxspes]

Arbi Sookazian wrote on Jun 18, 2009 18:08:



3) isn't performance better using straight JDBC with sprocs?  I know that you don't have 1st or 2nd level cache (and actually in these cases, the 2nd level cache may be useful b/c it's read-only operations) with sprocs, but sql pre-compiled on the db server is tough to beat.

It depends... sometimes it is, sometimes it isn't. Depending on the situation, cache can be really helpful fore read-only stuff (on the other hand due to limitations on server side programming, caching is not as effective as it could be).

For writing, the main advantage comes when batch processing, because data does not need to be loaded outside of the database to be modified interactively by a user... but when the data needs to be modified interactively by a user, in my experience, JPA gives a better performance than JDBC.

Of course, there is a problem when you need to execute the same business process algorithm, sometimes interatively, and some times as a batch (for example if you have some people capturing really old information from paper, and also have a process that read that read newer information from excel files). In those cases you generally end-up with a suboptimal solution (ORMs have bad performace for batching, stored procedures are bad for caching are their languages are very limited). That is why I think something like Dataphor or Rel could revolutionize the way we specify business rules and processes by offering a true relational programming model.

4) we use four-part naming convention for cross-server spanning queries (sometimes, yes performance may be slower).  I tried to do this with regular JPA query (not native queries) and it doesn't support more than one db per EntityManager and JBoss recommended a product 'metamatrix' which is not free.

AFAIK metamatrix was not opensource, but an opensource project was going to be created based on it... if I find the link for it I will post it here

[luxspes]

Arbi Sookazian wrote on Jun 18, 2009 18:08:



4) we use four-part naming convention for cross-server spanning queries (sometimes, yes performance may be slower).  I tried to do this with regular JPA query (not native queries) and it doesn't support more than one db per EntityManager and JBoss recommended a product 'metamatrix' which is not free.

The name of the (AFAIK free and opensource project) for that is now JBoss Teiid

[gonorrhea]

Francisco Peredo wrote on Jun 18, 2009 19:34:


The name of the (AFAIK free and opensource project) for that is now JBoss Teiid

Spring Tools Suite is free (JBDS should be as well, the whole world is suffering economically!)

I did read about Teiid, that frog icon thing.  What a horrible name.  JBoss really needs to get their act together in terms of marketing.  that's horrible. 

and what about Tattletale?  come on...

[luxspes]

Arbi Sookazian wrote on Jun 18, 2009 20:15:

Francisco Peredo wrote on Jun 18, 2009 19:34:


The name of the (AFAIK free and opensource project) for that is now JBoss Teiid

Spring Tools Suite is free (JBDS should be as well, the whole world is suffering economically!)

I see not advantage in making JBDS free, JBossTools is already free, and it is the same thing.

I did read about Teiid, that frog icon thing.  What a horrible name.  JBoss really needs to get their act together in terms of marketing.  that's horrible. 

Oh, come on, who cares about the name... as long as it works... (also, weird names are good, because Google loves weird names, Seam or Spring, both have names that make it easy to confuse them with non Java related stuff... even Java can be confused with a brown hot beverage)

and what about Tattletale?  come on...

[luxspes]

Ha ha, I didn't know Teiid is: an animal of the large family of mainly tropical American lizards characterized by a long forked tongue and the absence of bony plates beneath the scales.

Guess it is not such a good name, it already has 3,120,000 hits on google ;) .