springdata jpa使用Example快速實現動態查詢功能

Posted on by

Example官方介紹

Query by Example (QBE) is a user-friendly querying technique with a simple interface. It allows dynamic query creation and does not require to write queries containing field names. In fact, Query by Example does not require to write queries using store-specific query languages at all.

谷歌翻譯：

按例查詢（QBE）是一種用戶界面友好的查詢技術。 它允許動態創建查詢，並且不需要編寫包含字段名稱的查詢。 實際上，按示例查詢不需要使用特定的數據庫的查詢語言來編寫查詢語句。

Example api的組成

  • Probe：含有對應字段的實例對象。
  • ExampleMatcher：ExampleMatcher攜帶有關如何匹配特定字段的詳細信息，相當於匹配條件。
  • Example：由Probe和ExampleMatcher組成，用於查詢。

限制

  • 屬性不支持嵌套或者分組約束，比如這樣的查詢 firstname = ?0 or (firstname = ?1 and lastname = ?2)
  • 靈活匹配隻支持字符串類型，其他類型隻支持精確匹配

Limitations

1. No support for nested/grouped property constraints like firstname = ?0 or (firstname = ?1 and lastname = ?2)

2. Only supports starts/contains/ends/regex matching for strings and exact matching for other property types

使用

創建實體映射：

@Entity
@Table(name="t_user")
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name="username")
    private String username;
    @Column(name="password")
    private String password;
    @Column(name="email")
    private String email;
    @Column(name="phone")
    private String phone;
    @Column(name="address")
    private String address;
}

測試查詢

@Test
public void contextLoads() {
    User user = new User();
    user.setUsername("admin");
    Example<User> example = Example.of(user);
    List<User> list = userRepository.findAll(example);
    System.out.println(list);
}

打印的sql語句如下：

Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.address as address2_0_,
        user0_.email as email3_0_,
        user0_.password as password4_0_,
        user0_.phone as phone5_0_,
        user0_.username as username6_0_ 
    from
        t_user user0_ 
    where
        user0_.username=?

可以發現，試用Example查詢，默認情況下會忽略空值，官方文檔也有說明：

This is a simple domain object. You can use it to create an Example. By default, fields having null values are ignored, and strings are matched using the store specific defaults. Examples can be built by either using the of factory method or by using ExampleMatcher. Example is immutable.

在上面的測試之中，我們隻是隻是定義瞭Probe而沒有ExampleMatcher，是因為默認會不傳時會使用默認的匹配器。點進方法可以看到下面的代碼：

static <T> Example<T> of(T probe) {
    return new TypedExample(probe, ExampleMatcher.matching());
}
static ExampleMatcher matching() {
    return matchingAll();
}
static ExampleMatcher matchingAll() {
    return (new TypedExampleMatcher()).withMode(ExampleMatcher.MatchMode.ALL);
}

自定匹配器規則

@Test
public void contextLoads() {
    User user = new User();
    user.setUsername("y");
    user.setAddress("sh");
    user.setPassword("admin");
    ExampleMatcher matcher = ExampleMatcher.matching()
            .withMatcher("username", ExampleMatcher.GenericPropertyMatchers.startsWith())//模糊查詢匹配開頭，即{username}%
            .withMatcher("address" ,ExampleMatcher.GenericPropertyMatchers.contains())//全部模糊查詢，即%{address}%
            .withIgnorePaths("password");//忽略字段，即不管password是什麼值都不加入查詢條件
    Example<User> example = Example.of(user ,matcher);
    List<User> list = userRepository.findAll(example);
    System.out.println(list);
}

打印的sql語句如下：

select
    user0_.id as id1_0_,
    user0_.address as address2_0_,
    user0_.email as email3_0_,
    user0_.password as password4_0_,
    user0_.phone as phone5_0_,
    user0_.username as username6_0_ 
from
    t_user user0_ 
where
    (
        user0_.username like ?
    ) 
    and (
        user0_.address like ?
    )

參數如下：

2018-03-24 13:26:57.425 TRACE 5880 — [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] – [y%]
2018-03-24 13:26:57.425 TRACE 5880 — [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] – [%sh%]

補充

官方創建ExampleMatcher例子（1.8 lambda）

ExampleMatcher matcher = ExampleMatcher.matching()
  .withMatcher("firstname", match -> match.endsWith())
  .withMatcher("firstname", match -> match.startsWith());
}

StringMatcher 參數

Matching 生成的語句 說明
DEFAULT (case-sensitive) firstname = ?0 默認（大小寫敏感）
DEFAULT (case-insensitive) LOWER(firstname) = LOWER(?0) 默認（忽略大小寫）
EXACT (case-sensitive) firstname = ?0 精確匹配（大小寫敏感）
EXACT (case-insensitive) LOWER(firstname) = LOWER(?0) 精確匹配（忽略大小寫）
STARTING (case-sensitive) firstname like ?0 + ‘%’ 前綴匹配（大小寫敏感）
STARTING (case-insensitive) LOWER(firstname) like LOWER(?0) + ‘%’ 前綴匹配（忽略大小寫）
ENDING (case-sensitive) firstname like ‘%’ + ?0 後綴匹配（大小寫敏感）
ENDING (case-insensitive) LOWER(firstname) like ‘%’ + LOWER(?0) 後綴匹配（忽略大小寫）
CONTAINING (case-sensitive) firstname like ‘%’ + ?0 + ‘%’ 模糊查詢（大小寫敏感）
CONTAINING (case-insensitive) LOWER(firstname) like ‘%’ + LOWER(?0) + ‘%’ 模糊查詢（忽略大小寫）

說明：

1. 在默認情況下（沒有調用withIgnoreCase()）都是大小寫敏感的。

2. api之中還有個regex，但是我在mysql下測試報錯，不瞭解具體作用。

總結

通過在使用springdata jpa時可以通過Example來快速的實現動態查詢，同時配合Pageable可以實現快速的分頁查詢功能。

對於非字符串屬性的隻能精確匹配，比如想查詢在某個時間段內註冊的用戶信息，就不能通過Example來查詢

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：