首頁 > 軟體

Spring Data JPA系列QueryByExampleExecutor使用詳解

2022-09-30 14:02:07

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的資料請關注it145.com其它相關文章!


IT145.com E-mail:sddin#qq.com