Spring Data JPA does not recognize the SQL 'Limit' command?

If you have the same problem, I found this answer in SO and adapted to my problem and it worked. It uses pagination, as suggested by Marciano Machado, in one of the answers above.

Due to the way the challenge was sent, as well as unit tests that were previously written, and preformatted, needed to keep the original method and return it to a single object.

So I modified the repository to override the JPA method, findAll() , so that it received a Pageable object and also used the count() method. The repository looks like this:

long count();
Page<Quote> findAll(Pageable pageable);

In the class that implements the service, the code looks like this:

public Quote getQuote() {
        long countIds = repository.count();
        int idRandom = (int)(Math.random() * countIds);
        Page<Quote> quotePage = repository.findAll(PageRequest.of(idRandom, 1)); 
        Quote quote = null;
        if(quotePage.hasContent()) {
            quote = quotePage.getContent().get(0);
        }
        return quote;
    } 

Essentially, it uses the repository to count the amount of IDs in the table, and uses that result to multiply by the value generated by the random() method of the Math class.

Then the search is done and saved to quotePage , using ID random ( idRandom ), limiting it to one page only.

And then it checks: if content exists, quote receives that content and returns it to the getQuote() method, otherwise, it returns quote as null.

Hello, spring, uses JPA, which in turn uses the JPQL language for queries, which is a language similar to SQL but uses the object-relational approach.

JPQL does not implement the limit command, since JPQL tries to approach all relational databases, and each bank implements the limit of its form, and some do not implement, JPQL does not implement the limit.

Using JPQL and spring you can use pagination, as below:

public interface QuoteRepository extends JpaRepository<Quote, Integer> {

    @Query("SELECT detail FROM Quote ORDER BY RANDOM()")
    List<String> findByQuote(Pageable pageable);

}

In your service you can use the following code:

Pageable primeiroResultado = new PageRequest(0, 1);
List<String> result = repository.findByQuote(primeiroResultado);

Another option if you are using jpa 1.7 + is to change the naming of the method using top or first, eg:

 @Query("SELECT detail FROM Quote ORDER BY RANDOM(")
    Quote findTop1ByQuote();

 @Query("SELECT detail FROM Quote ORDER BY RANDOM()")
    Quote findFirstByQuote();