How to read all records from table(> 10 million records) and serve each record as chunk response

[Sivakumar Raja]

I try to fetch record from MySQL database using hibernate's scrollable result and with reference from this github project, i tried to send each record as chunk response.

Controller:

@Transactional(readOnly=true)
public Result fetchAll() {
    try {
        final Iterator<String> sourceIterator = Summary.fetchAll();
        response().setHeader("Content-disposition", "attachment; filename=Summary.csv");

        Source<String, ?> s =  Source.from(() -> sourceIterator);
        return ok().chunked(s.via(Flow.of(String.class).map(i -> ByteString.fromString(i+"\n")))).as(Http.MimeTypes.TEXT);

    } catch (Exception e) {
        return badRequest(e.getMessage());
    }
}

Service:

public static Iterator<String> fetchAll() {
    StatelessSession session = ((Session) JPA.em().getDelegate()).getSessionFactory().openStatelessSession();
    org.hibernate.Query query = session.createQuery("select l.id from Summary l")
            .setFetchSize(Integer.MIN_VALUE).setCacheable(false).setReadOnly(true);
    ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);

    return new models.ScrollableResultIterator<>(results, String.class);
}




Iterator:

public class ScrollableResultIterator<T> implements Iterator<T> {
   private final ScrollableResults results;
   private final Class<T> type;

   public ScrollableResultIterator(ScrollableResults results, Class<T> type) {
      this.results = results;
      this.type = type;
   }

   @Override
   public boolean hasNext() {
      return results.next();
   }

   @Override
   public T next() {
      return type.cast(results.get(0));
   }
}

For test purpose, i am having 1007 records in my table, whenever i call this end point, it always return only 503 records.

Enabled AKKA log level to DEBUG and tried it again, it logs the following line for 1007 times 2016-07-25 19:55:38 +0530 [DEBUG] from org.hibernate.loader.Loader in application-akka.actor.default-dispatcher-73 - Result row: From the log i confirm that it fetching all, but couldn't get where the remaining one got left.

I run the same query in my workbench and i export it to a file locally and compared it with the file generated by the end point, kept LHS record generated from end point and RHS file exported from Workbench. First row matches, second and third didn't match. After that it got matches for alternate records until the end.

Please correct me, if am doing anything wrong and suggest me is this the correct approach for generating CSV for large db records.

For testing purpose, i removed the logic

Thanks,

Sivakumar

[Greg Methvin]

Hi Sivakumar,

It seems like your ScrollableResultsIterator implementation may be incorrect. Iterator#hasNext() just checks if there is another element. It does not advance to the next element like results.next() does. From a quick Google search I found this implementation; you might want to try a similar strategy.

Greg

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to play-framewor...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/play-framework/d2879d0e-ebc4-41ed-80c7-a3f5ea130416%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Greg Methvin

Senior Software Engineer

Lightbend, Inc.

[Sivakumar Raja]

Hi Greg,

  Thanks for the hint, yes the issue lies on my iterator and used the implementation similar to the link you provided. That worked.

Thanks,

Sivakumar

To view this discussion on the web visit https://groups.google.com/d/msgid/play-framework/CAA%3D11HxE-kG4uiZmuCG7mzoxPF0pFkLwwzs3hbFdympShqJG3w%40mail.gmail.com.