0

I am not able to segment data coming from repository in pages. Currently List coming from repository has 14 elements. When I try to segment it with size 5 on each page, the code does not work. It throws exception (mentioned below).

JPA Repository method with native query

@Query(value= "select date(start_date) as DATEE, sum(case when subscription_from in ('IVR','SMS') and remarks like '%Change Song%' then 1 else 0 end) as song_change from subscription group by date(start_date) \n#pageable\n",nativeQuery=true)
    Page<Object[]> getSongChangeCount2(Pageable page);

REST Template calling repository

@Override
public SongChangeCountView getSongChangeCount(int pageNo) {
    logger.info("getAllCrbtData method:----------------");
    RestTemplate restTemplate = new RestTemplate();
    String ROOT_URI = IPADDRESS + "songChangeCounts?page=" + pageNo
            + "&size=5"; // here the total size coming is 14. when I change size to more than 14.. code works. but less than 14 it does not work. why so?
    ResponseEntity<SongChangeCountView> response = restTemplate
            .getForEntity(ROOT_URI, SongChangeCountView.class);
    SongChangeCountView songChangeCountView = (SongChangeCountView) response
            .getBody();
    return songChangeCountView;
}

in above code when I change url with size to 5 (which is less than the maximum list size), the code throws below exception.

Exception

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group) from subscription group by date(start_date) 
#pageable' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_181]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_181]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_181]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_181]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) ~[mysql-connector-java-5.1.6.jar:na]
    at com.mysql.jdbc.Util.getInstance(Util.java:381) ~[mysql-connector-java-5.1.6.jar:na]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) ~[mysql-connector-java-5.1.6.jar:na]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) ~[mysql-connector-java-5.1.6.jar:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) ~[mysql-connector-java-5.1.6.jar:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) ~[mysql-connector-java-5.1.6.jar:na]

What is this exception telling about? What should be the appropriate way to resolve this issue.?

JPG
  • 1,247
  • 5
  • 31
  • 64
  • Possible duplicate of [Spring Data and Native Query with pagination](https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination) – Coderino Javarino Sep 02 '19 at 07:56
  • The most appropiate way would be using the in-built pagination that Spring JPA provides in native queries https://dzone.com/articles/pagination-and-sorting-with-spring-data-jpa – Carlos López Marí Sep 02 '19 at 07:57
  • @CarlosLópezMarí I followed what you suggested.. now this coming `com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group) from subscription group by date(start_date) #pageable' at line 1` – JPG Sep 02 '19 at 08:09
  • @CarlosLópezMarí I did this `PageRequest page_req = new PageRequest(page.getPageNumber(), 5); Page songChangePageList = contantSongRepository.getSongChangeCount2(page_req);` – JPG Sep 02 '19 at 08:11
  • @CoderinoJavarino no it is not duplicate. That link was about pagination not working with native query. Mine is working, but size is causing the problem. – JPG Sep 02 '19 at 08:12
  • @JPG I think you are missing the countQuery because of which the Pagination is not working. – dassum Sep 02 '19 at 08:56
  • @JPG I would remove the pageable annotation in the custom query and replace the PageRequest with a Pageable object: Pageable pageable = PageRequest.of(page.getPageNumber(), 5); – Carlos López Marí Sep 02 '19 at 09:59
  • Not sure if it fixes your problem, but remove the ` \n#pageable\n` part. That is a workaround for a bug long fixed.https://jira.spring.io/browse/DATAJPA-928 – Jens Schauder Sep 02 '19 at 12:00
  • @CarlosLópezMarí `Pageable pageable = PageRequest.of(page.getPageNumber(), 5);` is not working. Its saying `The method of(int, int) is undefined for the type PageRequest` I am using spring boot 1.5 – JPG Sep 02 '19 at 12:46
  • @dassum According to given query, can you tell what query, should I use in `countQuery` element of `@query` annotation. – JPG Sep 02 '19 at 12:48
  • Right, I'm using Spring 2.1.7 and that's the way I know it... – Carlos López Marí Sep 02 '19 at 13:21
  • How to throw error when Pagination Size is more than anticipated ? – PAA Feb 09 '20 at 12:04

0 Answers0