快捷導(dǎo)航

Spring Data JPA使用JPQL與原生SQL進行查詢的操作

更新時間：2021年06月15日 09:20:14 作者：pan_junbiao

這篇文章主要介紹了Spring Data JPA使用JPQL與原生SQL進行查詢的操作，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

1、使用JPQL語句進行查詢

JPQL語言（Java Persistence Query Language）是一種和SQL非常類似的中間性和對象化查詢語言，它最終會被編譯成針對不同底層數(shù)據(jù)庫的SQL語言，從而屏蔽不同數(shù)據(jù)庫的差異。

JPQL語言通過Query接口封裝執(zhí)行，Query 接口封裝了執(zhí)行數(shù)據(jù)庫查詢的相關(guān)方法。調(diào)用 EntityManager 的 Query、NamedQuery 及 NativeQuery 方法可以獲得查詢對象，進而可調(diào)用Query接口的相關(guān)方法來執(zhí)行查詢操作。

JPQL是面向?qū)ο筮M行查詢的語言，可以通過自定義的JPQL完成UPDATE和DELETE操作。JPQL不支持使用INSERT。對于UPDATE或DELETE操作，必須使用注解 @Modifying 進行修飾。

【示例】使用JPQL語言進行查詢

package com.pjb.jpauserdemo.dao; 
import com.pjb.jpauserdemo.entity.UserInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
 
/**
 * 用戶信息數(shù)據(jù)庫訪問接口
 * 使用JPQL語言
 * @author pan_junbiao
 **/
@Repository
public interface UserJpqlDao extends JpaRepository<UserInfo,Integer>
{
    /**
     * 根據(jù)用戶姓名，查詢用戶信息
     */
    @Query("SELECT u FROM UserInfo u WHERE u.userName = ?1")
    public UserInfo getUserInfoByName(String name);
 
    /**
     * 根據(jù)用戶姓名，模糊查詢用戶列表
     */
    @Query("SELECT u FROM UserInfo u WHERE u.userName like %:name%")
    public List<UserInfo> getUserListByName(String name);
 
    /**
     * 修改用戶姓名
     */
    @Modifying
    @Query("UPDATE UserInfo u SET u.userName = :name WHERE u.userId = :id")
    public int updateUserName(@Param("id")int userId, @Param("name")String userName);
}

2、使用原生SQL語句進行查詢

在使用原生SQL查詢時，也使用@Query注解。此時，nativeQuery參數(shù)需要設(shè)置為true。

【示例】使用原生SQL語句進行查詢

package com.pjb.jpauserdemo.dao;
import com.pjb.jpauserdemo.entity.UserInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
 
/**
 * 用戶信息數(shù)據(jù)庫訪問接口測試類
 * 使用原生SQL語言
 * @author pan_junbiao
 **/
@Repository
public interface UserSqlDao extends JpaRepository<UserInfo,Integer>
{
    /**
     * 根據(jù)用戶ID，獲取用戶信息
     */
    @Query(value = "SELECT * FROM tb_user WHERE user_id = :id",nativeQuery = true)
    public UserInfo getUserById(@Param("id")int userId);
 
    /**
     * 根據(jù)用戶姓名，模糊查詢用戶列表
     */
    @Query(value = "SELECT * FROM tb_user WHERE user_name LIKE %:userName%",nativeQuery = true)
    public List<UserInfo> getUserListByName(@Param("userName")String userName);
 
    /**
     * 修改用戶姓名
     */
    @Modifying
    @Query(value = "UPDATE tb_user SET user_name = :name WHERE user_id = :id",nativeQuery = true)
    public int updateUserName(@Param("id")int userId, @Param("name")String userName);
}

可以看到，@Query與@Modifying這兩個注解一起聲明，可以定義個性化更新操作。

Spring data jpa@query使用原生SQl，需要注意的坑

根據(jù)代碼來解說：

@Query(value = "select bill.id_ as id, bill.created_date as date, bill.no, lawyer_case .case_no as caseNo, " +
            "lawyer_case .case_name as caseName, customer.no as customerNo, customer.cn_name as customerName, " +
            "bill.total_expense_after_tax, bill.collected_money, bill.book_ticket_amount, bill.version " +
            "e1.name as creator, bill.status" +
            "from bill " +
            "left join lawyer_case on lawyer_case .case_no=bill.case_no " +
            "left join customer on customer.no=bill.customer_no " +
            "left join employee e1 on e1.id_=bill.creator " +
            "where IF (?1!='', customer_no=?1, 1=1) " +
            "and   IF (?2!='', case_no=?2, 1=1) " +
            "and   IF (?3!='', status=?3, 1=1) " +
            "and   IF (?4!='', creator'%',?4,'%')), 1=1) " +
            "and   create_by=?5 " +
            "ORDER BY ?#{#pageable} ",
            countQuery = "select count(*) " +
                    "from bill " +
                    "left join lawyer_case on lawyer_case .case_no=bill.case_no " +
                    "left join customer on customer.no=bill.customer_no " +
                    "left join employee e1 on e1.id_=bill.creator " +
                    "where IF (?1!='', customer_no=?1, 1=1) " +
                    "and   IF (?2!='', case_no=?2, 1=1) " +
                    "and   IF (?3!='', status=?3, 1=1) " +
                    "and   IF (?4!='', creator'%',?4,'%')), 1=1) " +
                    "and   create_by=?5 "+
                    "ORDER BY ?#{#pageable} ",
            nativeQuery = true)
    Page<Object[]> findAllBill(String customerNo, String caseNo, Integer status, String creator,
                               String createBy, Pageable pageable);

需要注意的方法有以下幾點：

1、From 不支持重命名.

2、返回的是一個page<Object[]>，數(shù)組中只保存了數(shù)據(jù)，沒有對應(yīng)的key，只能根據(jù)返回數(shù)據(jù)的順序，依次注入到DTO中。

3、對于使用分頁，需要：“ORDER BY ?#{#pageable}”，可以直接傳入一個pageable對象，會自動解析。

4、注意格式問題，很多時候就是換行的時候，沒有空格。

5、仔細(xì)對應(yīng)數(shù)據(jù)庫中表字段，很多時候報某個字段找不到，就是因為字段名寫錯，和數(shù)據(jù)庫中對應(yīng)不上。

6、這是解決使用微服務(wù)，大量的數(shù)據(jù)都需要遠(yuǎn)程調(diào)用，會降低程序的性能。

7、使用Pageabel作為參數(shù)的時候，去進行分頁。剛開始的時候，覺得還是一個可行的辦法，但是得注意的時候，當(dāng)需要排序的時候，是無法加入sort字段的。會一直報錯left*。

8、針對7的解決方案，把原生SQL的數(shù)據(jù)查詢和countQuery分成兩個查詢方法。得到count，然后進行判斷，若是等于0，則直接返回空集合；反之，則取獲取數(shù)據(jù)。需要自己進行分頁計算，傳入正確的pageNumber和pageSize。大部分系統(tǒng)都是按照修改時間進行降序排序。所以，order by可以寫死。然后pageNumber和pageSize動態(tài)傳入。 pageNumber的算法= (pageNumber - 1) * pageSize, 前提是PageNumber是從1開始，若0，則pageNumber=pageNumber * PageSize；這樣就可以保證數(shù)據(jù)的正確。

/**
* pageInfos: 轉(zhuǎn)換之后的數(shù)據(jù)。
* pageable：傳入的pageable.
* totalPage: 第一條SQL算好的返回值。
* 這樣就可以統(tǒng)一的返回各種pageDTO。
*/
private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) {
        return new PageImpl<>(pageInfos, pageable, totalPage);
    }

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: