在使用hebernate的數(shù)據(jù)查詢時(shí)總是遇到問題，在此將自己在hebernate中的查詢做一下總結(jié)
1.jpa的數(shù)據(jù)查詢
-
直接使用關(guān)鍵字查詢，適合單表查詢，簡(jiǎn)單查詢,示例代碼如下
Page<MandatoryInstrument>findAllByStatusAndDepartmentAndNextCheckDateBetween(Byte statusNormal, Department department, Date beginDate, Date endDate, Pageable pageable);
方法的名字相當(dāng)于查詢的方法并配合方法的參數(shù)進(jìn)行數(shù)據(jù)查詢
當(dāng)前方法意思是在當(dāng)前實(shí)體中根據(jù)Status，Department，NextCheckDate（在beginDate與endDate之間）這幾個(gè)屬性都為當(dāng)前實(shí)體的屬性，通過這幾個(gè)條件查詢滿足條件的實(shí)體，最后一個(gè)參數(shù)pageable代表傳入分頁信息
- 使用@query查詢，適合多表查詢，和復(fù)雜查詢
當(dāng)時(shí)遇到問題是：不知道@query里面是存放純sql語句還是有專門語法，后來查了一下里面的語法不是純sql語句
<colgroup style="line-height: 1.57143em;"><col data-mce-style="width: 130px;" style="line-height: 1.57143em; width: 130px;"><col data-mce-style="width: 130px;" style="line-height: 1.57143em; width: 130px;"></colgroup>
@Query("select i from #{#entityName} i" +
" where i.deviceSet.department = :department " +
" and i.lastCheckDate between current_date() and :nextDay ")
Page<StandardDevice> findAllByDepartmentAndLastCheckDateBetweenTodayAndNextDay(@Param("nextDay") Date nextDay, @Param("department") Department department, Pageable pageable);
對(duì)象比較
i.deviceSet.department = ：department 可以找到當(dāng)前實(shí)體的標(biāo)準(zhǔn)器的部門,并直接與傳入的部門參數(shù)進(jìn)行比較
日期處理
i.lastCheckDate between current_date() and :nextDay 檢測(cè)當(dāng)前數(shù)據(jù)的日期是否在今天與下一個(gè)時(shí)間之間
i.lastCheckDate < current_date() 檢測(cè)當(dāng)前數(shù)據(jù)的日期是否小于今天
多表查詢
多表查詢是使用nativeQuery = true里面使用純SQL語句
// 查找屬于某個(gè)部門的非檢定員用戶
@Query(value ="select *" +
"from user " +
"where user.id not in " +
"(select check_personal.user_id from check_personal)" +
" and user.department_id = :departmentId", nativeQuery = true)
List<User> findNotCheckUserByDepartmentId(@Param("departmentId") Long departmentId);
返回查詢數(shù)據(jù)中的前多少條
@Query(nativeQuery = true,value = "SELECT * FROM sell WHERE sell.id > :lastid limit :number ")
