Since this has not been resolved yet with Vaadin 7.1.5 (and 2013 being almost over!!), I tried a hack to solve this by getting the EntityManager to preload the batch of entities just before the JPAContainer looks for them.
It was tricky, but I found that the EntityProvider’s “doGetEntityCount()” method is invoked just before the N queries begin. The result from this method is cached if your JPAContainer uses CachingLocalEntityProvider which means that doGetEntityCount() is only actually invoked once per “table refresh”.
So what I did was intercept this method to add some code to get the EntityManager to preload the entities, which then happen to be in the cache later when the JPAContainer looks for them and thus the “1 query per entity” is avoided.
This works by extending CachingLocalEntityProvider, like this:
public class MyCachingLocalEntityProvider extends CachingLocalEntityProvider
{
@Override
protected int doGetEntityCount(EntityContainer container, Container.Filter filter)
{
try
{
String entityIdPropertyName = getEntityClassMetadata()
.getIdentifierProperty().getName();
CriteriaBuilder cb = doGetEntityManager().getCriteriaBuilder();
CriteriaQuery<Long> query = cb.createQuery(getEntityClassMetadata().getMappedClass());
Root root = query.from(getEntityClassMetadata().getMappedClass());
List<Predicate> predicates = new ArrayList<Predicate>();
if (filter != null)
{
predicates.add(FilterConverter.convertFilter(filter, cb, root));
}
if (!predicates.isEmpty())
{
query.where(CollectionUtil.toArray(Predicate.class, predicates));
}
int prefetchSize = 150;
TypedQuery<Long> tq = doGetEntityManager().createQuery(query).setMaxResults(prefetchSize);
List resultList = tq.getResultList();
}
catch (Exception e)
{
e.printStackTrace(); //just to be sure no disruption is caused
}
return super.doGetEntityCount(container, filter);
}
}
To use this special provider you need to create your JPAContainer manually, like:
EntityManager em = .... //get it from the Factory.
container = new JPAContainer(clazz);
container.setEntityProvider(new MyCachingLocalEntityProvider(clazz, em));
For me this solved the “N+1” queries caused by the way the algorithm of fetching IDs first and then the row data.
I had an iadditional issue of N+1 queries and EclipseLink, but it was due to eager loading of @OneToOne relationships. If you run into it, make sure to mark the non owning side of the relationship as Lazy fetched:
@OneToOne(mappedBy = "fieldName",fetch = FetchType.LAZY ) @BatchFetch(BatchFetchType.IN)
… it doesnt hurt to add @BatchFetch(BatchFetchType.IN) to the owning side too.
Anyway, I tried this with Vaadin 7.1.5 and JPAContainer 3.1.0 and it worked like a charm, now I only get 3 queries total for my JPAContainer-powered Table.
Yet, even for the caching version it generates 1 select per row until the cache is filled up. However, in the real world, the cache doesn’t stay populated for very long, since the CachingSupport behind CachingLocalProvider is implemented so that the complete cache is invalidated once a single entity is updated/added/removed