JPA高级条件查询用法

发表于 更新于 阅读次数：

摘要：使用jpa-spec进行高级组合条件查询的用法

使用的插件

jpa-spec

1
2
3
4
5
<dependency>
    <groupId>com.github.wenhao</groupId>
    <artifactId>jpa-spec</artifactId>
    <version>3.2.3</version>
</dependency>

简单条件组合

1
2
3
4
5
Specification<BaseUser> spec = Specifications.<BaseUser>and()
        .eq("prop1","value1")
        .like(,"prop2","%value2%")
        .build();
List<BaseUser> list = baseUserRepository.findAll(spec);

复杂条件组合

1
2
3
4
5
6
7
8
9
10
11
12
13
Specification<BaseUser> spec1 = Specifications.<BaseUser>or()
  .eq("prop","value")
  .eq("prop","value")
  .build();
Specification<BaseUser> spec2 = Specifications.<BaseUser>or()
  .eq("prop","value")
  .eq("prop","value")
  .build();
Specification<BaseUser> spec0 = Specifications.<BaseUser>and()
  .predicate(spec1)
  .predicate(spec2)
  .build();
List<BaseUser> list = baseUserRepository.findAll(spec0);

解决条件in元素大于1000个时报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// 将大集合拆分成多个子集合
private static <T> List<List<T>> handleSubList(List<T> strList, int size) {
  List<List<T>> result = new ArrayList<>();
  List<T> subList = null;
  for (int i = 0, subIndex = 0; i < strList.size(); i++, subIndex++) {
    if (subIndex == size) {
      subIndex = 0;
    }
    if (subIndex == 0) {
      subList = new ArrayList<>();
      result.add(subList);
    }
    subList.add(strList.get(i));
  }
  return result;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// 大于1000个元素的集合
List<String> userIds = ....
List<List<String>> subLists = handleSubList(userIds, 1000);
PredicateBuilder<BaseUser> or = Specifications.<BaseUser>or();
for (List<String> subList : subLists) {
  or.in("userId", subList);
}
// 构造in条件
Specification<BaseUser> userIdSpec = or.build();
Specification<BaseUser> spec = Specifications.<BaseUser>and()
  .like(StringUtils.isNotBlank(account), "account", "%" + account + "%")
  .like(StringUtils.isNotBlank(userCode), "userCode", "%" + userCode + "%")
  .like(StringUtils.isNotBlank(realName), "realName", "%" + realName + "%")
  .predicate(userIdSpec)
  .ne("isDelete", 1)
  .build();
Page<BaseUser> page = baseUserService.queryListForPage(spec, pageable);