Pageable in spring for paging on List<Object> is not working

Question

I have a list of object which contain 10000 records i am trying to split that records in each of 10,

But somehow it is not working.. can someone have a look

@Query("select a.applicantid,coalesce(to_char(a.createdon,'yyyy-MM-dd'),to_char(filing_date,'yyyy-MM-dd')) as dt1, \r\n" + 
        "coalesce(c.companyname,i.InstituteName,(u.firstname||' '||u.lastname),\r\n" + 
        "u.firstname) ,a.c_denomination,cc.crop_common_name,cs.crop_botanical_name,\r\n" + 
        "a.id,aps.status,a.cropid, \r\n" + 
        "(select mv.varietytype from VarietyType mv where mv.id= a.varirtytypeid),\r\n" + 
        "(select sv.subvarietytype from SubVarietyType sv,VarietyType mvr \r\n" + 
        " where a.subvarietytypeid = sv.id and mvr.id= sv.varietyid),a.formtype,mcg.crop_group \r\n" + 
        " from Applications a left join ApplicantRegistration ap on \r\n" + 
        " a.applicantid = ap.id left join CompanyRegistration c on ap.companyid = c.id \r\n" + 
        " left join InstitutionRegistration i on ap.institutionid = i.id \r\n" + 
        " left join Crops cc on a.cropid = cc.id left join CropSpecies cs \r\n" + 
        " on a.cropspeciesid =cs.id left join InternalUser u on ap.id = u.applicantid \r\n" + 
        " left join ApplicationStatus aps on a.application_current_status = aps.id "
        + "left join CropGroup mcg on cc.cropgroupid = mcg.id order by a.id desc")
       List<Object[]> getapplication_adminview();



List<Object[]> admin_viewapplication=applicationrepository.getapplication_adminview();
int pageNumber = 0;
int size = 10;
Pageable pageable = PageRequest.of(pageNumber, size); // object of pageable
Page<Object> pages = new PageImpl(admin_viewapplication, pageable, admin_viewapplication.size());
List<Object> lpage = pages.getContent(); // here i am getting the lpage size as 10000 but as i enter pageable as of size 10  i am expecting 10 results only

where i am going wrong in this ? if i am trying to add pagable object to query and run the code i will get the following error:

Cannot create TypedQuery for query with more than one return using requested result type [java.lang.Long]; nested exception is java.lang.IllegalArgumentException: Cannot create TypedQuery for query with more than one return using requested result type [java.lang.Long]

Dwell

Can you show your repository method getapplication_adminview ? – Wicopy

@Wicopy updated ... if i am trying to add pagable to the repository method it is giving error that it is the typedquery.. – Dwell

Your data fetched properly if not use paging ? and are you getting any error pls attached them. – Wicopy

Please attached the error you are getting when add pageable in repository method – Wicopy

@Wicopy see i earlier tried to do with pageable that what is the basic we do like fetching the result and add Pageable with the findall() method ... but here the query is fetching result from many columns .. here it s not working ... – Dwell

In short return pages then you will see only 10 data in response (not efficient approach) and can you please provide full stacktrace – Wicopy

Answer 1

We can use PagedListHolder which can change the list in pages and we can than fetch a page by setting it's page size and page.

PagedListHolder<Object> page = new PagedListHolder(admin_viewapplicationpage);
      page.setPageSize(50); // number of items per page
      page.setPage(0);      // set to first page

      int totalPages = page.getPageCount(); //  gives the totalpages according to the main list
      
      List<Object> admin_viewapplication = page.getPageList();  // a List which represents the current page which is the sublist
      

Dwell

Answer 2

Page just represents one page of data . So page.getContent() only return all data in one page which is specified through constructor when you create this page instance . It has nothing to do with splitting the data in a page.

If you want to split a list , use Lists from Guava is the simplest way to go :

List<List<Object>> splittedList = Lists.partition(list, 10);

If you want to do pagination which split all the data stored in the database into different smaller pages , split it at the database level rather than getting the whole list to the memory to split which will be very inefficient when the entire list is large. See this for how to split it at the database level by declaring Pageable in the query method.

Coactive

bro you please tell me how can i break the 10000 row in chunks of 10 and page number as 0. coz it is a typed query giving too many fields, i have tried with pageable but it is not working.. in this case ... as i am fetching the fields from different tables and evaluate them – Dwell

seem that your query is a native sql , so try to add nativeQuery= true to the @Query , also define the countQuery, and add Pageable to the @Query method – Coactive

you are right in this but i don't want to update the method in repository , so i have taken another approach .. i will share the same.. thanks a lot – Dwell

thanks a lot for sharing the knowledge ... appreciable – Dwell

Answer 3

the following tutorial helped me -> https://www.baeldung.com/spring-data-jpa-query

At this point 4.3. Spring Data JPA Versions Prior to 2.0.4

VERY IMPORTANT to add \ n-- #pageable \ n

Without this I was wrong

Also the pagination setting must be without ordering

PageRequest paginaConf = new PageRequest ((param1 - 1)
                                                 , param2);

Finally to convert the Page <Object []>

  Page <Object []> list = myQueryofRepo ();
         List <XXXModel> lstReturn = myConversor (list.getContent ());
         Page <XXXModel> ret = new PageImpl <XXXModel> (lstReturn, pageConf, param2);

Avilla