A crucial part of report generation is data gathering. Data is arguably the most important input in a report. Having good "infrastructure code" for data gathering will highly improve maintainability in report modules. In this chapter, we'll use an SQL database, since it's probably the most common kind of data store in use. This means reports are filled with data queried using SQL. Reporting doesn't require saving data, only reading. SQL queries in reports tend to have multiple lines and are sometimes generated dynamically. MyBatis seems to be an excellent choice for reporting modules. MyBatis allows query definition in XML files, which, unlike Java Strings, help with long multi-line SQL queries and dynamic query definitions.

To use XML-based mappers with MyBatis, specify the name of the XML file using the resource attribute of the mapper element in the MyBatis configuration file:

<configuration>
    ...
    <mappers>
        <mapper resource="mappers/ReportsMapper.xml"/>
    </mappers>
</configuration>

The ReportsMapper.xml file is defined as follows:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="packt.vaadin.datacentric.chapter08.reports.ReportsMapper">
    ...
</mapper>

This file defines the mapper Java interface to use. All queries defined inside the mapper element are mapped to the corresponding methods in the ReportsMapper class. For example, we can define a method to get all the calls before a given time as follows:

public interface ReportsMapper {

    List<CallDto> findCallsBefore(LocalDateTime time);
}

Notice that we are not using the JPA Entity as a result type. Instead, we are using a DTO with, and only with, the required Java fields to store the data from the SQL query:

@Data
public class CallDto {
    private String client;
    private String phoneNumber;
    private City city;
    private LocalDateTime startTime;
    private Integer duration;
    private Status status;
}

We can map an SQL query to the findCallsBefore method as follows:

<mapper namespace="packt.vaadin.datacentric.chapter08.reports.ReportsMapper">

    <select id="findCallsBefore" resultType="packt.vaadin.datacentric.chapter08.reports.CallDto">
        SELECT
          city,
          client,
          phoneNumber,
          startTime,
          duration,
          status
        FROM Call
        WHERE startTime >= #{time}
        ORDER BY startTime DESC
    </select>

</mapper>

The UI doesn't consume the mapper interface directly. Instead, we can define more high-level methods in a service class. For example, the Worldwide Calls in the Last Hour report, the data from which the previous query comes, uses the lastHourCalls method in the ReportsService class:

public class ReportsService {

    public static List<CallDto> lastHourCalls() {
        try (SqlSession session = MyBatisService.getSqlSessionFactory().openSession()) {
            LocalDateTime startOfHour = LocalDateTime.now().minusHours(1);
            ReportsMapper mapper = session.getMapper(ReportsMapper.class);
            return mapper.findCallsBefore(startOfHour);
        }
    }
}

This allows for reusing queries when the data is the same but different processing (such as formatting, or computation of input parameters) is required.