1、Specifications进行查询
在一些查询条件不固定的场景下，我们需要动态去组装查询条件。在Spring Data JPA中可以通过 JpaSpecificationExecutor 接口查询。相比JPQL,其优势是类型安全,更加的面向对象。
1.1、创建Repository
需要继承 JpaSpecificationExecutor 接口。
public interface SpecificationRepository extends JpaRepository<Actor, Long>, JpaSpecificationExecutor<Actor> {
}1.2、Service
代码语言：javascript复制@Service
@Transactional
@Slf4j
public class SpecificationServiceImpl implements SpecificationService {
@Autowired
private SpecificationRepository specificationRepository;
@Override
public List<Actor> findBySpecification(Long id, Integer age, Integer page, Integer pageSize) {
Specification<Actor> specification = (Specification<Actor>) (root, criteriaQuery, criteriaBuilder) -> {
// 查询条件的集合
List<Predicate> list = new ArrayList<>();
// 条件1：id字段需要大于等于指定的id
list.add(criteriaBuilder.greaterThanOrEqualTo(root.get("id"), (id == null || id < 0) ? 0 : id));
// 条件2：如果指定了age，则需要相等
if(age != null && age > 0) {
// 字段需要和实体类中属性相同，而非表中字段
list.add(criteriaBuilder.equal(root.get("actorAge"), age));
}
// 转数组
Predicate[] predicates = new Predicate[list.size()];
list.toArray(predicates);
return criteriaBuilder.and(predicates);
};
// 指定排序和分页
Sort sort = new Sort(Sort.Direction.ASC, "id");
PageRequest pageRequest = PageRequest.of(page < 0 ? 0 : page, pageSize, sort);
Page<Actor> actorPage = specificationRepository.findAll(specification, pageRequest);
log.info("分页查询第:[{}]页,pageSize:[{}],共有:[{}]数据,共有:[{}]页", page, pageSize, actorPage.getTotalElements(), actorPage.getTotalPages());
List<Actor> actorListBySpecification = actorPage.getContent();
return actorListBySpecification;
}
}判断条件值是否存在，动态追加条件。 注意指定的字段是实体类中的属性名而非表中的字段名。
1.3、测试
代码语言：javascript复制@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class SpecificationTest {
@Autowired
private SpecificationService specificationService;
@Test
public void testSpecification() {
List<Actor> actorList = specificationService.findBySpecification(1L, null, 0, 10);
log.info(JSONUtil.toJsonPrettyStr(actorList));
}
}多次修改查询条件测试，可以看出查询条件是根据实际情况动态追加的。
2、ExampleMatcher进行查询
2.1、创建Repository
代码语言：javascript复制public interface ExampleMatcherRepository extends JpaRepository<Actor, Long> {
}2.2、Service
代码语言：javascript复制@Service
@Transactional
@Slf4j
public class ExampleMatcherServiceImpl implements ExampleMatcherService {
@Autowired
private ExampleMatcherRepository exampleMatcherRepository;
@Override
public List<Actor> findByExampleMatcher(String actorEmailPre, String actorNamePre, Integer page, Integer pageSize) {
Actor actor = new Actor();
actor.setActorEmail(actorEmailPre);
actor.setActorName(actorNamePre);
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("actorEmail", ExampleMatcher.GenericPropertyMatchers.startsWith())
.withMatcher("actorName", ExampleMatcher.GenericPropertyMatchers.startsWith())
.withIgnoreCase() // 忽略大小写，MYSQL模糊查询默认也会忽略大小写
.withIgnoreNullValues() // 忽略null值
// 默认会匹配所有字段作为查询条件，所以需要设置忽略哪些字段不作为条件匹配，即这些字段可以任意值
.withIgnorePaths("actorAge", "createTime");
Example<Actor> actorExample = Example.of(actor, matcher);
// 指定排序和分页
Sort sort = new Sort(Sort.Direction.ASC, "actorAge");
PageRequest pageRequest = PageRequest.of(page < 0 ? 0 : page, pageSize, sort);
Page<Actor> actorPage = exampleMatcherRepository.findAll(actorExample, pageRequest);
log.info("分页查询第:[{}]页,pageSize:[{}],共有:[{}]数据,共有:[{}]页", page, pageSize, actorPage.getTotalElements(), actorPage.getTotalPages());
List<Actor> actorListByExampleMatcher = actorPage.getContent();
return actorListByExampleMatcher;
}
}模糊查询匹配字符串。
2.3、测试
代码语言：javascript复制@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class ExampleMatcherTest {
@Autowired
private ExampleMatcherService exampleMatcherService;
@Test
public void testExampleMatcher() {
List<Actor> actorList = exampleMatcherService.findByExampleMatcher("EMAIL", "name", 0, 10);
log.info(JSONUtil.toJsonPrettyStr(actorList));
}
}代码地址
- github：https://github.com/senlinmu1008/jpa-action/tree/master/jpa-dynamic-query
- gitee：https://gitee.com/ppbin/jpa-action/tree/master/jpa-dynamic-query
