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.

HSQL DB custom function to mimic the DB function that HSQL not support (compatibility issue)

It is a solution say when we do integration test using Hyper SQLDB, if the SQL statement have functions which in-memory DB not support, we can use Java Language Routines to mimic the logic the specific-DB-function performs.

An examples is if the specific DB is SQL Server, and the SQL statement has LEN() function which will return the length of the varchar, in SQL Server, however, HSQLDB not support LEN() function (only support length()), and also we will not write a specific SQL for HSQLDB for testing purpose, in this case, we can use Java Language Routines which is a static method of a Java class, specified with a fully qualified method name in the routine definition.

Step 1: define a Java class and a static method to mimic the function you need

               For this case, I need a method by providing a string and return the length of string, so the method may look like below:

   public class JavaRoutinesForHSql {

   public static int stringLength(String chars){
      if (chars!=null)
         return chars.trim().length();
      return 0;
   }
}

Step 2: define the routine in sql script

             Before the SQL statement is called, create the function as below:     

Step 3:  you can drop the custom routine if needed:

               DROP SPECIFIC ROUTINE LEN;

For more information, please refer to:  http://hsqldb.org/doc/guide/sqlroutines-chapt.html#src_jrt_routines