0

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]

dhS
  • 3,739
  • 5
  • 26
  • 55
  • Can you show your repository method `getapplication_adminview` ? – Eklavya Jun 20 '20 at 13:01
  • @Eklavya updated ... if i am trying to add pagable to the repository method it is giving error that it is the typedquery.. – dhS Jun 20 '20 at 13:03
  • Your data fetched properly if not use paging ? and are you getting any error pls attached them. – Eklavya Jun 20 '20 at 13:06
  • Please attached the error you are getting when add pageable in repository method – Eklavya Jun 20 '20 at 13:14
  • @Eklavya 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 ... – dhS Jun 20 '20 at 13:27
  • In short return `pages` then you will see only 10 data in response (not efficient approach) and can you please provide full stacktrace – Eklavya Jun 20 '20 at 13:33

3 Answers3

1

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.

Ken Chan
  • 84,777
  • 26
  • 143
  • 172
  • 1
    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 – dhS Jun 20 '20 at 13:12
  • 1
    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 – Ken Chan Jun 20 '20 at 15:02
  • 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 – dhS Jun 20 '20 at 15:17
  • thanks a lot for sharing the knowledge ... appreciable – dhS Jun 20 '20 at 15:24
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
dhS
  • 3,739
  • 5
  • 26
  • 55
1

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);
Dharman
  • 30,962
  • 25
  • 85
  • 135
Juan Ignacio Liska
  • 172
  • 1
  • 6