Coding practices to generate report

 Some experience to generate report, for instance, Excel file.

1) simple case: 

    read data from data source (database) using JPA or sql

    process data

    generate report file using data processed

2) case 2

if you need to read relatively large data from DB, and process the data in the code. During the process, you need to access DB multiple times, either read data or write data. If this happens, think about if you can use some sql to do the process in DB side without moving data back and force between DB and application as internet is time consuming.

3) case 3

based on case 2, if the business logic is too complex to process using a few sql statements, think about using temporary table. Populating the temp table from a main table data first, then update columns step by step, based on the needs, you can add some helper columns. If the temp table is correctly populated, you can read data from the table to generate the report file. 

If you need physical data, can consider view as well.