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

查看：27
本文介绍了使用 spring-data-jpa 和 spring-mvc 过滤数据库行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个使用 spring-data-jpa 进行数据访问的 spring-mvc 项目.我有一个名为 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 输入框的表单，可用于过滤 Travels.

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

除了 lastName，我的 Travel 域对象还有很多我想过滤的属性.我认为如果这些属性都是字符串，那么我可以将它们添加为 @RequestParams 并添加一个 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 有一个 period 属性，它是 Period 域对象的外键，我需要将它作为下拉列表用户选择Period.

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

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

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 的优秀回答，在实际实施之后，我想为问题/答案的完整性提供一些评论:

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:

  1. 你应该实现 JpaSpecificationExecutor 来避免类型检查警告，而不是实现 JpaSpecificationExecutor.

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

重用 Travel 对象是不可能的，因为它包含我需要使用 Like 搜索的各种其他对象(也包含其他对象) - 而不是我使用了一个 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并编写一个Specification
  2. 使用 QueryDslPredicateExecutor 并使用 QueryDSL 编写谓词.
  1. Use the JpaSpecificationExecutor and write a Specification
  2. Use the QueryDslPredicateExecutor and use QueryDSL to write a predicate.

使用JpaSpecificationExecutor

首先将 JpaSpecificationExecutor 添加到您的 TravelRepository 这将为您提供一个 findAll(Specification) 方法，您可以删除您的自定义查找器方法.

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

然后您可以在您的存储库中创建一个方法，该方法使用基本上构建查询的 Specification.参见 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.

您唯一需要做的就是创建一个实现 Specification 并根据可用字段构建查询的类.该查询是使用 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(Predicate) 方法，您可以删除自定义查找器方法.

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 对象来构建使用 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屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆