LIKE Queries in Spring JPA Repositories

使用 JPA 的 Repository 可以節省很多撰寫 SQL 的程式，及由DB取得資料後，轉成 Object 的作業。在這裡將以 Spring Boot 配置 Maria Database 與交易控制 ( Transaction ) 的 table : authority 來作測試。

首先再加入 4 個角色

INSERT INTO authority (id, name, description, isSystem, enabled) VALUES 
(3, 'HR', '人資', 1, 1),
(4, 'RD', '開發', 1, 1),
(5, 'it', '資訊技術', 1, 1),
(6, 'ISS', '資訊系統', 1, 1);

• LIKE Query Methods 範例

下面會舉例常用的SQL語法來與 JPA 作對照

Containing, Contains, IsContaining and Like (包含那些字元)

在 SQL 中對含有 s 的資料作查詢

SELECT * FROM authority WHERE name LIKE '%s%'

在 JPA 的 Respository 加入 Containing, Contains, IsContaining, Like

List<Authority> findByNameContaining(String name);
List<Authority> findByNameContains(String name);
List<Authority> findByNameIsContaining(String name);
List<Authority> findByNameLike(String name);

可以預期的結果應該是 3 筆, 所以驗證程式如下

@Test
@Transactional
void TestQueries() {
 List<Authority> userRoles = new ArrayList<Authority>();

 userRoles = authorityRepo.findByNameContaining("s");
 assertEquals(3, userRoles.size());
  
 userRoles = authorityRepo.findByNameContains("s");
 assertEquals(3, userRoles.size());

 userRoles = authorityRepo.findByNameIsContaining("s");
 assertEquals(3, userRoles.size());

 userRoles = authorityRepo.findByNameLike("%s%");
 assertEquals(3, userRoles.size()); 
}

要注意的是，findByNameLike 必需要使用通用符號( wildcard characters)  %  ，否則會找不到資料。

Not (相反)

上面的語法，都可以在前面加入 Not 來取得與它相反的資料，例如: NotContains, NotContaining, and NotLike

StartsWith ( 開始的字元為 )

在 SQL 中對開始字元為 A 的資料作查詢

SELECT * FROM authority WHERE name LIKE 'A%'

在 JPA 的 Respository 加入

List<Authority> findByNameStartsWith(String name);

可以預期的結果應該是 1 筆, 所以驗證程式如下

@Test
@Transactional
void TestQueries() {
 List<Authority> userRoles = new ArrayList<Authority>();

 userRoles = authorityRepo.findByNameStartsWith("A");
 assertEquals(1,userRoles.size());  
}

EndsWith (結束的字元為)

在 SQL 中對最後字元為 D 的資料作查詢

SELECT * FROM authority WHERE name LIKE '%D'

在 JPA 的 Respository 加入

List<Authority> findByNameEndsWith(String name);

可以預期的結果應該是 1 筆, 所以驗證程式如下

@Test
@Transactional
void TestQueries() {
 List<Authority> userRoles = new ArrayList<Authority>();

 userRoles = authorityRepo.findByNameEndsWith("D");
 assertEquals(1,userRoles.size());      
}

• 使用 @Query

如果 JPA 無法用語法來自動產生查詢資料時，還可以使用 @Query 來寫一般的 SQL 語法。

Ordered Parameters

@Query(value = "select * from authority a "
    + " where 1=1 "
    + " and name like ?1% "
    + " or description like ?2% "
    , nativeQuery = true)
List<Authority> findByLikeOrder(String name, String description);

測試程式

@Test
@Transactional
void TestQueries() {
 List<Authority> userRoles = new ArrayList<Authority>();

 userRoles = authorityRepo.findByLikeOrder("admins","資訊");
 assertEquals(3,userRoles.size());      
}

Named Parameters (用字元作參數導入)

@Query(value = "select * from authority a "
    + " where 1=1 "
    + " and name like %:name% "
    + " or description like %:description% "
    , nativeQuery = true)
List<Authority> findByLike(@Param("name") String name, @Param("description") String description);

注意要用 @Param 來將值導入到@Query 的 SQL 參數裡，如: @Param("name") 會將值導入到  %:name%  。這種方式是比較符合閱讀習慣，尤其當要傳入的參數很多時。

To List<Map>

@Query(value = "select * from authority a "
    + " where 1=1 "
    + " and name like %:name% "
    + " or description like %:description% "
    , nativeQuery = true)
List<Map<String, Object>> findByLikeToList(@Param("name") String name, @Param("description") String description);

若將回饋(call back)的值改為 List<Map<String, Object>> 型態，JPA 也會自動轉換型態，很方便吧。

@Test
@Transactional
void TestQueries() {
 List<Map<String, Object>> userRoles = new ArrayList<Map<String, Object>>();

 userRoles = authorityRepo.findByLikeToList("admins","資訊");
 assertEquals(3,userRoles.size());      
}

參考:

https://www.baeldung.com/spring-jpa-like-queries