Spring JPA - create specification that returns entities in random order (MySql ORDER BY RAND() function)

9 points
Created by:

In this article, we would like to show how in Spring JPA create Specification that provides random order for returned entities (random sorting).

Quick solution:

// CriteriaQuery<?> query = ...
// CriteriaBuilder builder = ...

// put inside toPredicate function following line:
query.orderBy(builder.asc(builder.function("RAND", null))); // MySql RAND() function used

// do not use sort parameter using PageRequest:
PageRequest pageRequest = PageRequest.of(pageNumber - 1, pageSize);


Practical example file:

package com.example.controllers;

import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;

import com.example.utils.HttpUtils;
import com.example.database.repositories.OffersRepository; // use your repository here
import com.example.database.specifications.OffersSpecification;
import com.example.database.entities.OfferEntity;

public class OffersController {

    private OffersRepository offersRepository,

            method = RequestMethod.GET,
            value = "/offers",
            produces = MediaType.APPLICATION_JSON_VALUE
    public List<OfferEntity> getOffers(HttpServletRequest request) {

        int pageNumber = 1;
        int pageSize = 20;

        OffersSpecification offersSpecification = new OffersSpecification();
        PageRequest pageRequest = PageRequest.of(pageNumber - 1, pageSize); // do not add here sort parameter here

        Page<OfferEntity> pagedOffers = this.offersRepository.findAll(offersSpecification, pageRequest);

        return pagedOffers;
} file:

package com.example.database.specifications;


import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import com.example.database.entities.OfferEntity;

public class OffersSpecification implements Specification<OfferEntity> {

    public Predicate toPredicate(Root<OfferEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        Predicate predicate = builder.and();

        // ...

        query.orderBy(builder.asc(builder.function("RAND", null))); // MySql RAND() function used

        return predicate;
} file: 

package com.example.database.entities;

import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Entity;
import javax.persistence.Table;

import java.util.Date;

@Table(name = "offers")
public class OfferEntity
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // other fields ...    

    private Long parentId;
    private Date publicationTime;

    // getters and setters ...
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!

Java - MySQL

Native Advertising
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂