Java 使用 spring-data-jpa 和 spring-mvc 过滤数据库行

Filtering database rows with spring-data-jpa and spring-mvc

提问by Serafeim

I have a spring-mvc project that is using spring-data-jpa for data access. I have a domain object called Travelwhich I want to allow the end-user to apply a number of filters to it.

我有一个使用 spring-data-jpa 进行数据访问的 spring-mvc 项目。我有一个名为的域对象Travel，我想允许最终用户对其应用许多过滤器。

For that, I've implemented the following controller:

为此，我实现了以下控制器：

@Autowired
private TravelRepository travelRep;

@RequestMapping("/search")  
public ModelAndView search(
        @RequestParam(required= false, defaultValue="") String lastName, 
        Pageable pageable) {  
    ModelAndView mav = new ModelAndView("travels/list");  
    Page<Travel> travels  = travelRep.findByLastNameLike("%"+lastName+"%", pageable);
    PageWrapper<Travel> page = new PageWrapper<Travel>(travels, "/search");
    mav.addObject("page", page);
    mav.addObject("lastName", lastName);
    return mav;
}

This works fine: The user has a form with a lastNameinput box which can be used to filter the Travels.

这很好用：用户有一个带有lastName输入框的表单，可用于过滤 Travels。

Beyond lastName, my Traveldomain object has a lot more attributes by which I'd like to filter. I think that if these attributes were all strings then I could add them as @RequestParams and add a spring-data-jpa method to query by these. For instance I'd add a method findByLastNameLikeAndFirstNameLikeAndShipNameLike.

除了姓氏之外，我的Travel域对象还有很多我想过滤的属性。我认为如果这些属性都是字符串，那么我可以将它们添加为@RequestParams 并添加一个 spring-data-jpa 方法来查询这些。例如，我会添加一个方法findByLastNameLikeAndFirstNameLikeAndShipNameLike。

However, I don't know how should I do it when I need to filter for foreign keys. So my Travelhas a periodattribute that is a foreign key to the Perioddomain object, which I need to have it as a dropdown for the user to select the Period.

但是，当我需要过滤外键时，我不知道该怎么做。所以我Travel有一个period属性是Period域对象的外键，我需要将它作为下拉列表供用户选择Period.

What I want to do is when the period is null I want to retrieve all travels filtered by the lastName and when the period is not null I want to retrieve all travels for this period filtered by the lastName.

我想要做的是当句点为空时，我想检索由 lastName 过滤的所有旅行，当句点不为空时，我想检索由 lastName 过滤的这段时间的所有旅行。

I know that this can be done if I implement two methods in my repository and use an ifto my controller:

我知道如果我在我的存储库中实现两个方法并if在我的控制器中使用一个，就可以做到这一点：

public ModelAndView search(
       @RequestParam(required= false, defaultValue="") String lastName,
       @RequestParam(required= false, defaultValue=null) Period period, 
       Pageable pageable) {  
  ModelAndView mav = new ModelAndView("travels/list");  
  Page travels = null;
  if(period==null) {
    travels  = travelRep.findByLastNameLike("%"+lastName+"%", pageable);
  } else {
    travels  = travelRep.findByPeriodAndLastNameLike(period,"%"+lastName+"%", pageable);
  }
  mav.addObject("page", page);
  mav.addObject("period", period);
  mav.addObject("lastName", lastName);
  return mav;
}

Is there a way to do this withoutusing the if? My Travel has not only the period but also other attributes that need to be filtered using dropdowns !! As you can understand, the complexity would be exponentially increased when I need to use more dropdowns because all the combinations'd need to be considered :(

有没有办法在不使用的情况下做到这一点if？我的旅行不仅有期间，还有其他需要使用下拉列表过滤的属性！！正如您所理解的，当我需要使用更多下拉菜单时，复杂性会成倍增加，因为需要考虑所有组合:(

Update 03/12/13: Continuing from M. Deinum's excelent answer, and after actually implementing it, I'd like to provide some comments for completeness of the question/asnwer:

2013 年 3 月 12 日更新：继续 M. Deinum 的精彩回答，在实际实施之后，我想为问题/答案的完整性提供一些评论：

1. Instead of implementing JpaSpecificationExecutoryou should implement JpaSpecificationExecutor<Travel>to avoid type check warnings.

2. Please take a look at kostja's excellent answer to this question Really dynamic JPA CriteriaBuildersince you willneed to implement this if you want to have correct filters.

3. The best documentation I was able to find for the Criteria API was http://www.ibm.com/developerworks/library/j-typesafejpa/. This is a rather long read but I totally recommend it - after reading it most of my questions for Root and CriteriaBuilder were answered :)

4. Reusing the Travelobject was not possible because it contained various other objects (who also contained other objects) which I needed to search for using Like- instead I used a TravelSearchobject that contained the fields I needed to search for.

1. JpaSpecificationExecutor您应该实施JpaSpecificationExecutor<Travel>以避免类型检查警告，而不是实施。

2. 请查看 kostja 对这个问题真正动态 JPA CriteriaBuilder的出色回答， 因为如果您想拥有正确的过滤器，则需要实现它。

3. 我为 Criteria API 找到的最好的文档是http://www.ibm.com/developerworks/library/j-typesafejpa/。这是一个相当长的阅读，但我完全推荐它 - 阅读后，我对 Root 和 CriteriaBuilder 的大部分问题都得到了回答:)

4. 重用该Travel对象是不可能的，因为它包含我需要搜索使用的各种其他对象（也包含其他对象）Like- 相反，我使用了一个TravelSearch包含我需要搜索的字段的对象。

Update 10/05/15: As per @priyank's request, here's how I implemented the TravelSearch object:

2015 年 5 月 10 日更新：根据 @priyank 的要求，以下是我实现 TravelSearch 对象的方法：

public class TravelSearch {
    private String lastName;
    private School school;
    private Period period;
    private String companyName;
    private TravelTypeEnum travelType;
    private TravelStatusEnum travelStatus;
    // Setters + Getters
}

This object was used by TravelSpecification (most of the code is domain specific but I'm leaving it there as an example):

TravelSpecification 使用了此对象（大部分代码是特定于域的，但我将其留在此处作为示例）：

public class TravelSpecification implements Specification<Travel> {
    private TravelSearch criteria;


    public TravelSpecification(TravelSearch ts) {
        criteria= ts;
    }

    @Override
    public Predicate toPredicate(Root<Travel> root, CriteriaQuery<?> query, 
            CriteriaBuilder cb) {
        Join<Travel, Candidacy> o = root.join(Travel_.candidacy);

        Path<Candidacy> candidacy = root.get(Travel_.candidacy);
        Path<Student> student = candidacy.get(Candidacy_.student);
        Path<String> lastName = student.get(Student_.lastName);
        Path<School> school = student.get(Student_.school);

        Path<Period> period = candidacy.get(Candidacy_.period);
        Path<TravelStatusEnum> travelStatus = root.get(Travel_.travelStatus);
        Path<TravelTypeEnum> travelType = root.get(Travel_.travelType);

        Path<Company> company = root.get(Travel_.company);
        Path<String> companyName = company.get(Company_.name);

        final List<Predicate> predicates = new ArrayList<Predicate>();
        if(criteria.getSchool()!=null) {
            predicates.add(cb.equal(school, criteria.getSchool()));
        }
        if(criteria.getCompanyName()!=null) {
            predicates.add(cb.like(companyName, "%"+criteria.getCompanyName()+"%"));
        }
        if(criteria.getPeriod()!=null) {
            predicates.add(cb.equal(period, criteria.getPeriod()));
        }
        if(criteria.getTravelStatus()!=null) {
            predicates.add(cb.equal(travelStatus, criteria.getTravelStatus()));
        }
        if(criteria.getTravelType()!=null) {
            predicates.add(cb.equal(travelType, criteria.getTravelType()));
        }
        if(criteria.getLastName()!=null ) {
            predicates.add(cb.like(lastName, "%"+criteria.getLastName()+"%"));
        }
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));

    }
}

Finally, here's my search method:

最后，这是我的搜索方法：

@RequestMapping("/search")  
public ModelAndView search(
        @ModelAttribute TravelSearch travelSearch,
        Pageable pageable) {  
    ModelAndView mav = new ModelAndView("travels/list");  

    TravelSpecification tspec = new TravelSpecification(travelSearch);

    Page<Travel> travels  = travelRep.findAll(tspec, pageable);

    PageWrapper<Travel> page = new PageWrapper<Travel>(travels, "/search");

    mav.addObject(travelSearch);

    mav.addObject("page", page);
    mav.addObject("schools", schoolRep.findAll() );
    mav.addObject("periods", periodRep.findAll() );
    mav.addObject("travelTypes", TravelTypeEnum.values());
    mav.addObject("travelStatuses", TravelStatusEnum.values());
    return mav;
}

Hope I helped!

希望我有所帮助！

采纳答案by M. Deinum

For starters you should stop using @RequestParamand put all your search fields in an object (maybe reuse the Travel object for that). Then you have 2 options which you could use to dynamically build a query

对于初学者，您应该停止使用@RequestParam并将所有搜索字段放在一个对象中（可能会为此重用 Travel 对象）。然后您有 2 个选项可用于动态构建查询

1. Use the JpaSpecificationExecutorand write a Specification
2. Use the QueryDslPredicateExecutorand use QueryDSLto write a predicate.

1. 使用JpaSpecificationExecutor并写一个Specification
2. 使用QueryDslPredicateExecutor和 使用QueryDSL编写谓词。

Using JpaSpecificationExecutor

使用 JpaSpecificationExecutor

First add the JpaSpecificationExecutorto your TravelRepositorythis will give you a findAll(Specification)method and you can remove your custom finder methods.

首先添加JpaSpecificationExecutor到你的TravelRepositorythis 会给你一个findAll(Specification)方法，你可以删除你的自定义查找器方法。

public interface TravelRepository extends JpaRepository<Travel, Long>, JpaSpecificationExecutor<Travel> {}

Then you can create a method in your repository which uses a Specificationwhich basically builds the query. See the Spring Data JPA documentationfor this.

然后您可以在您的存储库中创建一个方法，该方法使用一个Specification基本上构建查询的方法。有关此内容，请参阅 Spring Data JPA文档。

The only thing you need to do is create a class which implements Specificationand which builds the query based on the fields which are available. The query is build using the JPA Criteria API link.

您唯一需要做的就是创建一个类，该类实现Specification并根据可用字段构建查询。该查询是使用 JPA Criteria API 链接构建的。

public class TravelSpecification implements Specification<Travel> {

    private final Travel criteria;

    public TravelSpecification(Travel criteria) {
        this.criteria=criteria;
    }

    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        // create query/predicate here.
    }
}

And finally you need to modify your controller to use the new findAllmethod (I took the liberty to clean it up a little).

最后你需要修改你的控制器以使用新findAll方法（我冒昧地清理了一下）。

@RequestMapping("/search")  
public String search(@ModelAttribute Travel search, Pageable pageable, Model model) {  
Specification<Travel> spec = new TravelSpecification(search);
    Page<Travel> travels  = travelRep.findAll(spec, pageable);
    model.addObject("page", new PageWrapper(travels, "/search"));
    return "travels/list";
}

Using QueryDslPredicateExecutor

使用 QueryDslPredicateExecutor

First add the QueryDslPredicateExecutorto your TravelRepositorythis will give you a findAll(Predicate)method and you can remove your custom finder methods.

首先添加QueryDslPredicateExecutor到你的TravelRepositorythis 会给你一个findAll(Predicate)方法，你可以删除你的自定义查找器方法。

public interface TravelRepository extends JpaRepository<Travel, Long>, QueryDslPredicateExecutor<Travel> {}

Next you would implement a service method which would use the Travelobject to build a predicate using QueryDSL.

接下来，您将实现一个服务方法，该方法将使用Travel对象来构建使用 QueryDSL 的谓词。

@Service
@Transactional
public class TravelService {

    private final TravelRepository travels;

    public TravelService(TravelRepository travels) {
        this.travels=travels;
    }

    public Iterable<Travel> search(Travel criteria) {

        BooleanExpression predicate = QTravel.travel...
        return travels.findAll(predicate);
    }
}

See also this bog post.

另见这篇博文。