tags:

views:

144

answers:

2

Hi,

If I have a complex where clause which varies only slightly between many queries, in SQL I would create a view based on most of the where clause, then query the view multiple times. Both for performance and maintainability.

I don't see an equivalent of that in jpql.

Am I or the jpql spec. missing something?

Cheers, Phil

A: 

In JPA2, the combination of EntityManager and CriteriaQuery is very powerful:

http://java.sun.com/javaee/6/docs/api/javax/persistence/criteria/CriteriaQuery.html

http://java.sun.com/javaee/6/docs/api/javax/persistence/EntityManager.html#createQuery(javax.persistence.criteria.CriteriaQuery%29

You define a basic CriteriaQuery (somewhat like a view) using the CriteriaBuilder and then use the entitymanager to create a query based on the criteriaquery. Since criteriaquery are objects, they are reuseable on the java side.

Possibly providers also implement caching against criteriaqueries, but I don't know about that.

seanizer
I'm basically thinking about how I can implement a x tab report with an overall filter. Each cell in the x tab report is the ANDing of two expressions, which vary per cell. And the overall filter which in the sql world would be used to create a view, from which the cell queries (assuming there will be more than one - one per cell) would be run against.I dont see how CriteraBuilder will help. It looks to me like I can build up a query using objects instead of strings to create a jpql statement. But surely the end result (no view and big generated SQL) will be the same?Cheers, Phil.
Phil
+1  A: 

Not a direct answer but why don't you map an Entity on an SQL view? In some cases, SQL views are the easiest and most effective solution, especially when the data are read-only. Just don't abuse them.

Pascal Thivent
Hi Pascal, thanks for your comment. If I do that then I'll have stepped out of jsql and into the database vendor specific world of sql. I want to maintain the database agnostic view onto my database layer, which for me, along with orm, is why I'm looking for a efficient jpql solution.Cheers, Phil.
Phil
@Phil Hmm... What makes you think you can't use JPQL if you have an Entity mapped on your view?
Pascal Thivent
Hi, the view is dynamic. generated at runtime, i.e not part of the schema. so I'm assuming that to map entity onto view, the view would need to exist when entity manager created. In my situation I want the view to be temporarily created and to exist for the duration of the report.
Phil