1

Problem: I want to fetch list of records with provided limit at JPA query/query Method level (using MariaDb).

What I am trying or have tried:

I have been trying to get List of POJO. I used JPA query @Query with LIMIT 1, but It's not accepting the limit. It gives compile time error. So I tried PageRequest.of(0,1) to to pass the limit with query, however, still doesn't work. I also tried with JPA query methods findTopByClientID and findFirstByClientID. No luck! I get the same exception. I am not sure if we need to add something in the configuration to support all this but this is RuntimeException.

Exception that I'm getting:

enter image description here

Here is the exception cause in Stack trace. But I don't see any SQL syntax error (created at runtime).

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near at line 1

Code:

public interface UserDAO extends JpaRepository<UserPO, Integer>, JpaSpecificationExecutor<UserPO> {
   Page<UserPO> findByClientID(Integer clientID, Pageable pr);
   default Page<UserPO> getFromQueue() {
       return findByClientID(124, PageRequest.of(0, 1));
   }
}

   @Service
   class UserImpl {
 public User getUser() {
    final Page<UserPO> user = userDAO.getFromQueue();
 }  
   }

I don't want to use Native Query, using so I would loose advantage of using JPA. Also I'm using MariaDB Database. Let me know If you need more detail or the question is not framed so well.

Ram
  • 3,887
  • 4
  • 27
  • 49
  • 3
    Did you try enabling `spring.jpa.show-sql=true and spring.jpa.properties.hibernate.format_sql=true` and see what query spring data is generating and running that query directly on database? – Kavithakaran Kanapathippillai Jul 31 '20 at 20:09
  • What is the order of get first data by client id ? If it's by id then try `findFirstByClientIDOrderByIdAsc` and show us your entity class – Eklavya Jul 31 '20 at 20:22
  • My application don't have `application.properties` file, so I added those properties in `bootstrap.properties` but that doesn't seem helpful. I cannot see the generated `SQL`. – Ram Jul 31 '20 at 20:22
  • @User-Upvotedon'tsayThanks I did try that way also like `findTopByClientIDOrderByDate(123)`. Where `Date` is a field in `UserPO` but still same exception. – Ram Jul 31 '20 at 20:26
  • @MAC Add your enitity class `UserPO` and related class also if used for query – Eklavya Jul 31 '20 at 20:30
  • just add `application.properties` under `src/main/resources` , put those properties and you will be able to see generated queries – Kavithakaran Kanapathippillai Jul 31 '20 at 20:46
  • Have you taken a look at [this](https://stackoverflow.com/questions/44565820/what-is-the-limit-clause-alternative-in-jpql) answer – Morteza Aug 01 '20 at 03:55

0 Answers0