Hibernate - load resultset into memory

Question

I have simple report mechanism which load List of objects from database, and write them in excel file (Apache POI)

public List<Exhibitor> exhibitorReport(String fromDate, String toDate, String 
   exhibitorName) {

    DateTime dateTimeFrom = null;
    DateTime dateTimeTo = null;

    CriteriaBuilder builder = em.getCriteriaBuilder();

    CriteriaQuery<Exhibitor> cq = builder.createQuery(Exhibitor.class);

    Root<Exhibitor> from = cq.from(Exhibitor.class);

    List<Predicate> predicates = new ArrayList<Predicate>();

    if (!"".equals(exhibitorName)) {
        predicates.add(builder.equal(from.get("name"), exhibitorName));
    }

    if (!"".equals(fromDate)) {
        if (!StringUtils.isBlank(fromDate)) {
            DateTimeFormatter fmt = DateTimeFormat.forPattern("d-M-y");
            dateTimeFrom = DateTime.parse(fromDate, fmt);
            predicates.add(builder.greaterThanOrEqualTo(from.get("created"), dateTimeFrom));
        }
    }
    if (!"".equals(toDate)) {
        if (!StringUtils.isBlank(toDate)) {
            DateTimeFormatter fmt = DateTimeFormat.forPattern("d-M-y");
            dateTimeTo = DateTime.parse(toDate, fmt);
            predicates.add(builder.lessThan(from.get("created"), dateTimeTo.plusDays(1)));
        }
    }

    cq.select(from).where(predicates.toArray(new Predicate[] {}));
    return em.createQuery(cq).getResultList();
}

Everything here works perfect. I have list with Exhibitors. With the code below, I just write some properties from List to XLSX.

  @Override
  protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, 
   HttpServletRequest request, HttpServletResponse response) throws Exception {

    response.setHeader("Content-Disposition", "attachment; 
   filename=\"ExhibitorReport.xlsx");
    List<Exhibitor> exhibitors = (List<Exhibitor>) model.get("exhibitors");

    Sheet sheet = workbook.createSheet("ExhibitorReport");
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("Country");
    header.createCell(1).setCellValue("City");
    header.createCell(2).setCellValue("Distributor");
    header.createCell(3).setCellValue("Address");
    header.createCell(4).setCellValue("ExhibitorName");
    header.createCell(5).setCellValue("CatalogueId");

    int counter = 1;

    for (Exhibitor exhibitor : exhibitors) {
        Row row = sheet.createRow(counter);
        row.createCell(0).setCellValue(exhibitor.getDistributor().getCountry());
        row.createCell(1).setCellValue(exhibitor.getDistributor().getCity());
        row.createCell(2).setCellValue(exhibitor.getDistributor().getName());
        row.createCell(3).setCellValue(exhibitor.getDistributor().getAddress());
        row.createCell(4).setCellValue(exhibitor.getName());
        row.createCell(5).setCellValue(exhibitor.getCatalogueNumber());

        counter++;
    }

}

Again nothing special... the problem is that this call SELECT query for every row (for every Distributor object) which can make without a problem 10K queries to DB. How can i just write the objects with only one SELECT for the whole list.

This seems to be a classic N+1 selects problem. Is it true to say that your Exhibitor entity has sub entities such as 'distributor' (i.e. distributor is on a different table)? You should probably review the answer to this question: https://stackoverflow.com/questions/2593029/how-can-i-resolve-the-n1-selects-problem — Finbarr O'B, Jul 25 '17 at 08:35
Most likely is that you will need to tune the fetch type for the join with Distributor in your Exhibitor entity to be FetchType.EAGER. — Finbarr O'B, Jul 25 '17 at 08:39
Thank you guys ! The problem is that i have to join Distributor entity in to Exhibitor, before to call the property distributor. It is N + 1 problem. — ROZZ, Jul 25 '17 at 11:15

Hibernate - load resultset into memory

0 Answers0