0

I'm new to spring. I would like to add pagination to my apis but I keep getting this error

 oracle.jdbc.OracleDatabaseException: ORA-00904: "R" : invalid identifier

"R" is the alias of my table in this query

Repository

@Query(
value ="select * from students r inner join conf c on r.status_id = c.id where c.code = 'NEW'", 
nativeQuery = true
)
Page<Student> findNewStudents(Pageable pageable);

this is just a simple query but I have also some LONG queries

I searched a lot I found that countQuery should be added and effictively it worked for me

@Query(
value ="select * from students r inner join conf c on r.status_id = c.id where c.code ='NEW'", 
countQuery ="select count(*) from students r inner join conf c on r.status_id = c.id where c.code = 'NEW'" ,
nativeQuery = true
)
Page<Student> findNewStudents(Pageable pageable);

but according to this answer countQuery is not needed since Spring Boot 2.4.3 and I'm using Spring Boot 2.7.1 but I got the error above if don't use it.

as I said with countQuery it works but I have some long queries it's bit hard to have the query of counting also!

UPDATE

after enabling sql log, I got

2022-08-14 23:30:07.384 DEBUG 10996 --- [nio-8080-exec-1] org.hibernate.SQL : select * from students r inner join conf c on r.status_id = c.id where c.code ='NEW'  fetch first ? rows only

2022-08-14 23:30:07.525 DEBUG 10996 --- [nio-8080-exec-1] org.hibernate.SQL:  select count(r) from students r inner join conf c on r.status_id = c.id where c.code = 'NEW'

Thanks in advance

hakima maarouf
  • 1,010
  • 1
  • 9
  • 26
  • To use pagination with native SQL queries declared using the @Query annotation, you need to define the count query by yourself. Visit here: https://attacomsian.com/blog/spring-data-jpa-pagination – Faramarz Afzali Aug 13 '22 at 17:10
  • 1
    enable an sql logging first, to see what queries had been generated by the `Hibernate` – Yuriy Tsarkov Aug 14 '22 at 18:58
  • @YuriyTsarkov I updated my post to include the result of logging, I guess it's because of the `count(r)`. I tried to made `select r.* from students r.. ` but I got `ORA-01747: invalid user.table.column, table.column, or column specification ` – hakima maarouf Aug 14 '22 at 22:40
  • Sure you'll get - count(r.*) is suppose `group by` existence. I've read an answer to a [post](https://stackoverflow.com/a/68098138/7214142) you've linked above. Please read the first comment there! _avoid using table alias in select query, it won't work!_. So I'd propose you to rewrite the query in an old-style comma-separated style join syntax. – Yuriy Tsarkov Aug 14 '22 at 23:42
  • Upd prev answer: not in old-style syntax, but without aliases and with asterisk – Yuriy Tsarkov Aug 14 '22 at 23:48
  • @YuriyTsarkov without alias I got `ORA-00918: column ambiguously defined` – hakima maarouf Aug 15 '22 at 09:34

0 Answers0