java - 为 JPA 和 Spring boot 实现搜索功能
问题描述
我正在尝试为数据库表实现正确的搜索功能。我尝试了这种方法:
控制器:
@GetMapping
public Page<TransactionDTO> find(TransactionFilterDTO filter, Pageable page) {
return searchRepository
.findTransactionsByFilter(mapper.toFilter(filter), page)
.map(mapper::toDTO);
}
归档 DTO:
public class TransactionFilterDTO {
private String name;
private Integer id;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)
private LocalDateTime from;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)
private LocalDateTime to;
... // getters and setter
}
搜索实现:
@Repository
public class TransactionSearchRepositoryImpl implements TransactionSearchRepository {
@Autowired
private TransactionRepository transactionRepository;
@Autowired
private TransactionSpecification specification;
@Override
public Page<Transaction> findTransactionsByFilter(TransactionFilter filter, @Nullable Pageable page) {
List<Transaction> transactions = transactionRepository
.findAll(specification.getFilter(filter));
int totalCount = transactions.size();
if(page != null) {
transactions = transactions
.stream()
.skip(page.getOffset())
.limit(page.getPageSize())
.collect(Collectors.toList());
}
return new PageImpl<>(transactions, page, totalCount);
}
}
存储库:
public interface TransactionSearchRepository {
Page<Transaction> findTransactionsByFilter(TransactionFilter filter, Pageable page);
}
有没有更好的方法来实现搜索功能?在我看来,这个解决方案非常难看。
解决方案
我可以推荐几种方法来解决这样的任务:
1)简单但不灵活:只需根据您的过滤器属性(name
, from
, to
)在控制器中使用请求参数并在您的存储库中准备相应的查询,例如:
控制器:
@GetMapping("/q")
public List<ResponseDto> getAllByQuery(
@RequestParam(value = "name", required = false) String name,
@RequestParam(value = "from", required = false) @DateTimeFormat(iso = ISO.DATE) LocalDate from,
@RequestParam(value = "to", required = false) @DateTimeFormat(iso = ISO.DATE) LocalDate to,
Pageable pageable
) {
return service.getByQuery(name, from, to, pageable);
}
服务:
public Page<ResponseDto> getByQuery(String name, LocalDate from, LocalDate to, Pageable pageable) {
return repo.getByQuery(name, from, to, pageable).map(mapper::toResponseDto);
}
存储库:
@Query("select m from MyEntity m where " +
"(?1 is null or upper(m.name) like concat('%', upper(?1), '%')) " +
"and (?2 is null or m.createdAt >= ?2) " +
"and (?3 is null or m.createdAt <= ?3)")
Page<MyEntity> getByQuery(String name, final LocalDate from, final LocalDate to, final Pageable pageable);
然后执行一个请求:
GET http://localhost:8080/q?name=john&from=2019-04-19&to=2019-04-19
2)使用QueryDsl。您应该将它添加到您的项目中(您可以在此处QuerydslPredicateExecutor
找到详细信息),从和扩展您的存储库,并QuerydslBinderCustomizer
为其添加一些“调整”:
public interface MyEntityRepo extends JpaRepository<MyEntity, Integer>, QuerydslPredicateExecutor<MyEntity>, QuerydslBinderCustomizer<QMyEntity> {
@Override
default void customize(@NonNull QuerydslBindings bindings, @NonNull QMyEntity entity) {
// Make case-insensitive 'like' filter for all string properties
bindings.bind(String.class).first((SingleValueBinding<StringPath, String>) StringExpression::containsIgnoreCase);
// Add 'between' and 'greater or equal' filter date property
bindings.bind(entity.createdAt).all((path, value) -> {
Iterator<? extends LocalDate> it = value.iterator();
LocalDate from = it.next();
if (value.size() >= 2) {
LocalDate to = it.next();
return Optional.of(path.between(from, to)); // between
} else {
return Optional.of(path.goe(from)); // greater than or equal
}
});
}
添加服务方法:
public Page<ResponseDto> getAllByQueryDsl(Predicate predicate, Pageable pageable) {
return repo.findAll(predicate, pageable).map(mapper::toResponseDto);
}
添加控制器方法:
@GetMapping("/query-dsl")
public Page<ResponseDto> getAllByQueryDsl(
@QuerydslPredicate(root = MyEntity.class, bindings = MyEntityRepo.class) Predicate predicate,
Pageable pageable
) {
return service.getAllByQueryDsl(predicate, pageable);
}
@DateTimeFormat
并为您的实体的“日期”属性添加注释:
@Entity
public class MyEntity {
// ...
@DateTimeFormat(iso = ISO.DATE) private LocalDate createdAt;
}
然后你可以执行这样的请求:
GET http://localhost:8080/query-dsl?name=john&createdAt=2019-04-15&createdAt=2019-04-19
其中第一个日期是 'from' 参数,第二个日期是 'to' 参数。如果您只使用一个日期 - 它将是“来自”参数(大于或等于)。
3) 使用规范-arg-resolver库。将其添加到您的项目中(请参阅说明:1和2),然后从以下位置扩展您的存储库JpaSpecificationExecutor
:
public interface MyEntityRepo extends JpaRepository<MyEntity, Integer>, JpaSpecificationExecutor<MyEntity> {}
将这样的方法添加到您的控制器:
@GetMapping("/specification")
public Page<ResponseDto> getAllBySpecification(
@And({
@Spec(path = "name", spec = LikeIgnoreCase.class),
@Spec(path = "createdAt", params = "from", spec = GreaterThanOrEqual.class),
@Spec(path = "createdAt", params = "to", spec = LessThanOrEqual.class)
}) Specification<MyEntity> specification,
Pageable pageable
) {
return service.getAllBySpecification(specification, pageable);
}
更新您的服务:
public Page<ResponseDto> getAllBySpecification(final Specification<MyEntity> specification, final Pageable pageable) {
return repo.findAll(specification, pageable).map(mapper::toResponseDto);
}
然后请求您的数据:
GET http://localhost:8080/specification?name=john&from=2019-04-10&to=2019-04-19
4)手动构建规范:
创建一个过滤器类:
@Data
public class MyFilter implements Specification<MyEntity> {
private String name;
@DateTimeFormat(iso = ISO.DATE) private LocalDate from;
@DateTimeFormat(iso = ISO.DATE) private LocalDate to;
@Override
public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
List<Predicate> predicates = new ArrayList<>();
if (name != null) predicates.add(builder.like(builder.upper(root.get("name")), "%" + name.toUpperCase() + "%"));
if (from != null) predicates.add(builder.greaterThanOrEqualTo(root.get("createdAt"), from));
if (to != null) predicates.add(builder.lessThanOrEqualTo(root.get("createdAt"), to));
return builder.and(predicates.toArray(new Predicate[0]));
}
}
创建控制器方法:
@GetMapping("/filter")
public Page<ResponseDto> getAllByMyFilter(MyFilter filter, Pageable pageable) {
return service.getAllBySpecification(filter, pageable);
}
然后运行请求:
GET http://localhost:8080/filter?name=john&from=2019-04-10&to=2019-04-19
推荐阅读
- html - 菜单不会出现在视差图像之上
- angular - 尝试在 Angular 6 中使用对象 Object 作为数组
- mongodb - golang 中的聚合查询
- java - Spring RefreshableKeycloakSecurityContext 内存泄漏
- slug - 将 slug 数组中的 '-' 替换为 '/'
- rest - 在 RESTful API 中表示资源
- xcode - 为什么新的 Xcode 版本会警告在块内使用 self
- javascript - VScode Intellisense 显示不在 JSdoc 上的定义
- sql - sql group_concat 和在哪里得到倒置的结果
- android - Style.xml 显示错误