Spring Data JPA中的动态查询 [英] Dynamic Queries in Spring Data JPA
问题描述
我正在寻找一种解决方案来动态构建使用Spring Data JPA的查询。我有一个GameController,它有一个RESTful服务端点/游戏,它需要4个可选参数:流派,平台,年份,标题。这些API可能不会被传递,全都是4,以及它们之间的每个组合。如果任何参数未通过,则默认为空。我需要一个存储库中的方法来构建适当的查询,理想情况下仍然允许Spring Data JPA Paging,尽管我不确定这是否可行。
I发现这篇文章,但这似乎并不是我所需要的,除非我误解。 http://spring.io/ blog / 2011/04/26 / advanced-spring-data-jpa-specifications-and-querydsl /
我知道JPA有Query Criteria API,但真的不知道如何实现这一点。
我意识到我可以为每个可能的场景创建一个方法,但这似乎是非常糟糕的做法和大量不必要的代码。
GameRepository:
package net.jkratz.igdb.repository;
导入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;
公共接口GameRepository扩展了JpaRepository< Game,Long> {
@Query(从g中选择g,GamePlatformMap gpm,Platform p其中g = gpm.game和gpm.platform = p和p.id =:platform)
Page< ;游戏和GT; getGamesByPlatform(@Param(platform)long platformId,Pageable可分页);
@Query(从g中选择g,其中g.title如:title)
Page< Game> getGamesByTitle(@Param(title)字符串标题,Pageable可分页);
@Query(从g中选择g,GameGenreMap ggm,Genre ge,其中g = ggm.game和ggm.genre = ge和ge.id =:genreId)
Page<游戏和GT; getGamesByGenre(@Param(genre)Long genreId,Pageable可分页);
}
我会说使用QueryDSL是你想做的一种方式。
例如,我有一个定义如下的库:
public interface UserRepository扩展PagingAndSortingRepository< User,Long>,QueryDslPredicateExecutor< User> {
公共页面<用户> findAll(谓词谓词,Pageable p);
}
我可以用任何参数组合来调用这个方法,如下所示: p>
public class UserRepositoryTest {
@Autowired
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());
}
}
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);
}
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());
}
}
这篇关于Spring Data JPA中的动态查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!