springdata jpa使用Example快速實現(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)是一種用戶界面友好的查詢技術。 它允許動態(tài)創(chuàng)建查詢,并且不需要編寫包含字段名稱的查詢。 實際上,按示例查詢不需要使用特定的數(shù)據(jù)庫的查詢語言來編寫查詢語句。
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
使用
創(chuàng)建實體映射:
@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查詢,默認情況下會忽略空值,官方文檔也有說明:
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);
}
自定匹配器規(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%]
補充
官方創(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 | 默認(大小寫敏感) |
| 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. 在默認情況下(沒有調(diào)用withIgnoreCase())都是大小寫敏感的。
2. api之中還有個regex,但是我在mysql下測試報錯,不了解具體作用。
總結(jié)
通過在使用springdata jpa時可以通過Example來快速的實現(xiàn)動態(tài)查詢,同時配合Pageable可以實現(xiàn)快速的分頁查詢功能。
對于非字符串屬性的只能精確匹配,比如想查詢在某個時間段內(nèi)注冊的用戶信息,就不能通過Example來查詢
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關文章
Java實戰(zhàn)之基于TCP實現(xiàn)簡單聊天程序
這篇文章主要為大家詳細介紹了如何在Java中基于TCP實現(xiàn)簡單聊天程序,文中示例代碼介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下2022-03-03
IntelliJ IDEA連接MySQL數(shù)據(jù)庫詳細圖解
今天小編就為大家分享一篇關于intellij idea連接mysql數(shù)據(jù)庫詳細圖解,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2018-10-10
深入理解Java SpringCloud Ribbon 負載均衡
Ribbon是一個客戶端負載均衡器,它提供了對HTTP和TCP客戶端的行為的大量控制。這篇文章主要介紹了SpringCloud Ribbon 負載均衡的實現(xiàn),感興趣的小伙伴們可以參考一下2021-09-09
Java Integer[]和int[]互相轉(zhuǎn)換方式
這篇文章主要介紹了Java Integer[]和int[]互相轉(zhuǎn)換方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12

