Springboot Batch retrieve data using Pagination

Question

i've a question about which is the less cost to memory and fast technic/methode to retrieve 1 million record rows data. i know there is some appropriate tools to handle huge data...but as now i'm in the middle of developing my apps...

i've tried to looking some similar question

Hibernate Pagination using HQL

Hibernate Batch process : Stateless Session VS Regular Session Native Query

and end with confusion, how can my apps less cost to memory and fast ?

myapps have a method for retrieve data from as400 and insert into posgresql, total row data 1 millions - 3 millions records...

i'm using entitymanager from jpa to create hql query select , and set the parameter .firstrow for starting row dan .maxrow for total data will retrieve per select query..

and i wrap the code in my simple batch logic :

int awalBaris   =   0; //--YA-- posisi awal row yang akan diretrieve
int jumlahBaris =   2; //--YA-- jumlah row data yang akan diretrieve per query
Long totalBaris; //--YA-- count row data yang akan diretrive tanpa parameter

TypedQuery<Long> qTotalBaris = em.createQuery("select Count(A) from as400 A where A.tvl = :tglAwal ", Long.class)
                                .setParameter("tglAwal", new BigDecimal( tglAwal));

totalBaris  =   qTotalBaris.getSingleResult();

if(totalBaris>0)
{            
    int maxLoop = 0; // --YA-- max looping 

    if(totalBaris < jumlahBaris)
    {
        maxLoop = 1;
    }else {
        maxLoop = (totalBaris.intValue() +jumlahBaris-1)/jumlahBaris;
    }

    for (int i = 0; i < maxLoop; i++) 
    {
        TypedQuery<as400> query  = em.createQuery("select A from as400 A where A.tvl = :tglAwal ", as400.class)
                .setFirstResult(awalBaris)
                .setMaxResults(jumlahBaris)
                .setParameter("tglAwal", new BigDecimal( tglAwal));

        dataAs400   =   query.getResultList();

        for (as400 as400Data : dataAs400) {
            estatePGSQL pgsql = new estatePGSQL();
            //System.out.println("before Set PGSQL : " + as400.getRefn() +" -- "+as400.getDesc());

            //--YA-- menggunakan reflection untuk setter properti class estatetPGSQL-nya postgres
            for(Field f : as400Data.getClass().getDeclaredFields() ){                           
                pgsql.setField(f.getName(), f.get(as400Data));
            }
    
            //System.out.println("After Set pgsql : " + pgsql.getRefn() +" -- "+ pgsql.getAmnt()+" -- "+pgsql.getDesc());
            dataReturn.add( pgsqlRepository.save(pgsql));
        }

        awalBaris += jumlahBaris;

     }                                                                  
}

as above code, i running a count query first to get total row data and divide with maximum row data that will be retrieve per select query.

and i've a second query to retrieve the data...the second query will be placed inside for statement, i'm using reflection for get value from as400 entity for setting value of postgresql entity , and end with save data through jparepository.

Springboot Batch retrieve data using Pagination

0 Answers0