Spring Data JPA系列QueryByExampleExecutor使用详解
目录
- 1、QueryByExampleExecutor用法
- 1.1 介绍
- 1.2 QueryByExampleExecutor接口
- 1.3 QueryByExampleExecutor实践
- 1.4 Example语法详解
- 1.5 ExampleMatcher语法分析
- 2、ExampleMatcher语法暴露常用方法
- 2.1 忽略大小写
- 2.2 NULL值的Property的处理方式
- 2.3 忽略某些属性列表,不参与查询过滤条件
- 2.4 字符串默认的匹配规则
- 3、实践出真理
- 3.1 AND查询
- 3.2 OR 查询
- 3.3 忽略大小写查询
- 3.3.1 忽略大小写 不指定属性
- 3.4 NULL值的处理
- 3.4.1 NULL属性值作为查询条件
- 3.4.2 忽略(实体类中)NULL属性
- 3.5 忽略某些属性不做筛选
- 3.6 字符串匹配规则
- 3.6.1 DEFAULT和EXACT 相等
- 3.6.2 STARTING和ENDING 模糊查询【开始匹配(?1 + %) 和 结束匹配(% + ?1 )) 】
- 3.6.3 Containing 包含模糊匹配【% + ?1 + %】
1、QueryByExampleExecutor用法
在前面章节中,我们介绍了DMQ 和 @Query两种查询方法,除此之外,还有QueryByExampleExecutor查询方法。
1.1 介绍
QueryByExampleExecutor是一种用户友好的查询技术,具有简单的接口,它允许动态创建,并且不需要填写包含字段名称的查询。
1.2 QueryByExampleExecutor接口
public interface QueryByExampleExecutor<T> { // 根据实体查询条件、查找一个对象 <S extends T> Optional<S> findOne(Example<S> example); // 根据实体查询条件、查询一批对象 <S extends T> Iterable<S> findAll(Example<S> example); // 根据实体查询条件并排序、查询一批对象 <S extends T> Iterable<S> findAll(Example<S> example, Sort sort); // 根据实体查询条件并分页,查询一批对象 <S extends T> Page<S> findAll(Example<S> example, Pageable pageable); // 根据实体查询条件、查询符合条件的对象个数 <S extends T> long count(Example<S> example); // 根据实体查询条件、判断是否有符合条件的对象 <S extends T> boolean exists(Example<S> example); // 根据实体查询条件、判断是否有符合条件的对象 <S extends T, R> R findBy(Example<S> example, Function<FluentQuery.FetchableFluentQuery<S>, R> queryFunction); }
1.3 QueryByExampleExecutor实践
第一步 :创建User实体和UserAddress实体
// User表 @Data @Entity @NoArgsConstructor @AllArgsConstructor @Builder @ToString(exclude = "address") public class User { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Integer id; private String name; private String email; private Integer age; private LocalDateTime createTime; private LocalDateTime updateTime; @OneToMany(mappedBy = "user",fetch = FetchType.LAZY) private List<UserAddress> address; } // Address表 @Entity @Data @Builder @AllArgsConstructor @NoArgsConstructor @ToString(exclude = "user") public class UserAddress { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long id; private String address; @ManyToOne(cascade = CascadeType.ALL) private User user; }
第二步: 编写DAO层,JpaRepository已经继承QueryByExampleExceutor
public interface UserAddressRepo extends JpaRepository<UserAddress,Integer> { }
第三步:测试
@Test public void test01 () { User user = User.builder() .name("jack") .email("123456@126.com") .age(20) .build(); userAddressRepo.saveAll(Lists.newArrayList(UserAddress.builder() .address("shanghai").user(user).build(),UserAddress.builder() .address("beijing").user(user).build())); } @Test public void testQBEE() throws JsonProcessingException { User user = User.builder() .name("jack") .age(20) .email("12345") .build(); UserAddress userAddress = UserAddress.builder() .address("shanghai") .user(user) .build(); ObjectMapper objectMapper = new ObjectMapper(); // 创建匹配器,构建动态查询条件 ExampleMatcher exampleMatcher = ExampleMatcher.matching() .withMatcher("user.email",ExampleMatcher.GenericPropertyMatchers.startsWith()) .withMatcher("address",ExampleMatcher.GenericPropertyMatchers.startsWith()); Page<UserAddress> u = userAddressRepo.findAll(Example.of(userAddress,exampleMatcher), PageRequest.of(0,2)); System.out.println(objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(u)); }
一开始写这个代码的时候,我也比较懵逼, Example是什么?ExampleMatcher是什么? 下面我一一介绍。
1.4 Example语法详解
首先:我们先看Example的源码
public interface Example<T> { static <T> Example<T> of(T probe) { return new TypedExample<>(probe, ExampleMatcher.matching()); } static <T> Example<T> of(T probe, ExampleMatcher matcher) { return new TypedExample<>(probe, matcher); } T getProbe(); ExampleMatcher getMatcher(); @SuppressWarnings("unchecked") default Class<T> getProbeType() { return (Class<T>) ProxyUtils.getUserClass(getProbe().getClass()); } }
- probe:实际实体类,即查询条件的封装类(又可以理解为查询条件参数)
- ExampleMatcher :匹配器,匹配特定字段的匹配规则。
- Example:由probe 和 ExampleMatcher租车,由于创建查询,即组合查询参数和参数的匹配规则。
创建Example的两个方法 :
- static Example of(T probe):需要一个实体参数,即查询条件。而里面的ExampleMatcher采用默认的ExamoleMatcher.matching(); 表示忽略NULL,所有字段采用精准匹配
- static Example of(T probe, ExampleMatcher matcher):需要两个参数构建Example,也就表示ExampleMatcher自由组合规则,正如我们上面的测试用例里面的代码一样。
1.5 ExampleMatcher语法分析
上图是ExampleMatcher向外暴露的方法,我们只要关心返回值为ExampleMatcher类型的方法。
其中有三个方法我们需要注意一下:
static ExampleMatcher matching() { return matchingAll(); } static ExampleMatcher matchingAll() { return new TypedExampleMatcher().withMode(MatchMode.ALL); }
上述的这两种方法表达的意思是一样的。两者采用的都是MatcheMode.ALL的模式,即AND模式,生成的SQL如下:
Hibernate: select count(useraddres0_.id) as col_0_0_ from user_address useraddres0_ inner join user user1_ on useraddres0_.user_id=user1_.id where (useraddres0_.address like ? escape ?) and user1_.name=? and (user1_.email like ? escape ?) and user1_.age=20
可以看到,这些查询条件都是AND的关系。再看另外一种方法
static ExampleMatcher matchingAny() { return new TypedExampleMatcher().withMode(MatchMode.ANY); }
当前方法与上面两个方法不一样的地方在于:第三个MatchMode.Any,表示查询条件是or的关系
Hibernate: select count(useraddres0_.id) as col_0_0_ from user_address useraddres0_ inner join user user1_ on useraddres0_.user_id=user1_.id where useraddres0_.address like ? escape ? or user1_.name=? or user1_.email like ? escape ? or user1_.age=20
以上就是初始化ExampleMatcher实例的方法,你在运用中需要注意and 和 or的关系
2、ExampleMatcher语法暴露常用方法
2.1 忽略大小写
// 哪些属性的paths忽略大小写,可以指定多个参数 ExampleMatcher withIgnoreCase(String... propertyPaths); // 提供一个默认的实现方法,忽略大小写 default ExampleMatcher withIgnoreCase() { return withIgnoreCase(true); } // 默认忽略大小写的方式,默认false ExampleMatcher withIgnoreCase(boolean defaultIgnoreCase);
2.2 NULL值的Property的处理方式
暴露的Null值处理方式如下所示:
ExampleMatcher withNullHandler(NullHandler nullHandler);
NullHandler枚举值如下所示:INCLUDE(包括)、IGNORE(忽略),
enum NullHandler { INCLUDE, IGNORE }
需要注意的是: 标识作为条件的实体对象中,一个属性值(条件值)为NULL时,是否参与过滤;
当该选项值是INCLUDE时,标识仍参与过滤,会匹配数据库表中该字段值是NULL的记录;
若为IGNORE值,表示不参与过滤;
// 把(实体类中)NULL属性值作为查询条件 default ExampleMatcher withIncludeNullValues() { return withNullHandler(NullHandler.INCLUDE); } // 提供一个默认实现方法,忽略(实体类中)NULL属性 default ExampleMatcher withIgnoreNullValues() { return withNullHandler(NullHandler.IGNORE); }
我们来看一下,把(实体类中)NULL属性值作为查询条件使用,执行的SQL如下所示:
Hibernate: select count(useraddres0_.id) as col_0_0_ from user_address useraddres0_ inner join user user1_ on useraddres0_.user_id=user1_.id where useraddres0_.id is null or useraddres0_.address like ? escape ? or user1_.name=? or user1_.email like ? escape ? or user1_.id is null or user1_.age=20
2.3 忽略某些属性列表,不参与查询过滤条件
// 忽略某些属性(可以是多个),不参与查询过滤条件 ExampleMatcher withIgnorePaths(String... ignoredPaths);
2.4 字符串默认的匹配规则
ExampleMatcher withStringMatcher(StringMatcher defaultStringMatcher);
默认字符串的匹配方式有以下几种 ,如下所示:
enum StringMatcher { DEFAULT, EXACT, STARTING, ENDING, CONTAINING, REGEX; }
DEFAULT:默认,作用和EXACT一样
EXACT:相等
STARTING:开始匹配
ENDING:结束匹配
CONTAINING:包含、模糊匹配
REGEX:正则表达式
使用方法如下
withStringMatcher(ExampleMatcher.StringMatcher.ENDING)
或指定某些字符串属性匹配规则
ExampleMatcher withMatcher(String propertyPath, GenericPropertyMatcher genericPropertyMatcher);
3、实践出真理
就从上面介绍的方法,我们手动练习一下。
新建一张Dog表
@Data @Entity @AllArgsConstructor @NoArgsConstructor @Table(name = "tb_dog") public class Dog { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(columnDefinition = "int(11) NOT NULL COMMENT '主键' ") private Integer id; @Column(columnDefinition = "varchar(30) DEFAULT '' COMMENT '宠物名'") private String name; @Column(columnDefinition = "int(11) DEFAULT NULL COMMENT '年龄'") private Integer age; }
3.1 AND查询
解释:根据当前dog对象的属性值作为查询条件去查询
@Test public void testBy01(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .build(); // AND 查询 ExampleMatcher matcher = ExampleMatcher.matching(); //ExampleMatcher.matchingAll() 也可以 System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
Hibernate: select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_ from tb_dog dog0_ where dog0_.name=? and dog0_.age=2
3.2 OR 查询
解释:根据当前dog对象的属性值作为查询条件去查询
@Test public void testBy02(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .build(); // OR 查询 ExampleMatcher matcher = ExampleMatcher.matchingAny(); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_ from tb_dog dog0_ where dog0_.name=? or dog0_.age=2
3.3 忽略大小写查询
解释:指定"name"属性忽略大小写
@Test public void testBy03(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .build(); ExampleMatcher matcher = ExampleMatcher.matching() .withIgnoreCase("name"); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_ from tb_dog dog0_ where lower(dog0_.name)=? and dog0_.age=2
在Dog表中添加type字段
@Column(columnDefinition = "varchar(20) DEFAULT NULL COMMENT '种类'") private String type;
3.3.1 忽略大小写 不指定属性
解释:不指定属性,默认为所有查询字符串条件加上忽略大小写条件
@Test public void testBy04(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .type("L") .build(); ExampleMatcher matcher = ExampleMatcher.matching() .withIgnoreCase(); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_, dog0_.type as type4_3_ from tb_dog dog0_ where lower(dog0_.name)=? and lower(dog0_.type)=? and dog0_.age=2
3.4 NULL值的处理
3.4.1 NULL属性值作为查询条件
解释:把(实体类中)NULL属性值作为查询条件使用
@Test public void testBy05(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .type("L") .build(); ExampleMatcher matcher = ExampleMatcher.matching() .withIgnoreCase() .withIncludeNullValues(); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_, dog0_.type as type4_3_ from tb_dog dog0_ where lower(dog0_.type)=? and (dog0_.id is null) and dog0_.age=2 and lower(dog0_.name)=?
3.4.2 忽略(实体类中)NULL属性
解释:把(实体类中)NULL属性值忽略
@Test public void testBy06(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .type("L") .build(); ExampleMatcher matcher = ExampleMatcher.matching() .withIgnoreNullValues(); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_, dog0_.type as type4_3_ from tb_dog dog0_ where dog0_.name=? and dog0_.type=? and dog0_.age=2
3.5 忽略某些属性不做筛选
解释:把(实体类中)某些属性忽略掉,不做筛选
@Test public void testBy07(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .type("L") .build(); // 忽略掉"name" 和 "type"两个属性 ExampleMatcher matcher = ExampleMatcher.matching() .withIgnorePaths("name","type"); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_, dog0_.type as type4_3_ from tb_dog dog0_ where dog0_.age=2
3.6 字符串匹配规则
3.6.1 DEFAULT和EXACT 相等
解释:把(实体类中)所有字符串属性匹配规则设置为 EXACT (相等)
@Test public void testBy08(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .type("L") .build(); ExampleMatcher matcher = ExampleMatcher.matching() // 字符串属性提供的匹配规则 EXACT相等 .withStringMatcher( ExampleMatcher.StringMatcher.EXACT); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_, dog0_.type as type4_3_ from tb_dog dog0_ where dog0_.name=? and dog0_.age=2 and dog0_.type=?
3.6.2 STARTING和ENDING 模糊查询【开始匹配(?1 + %) 和 结束匹配(% + ?1 )) 】
解释:把(实体类中)所有字符串属性匹配规则设置为 STARTING/ENDING (模糊查询)
public void testBy09(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .type("L") .build(); ExampleMatcher matcher = ExampleMatcher.matching() // 设置为开始匹配 .withStringMatcher(ExampleMatcher.StringMatcher.STARTING); // 设置为结束匹配 //.withStringMatcher(ExampleMatcher.StringMatcher.ENDING); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_, dog0_.type as type4_3_ from tb_dog dog0_ where dog0_.age=2 and (dog0_.type like ? escape ?) and (dog0_.name like ? escape ?)
3.6.3 Containing 包含模糊匹配【% + ?1 + %】
解释:把(实体类中)所有字符串属性匹配规则设置为 Containing (包含模糊查询)
@Test public void testBy11(){ Dog dog = Dog.builder() .name("TIMI") .age(2) .type("L") .build(); ExampleMatcher matcher = ExampleMatcher.matching() // 包含模糊查询 .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING); System.out.println(dogRepo.findAll(Example.of(dog, matcher))); }
执行SQL结果如下所示:
select dog0_.id as id1_3_, dog0_.age as age2_3_, dog0_.name as name3_3_, dog0_.type as type4_3_ from tb_dog dog0_ where dog0_.age=2 and (dog0_.type like ? escape ?) and (dog0_.name like ? escape ?)
以上就是Spring Data JPA系列QueryByExampleExecutor使用详解的详细内容,更多关于Spring Data JPA QueryByExampleExecutor的资料请关注我们其它相关文章!