JPA使用SQL的一些總結(jié)

一. 概述

簡單的增刪改查使用 JPA 非常方便，復(fù)雜的 Where 條件我們也在 Specification 和 CriteriaBuilder 的基礎(chǔ)上封裝了 WhereBuilder，實現(xiàn)了類似 C# 的 LINQ 的寫法，但是在很多情況下是需要使用 SQL 的，而且很多情況下原生 SQL 效率高很多。
JPA 支持二種最基本的 SQL 方式，Native SQL 和 JPQL，JPQL 語法非常類似于SQL語法，但是是針對 Java 對象和實體。我更傾向原生 SQL，畢竟 SQL 是通用的標(biāo)準(zhǔn)，開發(fā)人員或多或少都是掌握 SQL 的。
以下是在 JPA 里使用 SQL 的一些總結(jié)，不同的情況下有不同的應(yīng)對方式。

二. @Query注解的方式

我們可以直接在 DAO 的接口里定義 SQL，以下列出了7種方式:

@Repository
public interface UserDao extends JpaRepository<UserEntity, String>, JpaSpecificationExecutor<UserEntity> {
    @Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
    Optional<UserEntity> test1(String name);

    @Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
    Optional<UserVm> test2(String name);

    @Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
    Optional<IUserVm> test3(String name);

    @Query("select new d1.dxdevices.iot.user.model.UserVm( u.name,u.id,u.remark) from UserEntity u where u.name like %?1%")
    Optional<UserVm> test4(String name);

    @Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
    Optional<Object> test5(String name);

    @Query(nativeQuery = true, value = "select * from d1_user where callback_url is null")
    Page<UserEntity> test6(Pageable pageable);

    @Query(nativeQuery = true, value = "select * from d1_user where callback_url is null",
            countQuery = "select count(*) from d1_user where callback_url is null")
    Page<UserEntity> test7(Pageable pageable);
}

1. test1

@Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
Optional<UserEntity> test1(String name);

使用原生 SQL 一定需要加上 nativeQuery = true, 動態(tài)參數(shù)使用 ?1這種問號加數(shù)字的方式，比較特殊的是字段和表名一定得用上數(shù)據(jù)庫真實的值，比如 UserEntity 對應(yīng)的表名是 d1_user,原生的 SQL 是不可以 select * from UserEntity。
還有一個就是駝峰的雙詞字段，需要換成下劃線的方式，比如

/**
  * API回調(diào)地址
*/
@Column(length = 255)
@Comment("API回調(diào)地址")
private String callbackUrl;

UserEntity 下的字段 callbackUrl 映射到表里面字段名就變成 callback_url了，原生的 SQL 是不可以 select callbackUrl from d1_user,而應(yīng)該是select callback_url from d1_user,總之原生 SQL 是可以直接拷貝到 Navicat 等 SQL 工具里直接執(zhí)行成功的。

2. test2

@Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
Optional<UserVm> test2(String name);

和 test1 的差別是test1返回的就是 UserEntity ，但是 test2 返回的是 UserVm，是我們自己定義的一個實體對象，它的字段和 UserEntity 部分字段一樣。這樣的情況很常見，我們需要查詢出數(shù)據(jù)轉(zhuǎn)換成我們定義的實體對象，而不是數(shù)據(jù)庫對應(yīng)的 Entity。
實際上這個 test2 是執(zhí)行失敗的，這樣是不行的，會報 Converter 錯誤，因為我們沒有定義映射關(guān)系，即使 UserVm 和 UserEntity 字段完全一樣也不行。

3. test3

@Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
Optional<IUserVm> test3(String name);

為了解決 test2 不行的問題，我們對應(yīng)有幾種解決方式，最簡單的就是把實體類換成 interface 就可以了。

public interface IUserVm {
    String getId();
    LocalDateTime getCreateTime();
    String getName();
    String getCallbackUrl();
    String getCallback_Url();
}

但是注意因為是原生 SQL ，IUserVm 里的 get 方法也必須和表里面的字段保持一致，參考上面，其中 getCallbackUrl() 就不可以，而 getCallback_Url() 才能獲取到值

4. test4

@Query("select new d1.dxdevices.iot.user.model.UserVm( u.name,u.id,u.remark) from UserEntity u where u.name like %?1%")
Optional<UserVm> test4(String name);

為了解決 test2 不行的問題,還有一種辦法，就是不用原生 SQL，使用 JPQL ，可以直接在 select 里面加上自定義實體類的構(gòu)造函數(shù)，注意必須把實體類的完整 package 寫全。這個時候表名和字段名又不能按數(shù)據(jù)庫表的來了，得按 Entity 對象定義的來了。

5. test5

@Query(nativeQuery = true, value = "select * from d1_user where name like %?1%")
Optional<Object> test5(String name);

更通用的方式，或者說最原始的方式，類似我們早期用 JDBC 的方式，返回的是 ResultSet，是一個 Object 的數(shù)組，然后由調(diào)用者自己去做映射，以下是調(diào)用 test5 的代碼

public void test5(){
  Optional<Object> optionalUserEntity = userDao.test5("ad");
  if (optionalUserEntity.isEmpty()) {
      return;
  }
  Object obj = optionalUserEntity.get();
  UserVm userVm = new UserVm(obj);
  System.out.println(userVm.getName());
}

返回的 Object 其實是一個 Object[] 數(shù)組，我們可以給 UserVm里增加一個構(gòu)造函數(shù)，參數(shù)是這個數(shù)組。

public UserVm(Object obj) {
  if (obj instanceof Object[] objs) {
      this.name = objs[0].toString();
      //......
  }
}

這里要注意的是，這個數(shù)組里對應(yīng)的元素的順序是按表里定義字段的順序，一點兒也不能錯

6. test6

@Query(nativeQuery = true, value = "select * from d1_user where callback_url is null")
Page<UserEntity> test6(Pageable pageable);

原生的 SQL 要支持分頁可以通過在 sql 語句里面加 limit offset 之類的關(guān)鍵字，但是更簡單的方式是使用 Pageable ，這個我們也一直在使用，也就是加上 Pageabele 參數(shù)后，JPA 會在 sql 后面自動加上 limit 和 offset，我相信切換一個其他類型的數(shù)據(jù)庫， JPA 會自動拼接特定的關(guān)鍵字。
另外為了返回 Page 類型的結(jié)構(gòu)體，需要查詢總數(shù)，JPA 會根據(jù)你的 sql 自動拼接出查詢總數(shù)的 sql 語句。

Hibernate: /* dynamic native SQL query */ select * from d1_user where callback_url is null limit ? offset ?
Hibernate: /* dynamic native SQL query */ select count(*) from d1_user

7. test7

@Query(nativeQuery = true, value = "select * from d1_user where callback_url is null",countQuery = "select count(*) from d1_user where callback_url is null")
Page<UserEntity> test7(Pageable pageable);

相比 test6 ，test7 增加了一個 countQuery,在當(dāng)前的例子里，這個寫不寫都沒有問題，但是在一些特殊的情況下，你的 sql 比較復(fù)雜的時候，有可能 JPA 無法準(zhǔn)確正確的自動拼接出查詢總數(shù)的 sql 語句，所以 JPA 提供了這個屬性來讓開發(fā)者自己定義查詢總數(shù)的 sql。

8. 動態(tài) sql

舉一個例子，我們根據(jù)用戶來查詢這個用戶名下的所有設(shè)備，假如傳遞過來的用戶是admin，則返回所有設(shè)備，否則就返回這個用戶下的設(shè)備。如果用原生 SQL 的方式則比較麻煩了。

@Query(nativeQuery = true, value = "select * from d1_device where name=?1")
Page<DeviceEntity> test8(String name,Pageable pageable);

如果按照以上的寫法是無法實現(xiàn)這個需求的。JPA 提供了一個 QueryRewriter 來實現(xiàn)這個功能，可惜是 JPA 3.0 才有的功能，而我們現(xiàn)在用的是 JPA 2.6，所以我沒有寫相關(guān)的例子，基本用法也很簡單，類似:

@Query(value = "select original_user_alias.* from SD_USER original_user_alias",nativeQuery = true, queryRewriter = MyQueryRewriter.class)
List<User> findByNativeQuery(String param);

然后定義一個 MyQueryRewriter

public class MyQueryRewriter implements QueryRewriter {
    @Override
    public String rewrite(String query, Sort sort) {
        if(query.contain("admin"){//在執(zhí)行查詢前根據(jù)條件修改sql
        return query.repalceAll("name=admin","1=1");
      }else{
        return query;
      }
    }
}

注意：以上代碼我沒有驗證，因為沒有 JPA3 的環(huán)境

9. 總結(jié)

最合適的方式我推薦的是 test1、test3、test6，也就是用 interface 的方式支持自定義實體類和用 Pageable 支持分頁。

三. 代碼的方式執(zhí)行原生 SQL

這種方式繁瑣一點，但是完全靠代碼來實現(xiàn)，類似 JDBC 的方式，是最靈活的，可以實現(xiàn)各種復(fù)雜的需求。

我們首先在 dao（Repository）定義一個 test8() 函數(shù)

@Repository
public interface UserDao extends JpaRepository<UserEntity, String>, JpaSpecificationExecutor<UserEntity> {
   //這里要做一個關(guān)聯(lián)查詢，關(guān)聯(lián)設(shè)備表和用戶表，但是不用 @Query注解
   List test8(String name);
}

正常我們是需要編寫一個類 實現(xiàn)這個接口，實現(xiàn)test8(),如下:

@Service
public class UserDaoService implements UserDao{
    @override
  List test8(String name){
    //......
  }
}

但是這樣寫是不合適的，因為如果這樣寫需要實現(xiàn)所有方法，包括 JpaRepository 和 JpaSpecificationExecutor 下有幾十個方法，很顯然這個不合適。

JPA 有一個約定，很好的解決這個問題，也就是在你定義一個實現(xiàn)類，類名是你定義的 Repository 類后加上后綴 Impl 就可以，而不需要顯式的實現(xiàn)接口，也就是 ** 不需要 ** 寫 implements UserDao ,如下

@Service
public class UserDaoImpl {
    private final Logger log = LoggerFactory.getLogger(this.getClass());
    private final EntityManager manager;

    public UserDaoImpl(EntityManager manager) {
        this.manager = manager;
    }

    public List test8(String name) {
        String nativeSql = "SELECT u.name,d.remark FROM d1_user u,d1_device d WHERE d.user_id=u.id and u.name like CONCAT('%', :name, '%')";
        Query query = manager.createNativeQuery(nativeSql);
        query.setParameter("name", name);
        List list = query.getResultList();
        //做一些相應(yīng)的處理轉(zhuǎn)換成我們需要的實體對象
        return list;
    }
}

看上去 UserDaoImpl 和 UserDao沒有任何關(guān)系，但是 JPA 執(zhí)行的時候會自動調(diào)用 UserDaoImpl 的 test8 方法，剩下就是開發(fā)者自己編寫 SQL 語句，可以設(shè)置參數(shù),冒號加名稱表示一個參數(shù)變量。
這里弄了一個關(guān)聯(lián)查詢，我們返回的是一個 List ，里面是一個 Object，需要把 Object 轉(zhuǎn)換成自定義的實體對象。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容