快捷導(dǎo)航

springdata jpa使用Example快速實(shí)現(xiàn)動態(tài)查詢功能

更新時間：2021年11月18日 11:37:11 作者：一號搬磚手

這篇文章主要介紹了springdata jpa使用Example快速實(shí)現(xiàn)動態(tài)查詢功能，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

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）是一種用戶界面友好的查詢技術(shù)。它允許動態(tài)創(chuàng)建查詢，并且不需要編寫包含字段名稱的查詢。實(shí)際上，按示例查詢不需要使用特定的數(shù)據(jù)庫的查詢語言來編寫查詢語句。

Example api的組成

Probe：含有對應(yīng)字段的實(shí)例對象。
ExampleMatcher：ExampleMatcher攜帶有關(guān)如何匹配特定字段的詳細(xì)信息，相當(dāng)于匹配條件。
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

使用

創(chuàng)建實(shí)體映射：

@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=?

可以發(fā)現(xiàn)，試用Example查詢，默認(rèn)情況下會忽略空值，官方文檔也有說明：

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，是因?yàn)槟J(rèn)會不傳時會使用默認(rèn)的匹配器。點(diǎn)進(jìn)方法可以看到下面的代碼：

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);
}

自定匹配器規(guī)則

@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 ?
    )

參數(shù)如下：

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%]

補(bǔ)充

官方創(chuàng)建ExampleMatcher例子（1.8 lambda）

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

StringMatcher 參數(shù)

Matching	生成的語句	說明
DEFAULT (case-sensitive)	firstname = ?0	默認(rèn)（大小寫敏感）
DEFAULT (case-insensitive)	LOWER(firstname) = LOWER(?0)	默認(rèn)（忽略大小寫）
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) + ‘%'	模糊查詢（忽略大小寫）