Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to use JPA @Query with a native SQL query to get random results in Spring Data JPA?

I’m working on a Spring Boot application using Spring Data JPA, and I’m trying to create a repository method that retrieves a random list of questions from a Question table based on a specified category. I attempted to use the @Query annotation for this purpose, but I’m encountering an error.

here is the code:

package com.example.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.example.model.Question;

@Repository
public interface QuestionDao extends JpaRepository<Question, Integer> {

    List<Question> findByCategory(String category);

    @Query(value = "SELECT q.id FROM question q Where q.category=:category ORDER BY RAND() LIMIT :numQ", nativeQuery = true)
    List<Integer> findRandomQuestionsByCategory(String category, int numQ);
}

error:
MySQL: no viable alternative at input ‘SELECT * FROM Question q WHERE q.category = :category ORDER BY RAND() LIMIT :’

What I’ve Tried:
I used ORDER BY RANDOM() but switched to ORDER BY RAND() for MySQL compatibility.

what i am expecting:
A working JPA repository method that retrieves a specified number of random questions from the Question table based on the given category.

Tags:

2

Answers


  1. 0

    You can’t bind a placeholder to the LIMIT clause as you are doing. What you can do is to provide an implementation to the findRandomQuestionsByCategory() method and use the JPA repo methods to enforce a limit to the size of the result set.

    @Override
public List<Integer> findRandomQuestionsByCategory(String category, int limit) {
    String sql = "SELECT q.id FROM question q WHERE q.category = :category ORDER BY RAND()";
    return entityManager.createQuery(sql, Integer.class)
                        .setParameter("category", category)
                        .setMaxResults(limit)
                        .getResultList();
}
    Login or Signup to reply.
  2. 0

    test this:

    import org.springframework.data.repository.query.Param;
@Query(value = "SELECT q.id FROM question q Where q.category=:category ORDER BY radiant LIMIT :numQ")
    List<Integer> findRandomQuestionsByCategory(@Param("category")String category,@Param("numQ") int numQ);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search