个人博客

1、Specifications进行查询

在一些查询条件不固定的场景下，我们需要动态去组装查询条件。在Spring Data JPA中可以通过 JpaSpecificationExecutor 接口查询。相比JPQL,其优势是类型安全,更加的面向对象。

1.1、创建Repository

需要继承 JpaSpecificationExecutor 接口。

1
2
3
public interface SpecificationRepository extends JpaRepository<Actor, Long>, JpaSpecificationExecutor<Actor> {

}

1.2、Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
@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、测试

1
2
3
4
5
6
7
8
9
10
11
12
13
@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

1
2
3
public interface ExampleMatcherRepository extends JpaRepository<Actor, Long> {
    
}

2.2、Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@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、测试

1
2
3
4
5
6
7
8
9
10
11
12
13
@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));
    }
}

代码地址