使用spring-data-jpa和spring-mvc过滤数据库行 [英] Filtering database rows with spring-data-jpa and spring-mvc

问题描述

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

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

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

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

此工作正常：用户的表单带有 lastName 输入框，可用于过滤旅行。

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

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

Beyond lastName, my Travel domain 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 有一个期间属性，它是期间域对象，我需要将其作为下拉列表供用户选择期间。

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

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

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.

我知道如果我在我的存储库中实现两个方法并且在我的控制器中使用 if ，这可以完成：

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

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 without using 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 :(

更新03 / 12/13 ：继续从M. Deinum的优秀答案开始，在实际实施之后，我想就问题的完整性提供一些意见/ asnwer：

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:

无需实现的 JpaSpecificationExecutor 应实现 JpaSpecificationExecutor<旅游与GT; 避免类型检查警告。

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

请看看kostja对这个问题的出色答案
真正动态的JPA CriteriaBuilder
，因为如果你想拥有正确的过滤器，将需要实现这一点。

Please take a look at kostja's excellent answer to this question Really dynamic JPA CriteriaBuilder since you will need to implement this if you want to have correct filters.

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

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 :)

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

Reusing the Travel object 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 TravelSearch object that contained the fields I needed to search for.

更新10/05/15 ：根据@ priyank的要求，以下是我实施TravelSearch对象的方式：

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

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

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

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

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

希望我帮忙！

推荐答案

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

For starters you should stop using @RequestParam and 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

1. 使用 JpaSpecificationExecutor 并写一个规范


2. 使用 QueryDslPredicateExecutor 并使用 QueryDSL 写一个谓词。

1. Use the JpaSpecificationExecutor and write a Specification
2. Use the QueryDslPredicateExecutor and use QueryDSL to write a predicate.

< h2>使用 JpaSpecificationExecutor

首先将 JpaSpecificationExecutor 添加到您的 TravelRepository 这将为您提供 findAll（规范）方法，您可以删除自定义查找器方法。

Using JpaSpecificationExecutor

First add the JpaSpecificationExecutor to your TravelRepository this will give you a findAll(Specification) method and you can remove your custom finder methods.

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

然后，您可以在资源库中创建一个使用规范的方法基本上构建查询。请参阅Spring Data JPA 文档。

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

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

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

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.
    }
}

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

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

@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";
}

使用 QueryDslPredicateExecutor

首先将 QueryDslPredicateExecutor 添加到 TravelRepository 这将给出你是一个 findAll（谓词）方法，你可以删除你的自定义查找器方法。

Using QueryDslPredicateExecutor

First add the QueryDslPredicateExecutor to your TravelRepository this will give you a findAll(Predicate) method and you can remove your custom finder methods.

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

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

Next you would implement a service method which would use the Travel object to build a predicate using 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);
    }
}

另请参阅这个沼泽帖子。

这篇关于使用spring-data-jpa和spring-mvc过滤数据库行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！