在 spring 数据 jpa 存储库中通过许多可选参数搜索 [英] Search by many optional parameters in spring data jpa repository
问题描述
所以我有表格评论和作者.我想用许多可选参数构建复杂的搜索栏.我想使用可选的作者名字/姓氏和一些诸如流行度(基于评论评级)之类的标志来过滤评论.
So I have tables comment and author. I want to build complex search bar with many optional parameters. I want filter comments with optional author firstname/lastname and some flags like popularity(based on comment rating).
由于我不知道如何使用 spring 数据 jpa 存储库编写它,我一直在考虑将其编写为带有 @Query 注释的本机查询,这样应该可以工作
As I dont know how to write it using spring data jpa repository I ve been thinking on writing it as native query with @Query annotation, smth like this should work
Select c.* from comment c join author a on a.id = c.author_id
Where (:firstname = '' or (:firstname = a.firstname))
And (:lastname = '' or (:lastname = a.lastname))
And (:popular = false or (c.rating > 25))
是否可以选择使用 spring data jpa 编写它?
Is there option to write it using spring data jpa?
将来我计划添加更多参数和分页.使用 spring 就像 1 分钟的 sql 查询,我将失去几个小时.
In future I plan to add more parameters and pagination for example. Using spring it would be like 1 minute with sql query I will lost few hours.
在这种情况下是否有一些最佳实践?
Is there are some best practices in such cases?
推荐答案
我建议使用 JpaSpecificationExecutor
存储库方法 findAll(Specification
.此解决方案允许您使用相同的存储库和服务 API 来扩展参数列表
I suggest to use JpaSpecificationExecutor
repository method findAll(Specification<T> spec, Pageable pageable)
. This solution allows you to extend the parameters list using the same repository and service API
实体:
@Entity
@Table(name = "author")
public class Author {
@Id
@GeneratedValue
@Column(name = "id")
private Long id;
@Column(name = "firstname")
String firstname ;
@Column(name = "lastname")
String lastname ;
// getters, setters, equals, hashcode, toString ...
}
@Entity
@Table(name = "comment")
public class Comment {
@Id
@GeneratedValue
@Column(name = "id")
private Long id;
@ManyToOne
@JoinColumn(name = "author_id")
Author author;
@Column(name = "rating")
Integer rating;
// getters, setters, equals, hashcode, toString ...
}
存储库:
@Repository
public interface CommentRepository
extends JpaRepository<Comment, Long>, JpaSpecificationExecutor<Comment> {
}
规范: org.springframework.data.jpa.domain.Specification
public class CommentSpecs {
/** if firstname == null then specification is ignored */
public static Specification<Comment> authorFirstnameEquals(String firstname) {
return (root, query, builder) ->
firstname == null ?
builder.conjunction() :
builder.equal(root.get("author").get("firstname"), firstname);
}
/** if lastname == null then specification is ignored */
public static Specification<Comment> authorLastnameEquals(String lastname) {
return (root, query, builder) ->
lastname == null ?
builder.conjunction() :
builder.equal(root.get("author").get("lastname"), lastname);
}
/** if rating == null then specification is ignored */
public static Specification<Comment> ratingGreaterThan(Integer rating) {
return (root, query, builder) ->
rating == null ?
builder.conjunction() :
builder.greaterThan(root.get("rating"), rating);
}
}
服务方法参数:
public class CommentParameters {
String authorFirstname;
String authorLastname;
Integer rating;
// getters, setters
}
所有参数都可以为空.您可以只设置您需要的参数.如果参数为 null,我们的规范会忽略它
All parameters are nullable. You can set the parameters you need only. If the parameter is null it is ignored by our specifications
服务:
@Service
public class CommentService {
@Autowired
CommentRepository repository;
public List<Comment> getComments(CommentParameters params, Pageable pageable) {
Specification spec1 = CommentSpecs.authorFirstnameEquals(params.getAuthorFirstname());
Specification spec2 = CommentSpecs.authorLastnameEquals(params.getAuthorLastname());
Specification spec3 = CommentSpecs.ratingGreaterThan(params.getRating());
Specification spec = Specifications.where(spec1).or(spec2).or(spec3);
return repository.findAll(spec, pageable);
}
}
我使用文本编辑器编写了代码,因此需要修改.但我认为重点很容易发现
I have written the code using a text editor, so it needs a revision. But I think the main point is easy to spot
这篇关于在 spring 数据 jpa 存储库中通过许多可选参数搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!