Dynamic Queries in Spring Data JPA

Question

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

Answer 1

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

Answer 2

For those using Kotlin (and Spring Data JPA), we've just open-sourced a Kotlin JPA Specification DSL library which lets you create type-safe dynamic queries for a JPA Repository.

It uses Spring Data's JpaSpecificationExecutor (i.e. JPA criteria queries), but without the need for any boilerplate or generated metamodel.

The readme has more details on how it works internally, but here's the relevant code examples for a quick intro.

import au.com.console.jpaspecificationsdsl.*   // 1. Import Kotlin magic

////
// 2. Declare JPA Entities
@Entity
data class TvShow(
    @Id
    @GeneratedValue
    val id: Int = 0,
    val name: String = "",
    val synopsis: String = "",
    val availableOnNetflix: Boolean = false,
    val releaseDate: String? = null,
    @OneToMany(cascade = arrayOf(javax.persistence.CascadeType.ALL))
    val starRatings: Set<StarRating> = emptySet())

@Entity
data class StarRating(
    @Id
    @GeneratedValue
    val id: Int = 0,
    val stars: Int = 0)


////
// 3. Declare JPA Repository with JpaSpecificationExecutor
@Repository
interface TvShowRepository : CrudRepository<TvShow, Int>, JpaSpecificationExecutor<TvShow>


////
// 4. Kotlin Properties are now usable to create fluent specifications
@Service
class MyService @Inject constructor(val tvShowRepo: TvShowRepository) {
   fun findShowsReleasedIn2010NotOnNetflix(): List<TvShow> {
     return tvShowRepo.findAll(TvShow::availableOnNetflix.isFalse() and TvShow::releaseDate.equal("2010"))
   }

   /* Fall back to spring API with some extra helpers for more complex join queries */
   fun findShowsWithComplexQuery(): List<TvShow> {
       return tvShowRepo.findAll(where { equal(it.join(TvShow::starRatings).get(StarRating::stars), 2) })
   }
}

For more complex and dynamic queries it's good practice to create functions that use the DSL to make queries more readable (as you would for QueryDSL), and to allow for their composition in complex dynamic queries.

fun hasName(name: String?): Specifications<TvShow>? = name?.let {
    TvShow::name.equal(it)
}

fun availableOnNetflix(available: Boolean?): Specifications<TvShow>? = available?.let {
    TvShow::availableOnNetflix.equal(it)
}

fun hasKeywordIn(keywords: List<String>?): Specifications<TvShow>? = keywords?.let {
    or(keywords.map { hasKeyword(it) })
}

fun hasKeyword(keyword: String?): Specifications<TvShow>? = keyword?.let {
    TvShow::synopsis.like("%$keyword%")
}

These functions can be combined with and() and or() for complex nested queries:

val shows = tvShowRepo.findAll(
        or(
                and(
                        availableOnNetflix(false),
                        hasKeywordIn(listOf("Jimmy"))
                ),
                and(
                        availableOnNetflix(true),
                        or(
                                hasKeyword("killer"),
                                hasKeyword("monster")
                        )
                )
        )
)

Or they can be combined with a service-layer query DTO and mapping extension function

/**
 * A TV show query DTO - typically used at the service layer.
 */
data class TvShowQuery(
        val name: String? = null,
        val availableOnNetflix: Boolean? = null,
        val keywords: List<String> = listOf()
)

/**
 * A single TvShowQuery is equivalent to an AND of all supplied criteria.
 * Note: any criteria that is null will be ignored (not included in the query).
 */
fun TvShowQuery.toSpecification(): Specifications<TvShow> = and(
        hasName(name),
        availableOnNetflix(availableOnNetflix),
        hasKeywordIn(keywords)
)

for powerful dynamic queries:

val query = TvShowQuery(availableOnNetflix = false, keywords = listOf("Rick", "Jimmy"))
val shows = tvShowRepo.findAll(query.toSpecification())

JpaSpecificationExecutor supports paging, so you can achieve pageable, type-safe, dynamic queries!

Answer 3

I have got a solution for this. I wrote some code to extend the spring-data-jpa .

I call it spring-data-jpa-extra

spring-data-jpa-extra comes to solve three problem:

1. dynamic native query support like mybatis
2. return type can be anything
3. no code, just sql

You can try it : )