Reporting queries

Scenario

For a report you need to print the name of the author, the number of books he wrote and the total of chapters in his books. If your dataset consists of 10 authors with 10 books each and each book having 10 chapters, you will end up with 1000 objects in memory. The report requires only 10 java.lang.String and 20 java.lang.Integer.

Solution

The problem can easily be solved with a reporting query. The following query returns a list of Object arrays instead of entitys.

List<Object[]> authorReportObjects = session.createQuery("select a.name, " +
   "count(b) as totalBooks, count(c) as totalChapters " +
   "from Author a join a.books b join b.chapters c group by a.name")
   .list();
for (Object[] objects : authorReportObjects) {
   log.info(String.format("Report: Author %s, total books %d, total chapters %d",
      objects[0], objects[1], objects[2]));
}

An alternative is to fill a Java class dynamically. If you use HQL you might call the constructor with a corresponding arguments or with both HQL and criteria you can use an AliasToBeanResultTransformer. In that case the Java class needs to have the same properties as the column names of your query.

Constructor approach. 

List<AuthorReport> authorReports = session.createQuery(
   "select new de.laliluna.other.query.AuthorReport(a.id, a.name, " +
   "count(b), count(c)) " +
   "from Author a join a.books b join b.chapters c group by a.id, a.name")
   .list();
for (AuthorReport authorReport : authorReports) {
   log.info(authorReport);
}

AliasToBeanResultTransformer approach. 

List<AuthorReport> authorReports = session.createQuery(
   "select a.name as name, count(b) as totalBooks, count(c) as totalChapters " +
   "from Author a join a.books b join b.chapters c group by a.name")
   .setResultTransformer(new AliasToBeanResultTransformer(AuthorReport.class))
   .list();
for (AuthorReport authorReport : authorReports) {
 log.info(authorReport);
}