在spring data jpa存储库中通过许多可选参数进行搜索
在spring data jpa存储库中通过许多可选参数进行搜索
提问于 2019-10-05 00:19:23
所以我有表comment和author。我想建立复杂的搜索栏与许多可选的参数。我想过滤评论与可选的作者名字/姓氏和一些标志，如受欢迎(基于评论评级)。
由于我不知道如何使用spring data jpa存储库编写它，我一直在考虑用@Query注解将它写成原生查询，像这样的smth应该可以工作。
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编写它的选项？
例如，将来我计划添加更多的参数和分页。使用spring时，使用sql查询只需要1分钟，我会损失几个小时。
在这种情况下，有没有一些最佳实践？
回答 2
Stack Overflow用户
发布于 2019-10-05 04:56:21
我建议使用JpaSpecificationExecutor存储库方法findAll(Specification<T> spec, Pageable pageable)。此解决方案允许您使用相同的存储库和服务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> {
}Specifications: 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
}所有参数都可以为空。您可以设置仅需要的参数。如果参数为空，我们的规范将忽略该参数
服务：
@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);
}
}我已经使用文本编辑器编写了代码，因此需要对其进行修改。但我认为要点很容易被发现
Stack Overflow用户
发布于 2019-10-05 01:28:36
您可以使用jpa或criteriabuilder，如果您更喜欢使用JPA，您可以这样做：
@Query("select s from SomeEntity s "
+ "where (s.description is null or s.description = :description) "
+ "and (s.name is null or s.name = :name) "
List<SomeEntity> find(String description, String name);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58240024
复制相关文章
相似问题
腾讯云开发者
