Spring Data JPA 与 QueryDSL 集成并支持动态参数
大约 2 分钟
提示
Spring Data JPA 与 QueryDSL 集成并支持动态参数
1. pom.xml配置
<properties>
<querydsl.version>5.0.0</querydsl.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
<optional>true</optional>
<classifier>jakarta</classifier>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
<classifier>jakarta</classifier>
</dependency>
</dependencies>
2. 启用配置
@EnableWebMvc
@EnableSpringDataWebSupport
@EnableJpaRepositories
@EnableTransactionManagement
@EnableJpaAuditing
3. 使用
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
UserRepository repository;
@RequestMapping(value = "/", method = RequestMethod.GET)
public ResponseEntity<Slice<User>> index(@QuerydslPredicate(root = User.class) Predicate predicate,
Pageable pageable, @RequestParam MultiValueMap<String, String> parameters) {
// 这里使用了动态参数查询,
// 查询参数与请求参数都不为集合 =, 查询参数为集合且请求参数不为集合 contains, 查询参数不为集合且请求参数为集合 in
return ResponseEntity.ok(repository.findAll(predicate, pageable));
}
@RequestMapping(value = "/page", method = RequestMethod.GET)
public ResponseEntity<Slice<User>> page(@RequestParam(name = "name", required = false) String name, Pageable pageable) {
// 常见的2次查询分页. count + query
return ResponseEntity.ok(repository.findByNameLike(name, pageable));
}
@RequestMapping(value = "/offset", method = RequestMethod.GET)
public ResponseEntity<Window<User>> offset(@RequestParam(name = "name", required = false) String name,
@RequestParam(name = "offset", required = false) Long offset) {
// 单词查询分页, 返回结果会展示是否存在下一行, 通过 Top3 + 排序, 查询4条数据确认是否存在下一行, 且跳过offset数据
return ResponseEntity.ok(repository.findTop3ByNameLike("%"+name+"%",
ScrollPosition.offset(Objects.isNull(offset) ? 0 : offset),
Sort.by("name")));
}
@RequestMapping(value = "/keyset", method = RequestMethod.GET)
public ResponseEntity<Window<User>> keyset(@RequestParam(name = "name", required = false) String name) {
// 单词查询分页, 返回结果会展示是否存在下一行, 通过 Top3 + 排序, 查询4条数据确认是否存在下一行, 且跳过keyset数据
WindowIterator<User> users =
WindowIterator.of(position -> repository.findTop3ByNameLikeOrderByName("%"+name+"%", position))
.startingAt(ScrollPosition.keyset());
for (WindowIterator<User> it = users; it.hasNext(); ) {
User u = it.next();
}
return ResponseEntity.ok(repository.findTop3ByNameLikeOrderByName("%"+name+"%",
ScrollPosition.keyset()));
}
}
repository:
@Repository
public interface UserRepository extends JpaRepository<User, Long>,
JpaSpecificationExecutor<User>,
QuerydslPredicateExecutor<User> ,
QuerydslBinderCustomizer<QUser> {
@Override
default void customize(QuerydslBindings bindings, QUser user) {
// 自定义web传参过滤绑定规则
bindings.bind(user.name).first(StringExpression::contains);
bindings.bind(String.class)
.first((StringPath path, String value) -> path.containsIgnoreCase(value));
}
Slice<User> findByNameLike(String name, Pageable pageable);
Window<User> findTop3ByNameLike(String name, ScrollPosition position, Sort sort);
Window<User> findTop3ByNameLikeOrderByName(String name, ScrollPosition position);
}