CODEWITHSUNDEEP
javaspringhibernatespring-data-jpajpql
Mustafa Celik
Mustafa Celik

Reputation: 2399

Corresponding Spring JPA method name for requesting a random row in SQL

In order to get the a record in an SQL table with a name, I am using the following query: 

SELECT * FROM User WHERE User.name = name;

And the corresponding Spring JPA method name is the following: 

UserEntity findUserByName(@Param("name") String name);

My question is the following:

How can I request a random record from an SQL table?
I know that my SQL query should be the following: 

SELECT * FROM User
ORDER BY RAND()
LIMIT 1;

But, what should be the corresponding Spring JPA method name for that? 

UserEntity findUserXXXXXXX (XXXXXXX);

Upvotes: 5

Views: 6886

Answers (2)

Mohhamed Nabil
Mohhamed Nabil

Reputation: 5502

if you want to random a list you can easily do it by using PagingAndSortingRepository and provide a random page number where (page_number < page_count)

e.g 

long page_count = (totalRecords / perPage)
//Use PageRequest for PagingAndSortingRepository Pageable object
PageRequest.of(new Random().nextLong(page_count), perPage)

or you can provide the random page number from front_end side

Upvotes: 0

abaghel
abaghel

Reputation: 15297

JPA supports functions which are defined in specification. You can use native query option or JPA 2.1 function to call database functions which are not directly supported by the JPA specification. You can use @Query annotation in your spring data jpa repository.

Native Query

@Query(value="SELECT * FROM User ORDER BY RAND() LIMIT 1", nativeQuery = true)
UserEntity findUser();

Function

@Query("SELECT u FROM UserEntity u order by function('RAND')")
List<UserEntity> findUser();

You can use list.get(0) to get the single user.

Upvotes: 14

Related Questions