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

Question

I am creating a REST API, using Spring Boot, which, using data from a table, displays random phrases.

Giving a Google, I found that it is possible to generate random results through pure SQL:

SELECT <coluna> FROM <tabela> ORDER BY RANDOM() LIMIT 1;

I made the appointment of Bank H2, which is the bank I’m using, and it worked:

The problem is that Spring Data does not recognize the SQL command LIMIT. When I create the query inside the repository, through this code:

public interface QuoteRepository extends JpaRepository<Quote, Integer> {

    @Query("SELECT detail FROM Quote ORDER BY RANDOM() LIMIT 1")
    Quote findByQuote();

    Quote findByActor(String actor);
}

I get the following exception:

Unexpected token: LIMIT near line 1, column 54 [SELECT Detail FROM Challenge. Quote ORDER BY RANDOM() LIMIT 1]

Observing: the table scripts is mapped by the domain class Quote.

Does anyone know of a command they can replace LIMIT?

Complete code on Github.

Answer 1

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

JPQL does not implement the limit command, since JPQL attempts to address all relational databases, and each database implements the limit in its form, and some does not implement, JPQL does not implement the limit.

Using JPQL and spring you can use paging, 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);

Answer 2

In case you’re having the same problem, I found that response in the OS and adapted to my problem and worked. It uses paging, as suggested by Marciano Machado, in one of the answers above.

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

So I modified the repository to overwrite the JPA method, findAll(), to receive an object Pageable and I also used the method count(). The repository was 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 this result to multiply by the value generated by the method random() class Math.

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

And then he checks: if there is content, quote receives this content and returns it to the method getQuote(), else, it returns quote null.

Answer 3

Another option if you are using jpa 1.7+ is to change the method nomenclature using top or first, example:

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

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