EclipseLink JPA: Can I run multiple queries from one builder?

I have a method that builds and runs a Criteria query. The query does what I want it to, specifically it filters (and sorts) records based on user input.

Also, the query size is restricted to the number of records on the screen. This is important because the data table can be potentially very large.

However, if filters are applied, I want to count the number of records that would be returned if the query was not limited. So this means running two queries: one to fetch the records and then one to count the records that are in the overall set. It looks like this:

public List<Log> runQuery(TableQueryParameters tqp) {

    // get the builder, query, and root

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Log> query = builder.createQuery(Log.class);
    Root<Log> root = query.from(Log.class); 

    // build the requested filters

    Predicate filter = null;
    for (TableQueryParameters.FilterTerm ft : tqp.getFilterTerms()) {

       // this section runs trough the user input and constructs the 
       // predicate

    }
    if (filter != null) query.where(filter);

    // attach the requested ordering

    List<Order> orders = new ArrayList<Order>();
    for (TableQueryParameters.SortTerm st : tqp.getActiveSortTerms()) {

        // this section constructs the Order objects

    }
    if (!orders.isEmpty()) query.orderBy(orders);        

    // run the query

    TypedQuery<Log> typedQuery = em.createQuery(query);
    typedQuery.setFirstResult((int) tqp.getStartRecord());
    typedQuery.setMaxResults(tqp.getPageSize());
    List<Log> list = typedQuery.getResultList();

    // if we need the result size, fetch it now

    if (tqp.isNeedResultSize()) {
        CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
        countQuery.select(builder.count(countQuery.from(Log.class)));
        if (filter != null) countQuery.where(filter);
        tqp.setResultSize(em.createQuery(countQuery).getSingleResult().intValue());
    }

    return list;
}

As a result, I call createQuery twice on the same CriteriaBuilder and I share the Predicate object (filter) between both of them. When I run the second query, I sometimes get the following message:

Exception [EclipseLink-6089] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.excepti开发者_Go百科ons.QueryException Exception Description: The expression has not been initialized correctly. Only a single ExpressionBuilder should be used for a query. For parallel expressions, the query class must be provided to the ExpressionBuilder constructor, and the query's ExpressionBuilder must always be on the left side of the expression. Expression: [ Base com.myqwip.database.Log] Query: ReportQuery(referenceClass=Log ) at org.eclipse.persistence.exceptions.QueryException.noExpressionBuilderFound(QueryException.java:874) at org.eclipse.persistence.expressions.ExpressionBuilder.getDescriptor(ExpressionBuilder.java:195) at org.eclipse.persistence.internal.expressions.DataExpression.getMapping(DataExpression.java:214)

Can someone tell me why this error shows up intermittently, and what I should do to fix this?

Short answer to the question : Yes you can, but only sequentially.

In the method above, you start creating the first query, then start creating the second, the execute the second, then execute the first.

I had the exact same problem. I don't know why it's intermittent tough.

I other words, you start creating your first query, and before having finished it, you start creating and executing another.

Hibernate doesn't complain but eclipselink doesn't like it.

If you just start by the query count, execute it, and then create and execute the other query (what you've done by splitting it in 2 methods), eclipselink won't complain.

see https://issues.jboss.org/browse/SEAMSECURITY-91

It looks like this posting isn't going to draw much more response, so I will answer this in how I resolved it.

Ultimately I ended up breaking my runQuery() method into two methods: runQuery() that fetches the records and runQueryCount() that fetches the count of records without sort parameters. Each method has its own call to em.getCriteriaBuilder(). I have no idea what effect that has on the EntityManager, but the problem has not appeared since.

Also, the DAO object that has these methods used to be @ApplicationScoped. It now has no declared scope, so it is now constructed on demand from the various @RequestScoped and @ConversationScoped beans that use it. I don't know if this has any effect on the problem but since it has not appeared since I will use this as my code pattern from now on. Suggestions welcome.