Как использовать JPA @Query с нативным SQL-запросом для получения случайных результатов в Spring Data JPA?

Содержание

Вопрос или проблема
Ответ или решение
Шаги для исправления
Реализация
Примечания по коду:
Завершение

Вопрос или проблема

Я работаю над приложением Spring Boot, использующим Spring Data JPA, и пытаюсь создать метод репозитория, который извлекает случайный список вопросов из таблицы Question на основе указанной категории. Я пытался использовать аннотацию @Query для этой цели, но сталкиваюсь с ошибкой.

Вот код:

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);
}

ошибка:
MySQL: нет жизнеспособной альтернативы на входе ‘SELECT * FROM Question q WHERE q.category = :category ORDER BY RAND() LIMIT :’

Что я пробовал:
Я использовал ORDER BY RANDOM(), но перешел к ORDER BY RAND() для совместимости с MySQL.

что я ожидаю:
Работающий метод репозитория JPA, который извлекает указанное количество случайных вопросов из таблицы Question на основе данной категории.

Вы не можете привязать заполнитель к клаузе LIMIT, как вы это делаете. То, что вы можете сделать, это предоставить реализацию метода findRandomQuestionsByCategory() и использовать методы репозитория JPA, чтобы установить ограничение на размер результирующего набора.

@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();
}

Ответ или решение

Вам необходимо получить случайный набор вопросов из базы данных на основе заданной категории с использованием Spring Data JPA и нативного SQL-запроса. Код, предоставленный вами, содержит ошибку, связанную с неправильным использованием параметров для оператора LIMIT. В MySQL нельзя использовать именованные параметры в секции LIMIT, что и вызвало возникновение ошибки.

Вот как вы можете правильно реализовать метод findRandomQuestionsByCategory() для получения заданного количества случайных вопросов на основе категории:

Шаги для исправления

Удалите параметр LIMIT из аннотации @Query: Вам необходимо удалить :numQ из вашего запроса, так как MySQL не поддерживает именованные параметры в секции LIMIT.
Используйте EntityManager для задания лимита: Для того чтобы ограничивать количество возвращаемых результатов, вы можете использовать EntityManager и настроить параметр setMaxResults.

Реализация

Ниже приведен пример кода с поправками, который вы можете использовать:

package com.example.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.example.model.Question;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

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

    List<Question> findByCategory(String category);

    default List<Integer> findRandomQuestionsByCategory(String category, int limit) {
        String sql = "SELECT q.id FROM question q WHERE q.category = :category ORDER BY RAND()";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter("category", category);
        query.setMaxResults(limit);
        return query.getResultList();
    }
}

Примечания по коду:

Метод findRandomQuestionsByCategory теперь использует EntityManager для выполнения запроса.
Мы создаем нативный SQL-запрос с помощью entityManager.createNativeQuery() и устанавливаем параметр :category.
Использование setMaxResults(limit) позволяет вам задать необходимый лимит на количество возвращаемых результатов.

Завершение

Данная реализация позволяет вам избежать ошибок, связанных с использованием именованных параметров в операторе LIMIT, и гарантирует корректное извлечение случайных вопросов. Теперь при вызове метода findRandomQuestionsByCategory() с указанием категории и количества требуемых вопросов вы будете получать правильный результат.

Не забывайте, что использование ORDER BY RAND() может быть ресурсозатратным для больших таблиц, и для оптимизации запросов на больших объемах данных лучше рассмотреть другие подходы, такие как выборка случайных ID из таблицы с последующей выборкой по этим ID.