springhibernatejpaspring-dataspring-data-jpa

Dynamic Queries in Spring Data JPA


I am looking for a solution to dynamically build queries using Spring Data JPA. I have a GameController which has a RESTful service endpoint /games which takes 4 optional parameters: genre, platform, year, title. The API may be passed none of those, all 4, and every combination in between. If any parameter is not passed it defaults to null. I need a method in the Repository that will build the appropriate query and ideally also still allow Spring Data JPA Paging, although I'm not sure if that is possible.

I found this article but this doesn't seem to be what I need unless I am misunderstanding. http://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

I know JPA has a Query Criteria API but really have no idea how to implement this.

I realize I could create a method for each possible scenario but that seems like really bad practice and a lot of unnecessary code.

GameRepository:

package net.jkratz.igdb.repository;

import net.jkratz.igdb.model.Game;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface GameRepository extends JpaRepository<Game, Long> {

    @Query("select g from Game g, GamePlatformMap gpm, Platform p where g = gpm.game and gpm.platform = p and p.id = :platform")
    Page<Game> getGamesByPlatform(@Param("platform") Long platformId, Pageable pageable);

    @Query("select g from Game g where g.title like :title")
    Page<Game> getGamesByTitle(@Param("title") String title, Pageable pageable);

    @Query("select g from Game g, GameGenreMap ggm, Genre ge where g = ggm.game and ggm.genre = ge and ge.id = :genreId")
    Page<Game> getGamesByGenre(@Param("genre") Long genreId, Pageable pageable);
}

Solution

  • I would say that using QueryDSL is one way of doing what you want.

    For example I have a repository defined as below:

    public interface UserRepository extends PagingAndSortingRepository<User, Long>, QueryDslPredicateExecutor<User> {
    
        public Page<User> findAll(Predicate predicate, Pageable p);
    }
    

    I can call this method with any combination of parameters, like below:

    public class UserRepositoryTest{
    
        @Autowired
        private UserRepository userRepository;
    
        @Test
        public void testFindByGender() {
            List<User> users = userRepository.findAll(QUser.user.gender.eq(Gender.M));
            Assert.assertEquals(4, users.size());
    
            users = userRepository.findAll(QUser.user.gender.eq(Gender.F));
            Assert.assertEquals(2, users.size());
        }
    
        @Test
        public void testFindByCity() {
    
            List<User> users = userRepository.findAll(QUser.user.address.town.eq("Edinburgh"));
            Assert.assertEquals(2, users.size());
    
            users = userRepository.findAll(QUser.user.address.town.eq("Stirling"));
            Assert.assertEquals(1, users.size());
        }
    
        @Test
        public void testFindByGenderAndCity() {
            List<User> users = userRepository.findAll(QUser.user.address.town.eq("Glasgow").and(QUser.user.gender.eq(Gender.M)));
            Assert.assertEquals(2, users.size());
    
            users = userRepository.findAll(QUser.user.address.town.eq("Glasgow").and(QUser.user.gender.eq(Gender.F)));
            Assert.assertEquals(1, users.size());
        }
    }