The database is often the bottleneck in an enterprise application, and to achieve good performance it's essential to tune the persistence layer and minimize the number of calls to the database.

--Chris Richardson, POJO's in Action

Scenario:

Assume that all relationships are LAZY fetch type.

A SFSB JPA query that results in a single entity in the result list that is joined to multiple entities in the query.

In the xhtml dataTable, additional queries are dynamically executed when EL path navigation is used to display other related entities' fields.

Example:

<h:outputText value="#{foobar.customer.fname}"/>

To optimize the performance, it is suggested to use a fetch join which is prefetched by the query engine and are not lazy loaded later at runtime.  Otherwise, there will be multiple additional queries when the JSF is rendered (and thus multiple additional RT's to DB).

The limitation with this strategy is that a query can only use a fetch join on a single collection and in this case there are multiple collections (multiple 1:m relationships b/n foobar entity and the others - foobar table has 4 foreign keys).

The other option is to retrieve all the required entities in the SFSB/DAO JPA query as a result list with an Object array.  The problem with that is you then have to reference the var value of the dataTable by hard-coded indexes as follows in the JSF EL:

<h:outputText value="#{list[0].fname}"/>

So what is the best solution here for optimizing the performance of this use case?

I'd like to stick with JPA only rather than Hibernate specific API if possible.  JDO default and custom fetch groups are interesting but not sure if JPA/Hibernate supports that.  thx.