How to use ScrollableResults for Hibernate Queries when joining many different entities

Question

I am using Spring Boot endpoints to return results from database queries. It works fine when using getResultList() on the TypedQuery. However I know I will have to managed very large data sets. I am looking into using ScrollableResults via hibernate but I cannot figure out how to actually reference the contents of each row.

StatelessSession session = ((Session) entityManager.getDelegate()).getSessionFactory().openStatelessSession();

    criteriaQuery.multiselect(selections);

    criteriaQuery.where(predicates.toArray(new Predicate[]{}));

    Query<?> query = session.createQuery(criteriaQuery);
    query.setMaxResults(5);
    query.setFetchSize(1000);
    query.setReadOnly(true);
    ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);

    while(results.next()){
        Object row = results.get();
    }
    results.close();
    session.close();

I have tried results.get(0), results.get(0)[0], results.getLong(0), Object[] row vs Object row, etc. With and without toString() on all of the options. Nothing I do gets more out of the row than the java object reference. I've tried casting as well and get a "cannot cast error". Sometimes I get an error, "query specifies a holder class". Not sure what that means because my criteria query is built by joining 1 or more entities where the entities and selected columns are not known before hand. So I am not actually specifying a class. They entities and selects are specified by user input. Any thoughts? Thanks!

UPDATE: I can do System.out.println(scroll.getType(0)); and in this case observe a long. But when I try to save that long (.getLong(0)) I get the error, "query specifies a holder class". Or again the cannot cast error.

score 1 · Accepted Answer · answered Nov 19 '20 at 20:13

Got it figured out. queryDetails is a CriteriaQuery<Tuple>

StatelessSession session = entityManagers.get("DatasourceName").unwrap(Session.class).getSessionFactory().openStatelessSession();
        
    

Stream<Tuple> resultStream = session.createQuery(queryDetails)
                .setReadOnly(true)
                .setMaxResults(100)
                .setFetchSize(1000)
                .setCacheable(false)
                .getResultStream();
        Iterator<Tuple> itr = resultStream.iterator();
while (itr.hasNext()){
                //Get the next row:
                Tuple row = itr.next();

}

score 0 · Answer 2 · answered Nov 05 '20 at 07:01

A CriteriaQuery that uses multiselect produces an Object[] or javax.persistence.Tuple as result type. Maybe you should try debugging to see the actual object type and from there you can work further.

If you are processing and returning all rows anyway, there is no need to use the ScrollableResults API as you will have to create objects for all rows anyway. If your use case is to do some kind of aggregation, I would recommend you use an aggregate function instead and let the database do the aggregation.

How to use ScrollableResults for Hibernate Queries when joining many different entities

2 Answers2