復雜查詢 · JAVA

[TOC] # 復雜查詢在實際的開發中我們需要用到分頁、刪選、連表等查詢的時候就需要特殊的方法或者自定義SQL ## 分頁查詢分頁查詢在實際使用中非常普遍了，spring data jpa已經幫我們實現了分頁的功能，在查詢的方法中，需要傳入參數`Pageable`,當查詢中有多個參數的時候`Pageable`建議做為最后一個參數傳入 ~~~ @Query("select u from User u") Page<User> findALL(Pageable pageable); Page<User> findByUserName(String userName,Pageable pageable); ~~~ **`Pageable`是spring封裝的分頁實現類，使用的時候需要傳入頁數、每頁條數和排序規則** **返回對象除使? Page 外, 還可以使? Slice 作為返回值** ~~~ Slice<User> findByNickNameAndEmail(String nickName, String email,Pageable pageable ); ~~~ Page 和 Slice 的區別如下 * Page 接?繼承? Slice 接?，? Slice 繼承? Iterable 接?。 * Page 接?擴展了 Slice 接?，添加了獲取總?頁數和元素總數量的?法，因此，返回 Page 接?時，必須執?兩條 SQL，?條復雜查詢分?頁數據，另?條負責統計數據數量。 * 返回 Slice 結果時，查詢的 SQL 只會有查詢分?頁數據這?條，不統計數據數量。 * ?途不?樣：Slice 不需要知道總?頁數、總數據量，只需要知道是否有下?頁、上?頁，是否是?頁、尾頁等，?如前端滑動加載?頁可?；? Page 知道總頁數、總數據量，可以?于展示具體的頁數信息， ?如后臺分頁查詢 ~~~ @Test public void testPageQuery() throws Exception { int page = 1, size = 10; Sort sort = new Sort(Direction.DESC, "id"); Pageable pageable = PageRequest.of(page, size, sort); userRepository.findALL(pageable); userRepository.findByUserName("testName", pageable); } ~~~ * Sort，控制分?頁數據的排序，可以選擇升序和降序。 * PageRequest，控制分?頁的輔助類，可以設置?頁碼、每?頁的數據條數、排序等 ## 限制查詢有時候我們只需要查詢前N個元素，或者支取前一個實體。 ~~~ User findFirstByOrderByLastnameAsc(); User findTopByOrderByAgeDesc(); Page<User> queryFirst10ByLastname(String lastname, Pageable pageable); List<User> findFirst10ByLastname(String lastname, Sort sort); List<User> findTop10ByLastname(String lastname, Pageable pageable); ~~~ ## 自定義SQL查詢其實Spring data 覺大部分的SQL都可以根據方法名定義的方式來實現，但是由于某些原因我們想使用自定義的SQL來查詢，spring data也是完美支持的；在SQL的查詢方法上面使用`@Query`注解，如涉及到刪除和修改在需要加上`@Modifying`,在調用的地方必須加事務，沒有事務不能正常執行也可以根據需要添加`@Transactional`對事物的支持，查詢超時的設置等. --- 主要的語法是 ?ndXXBy、readAXXBy、queryXXBy、countXXBy、getXXBy 后?跟屬性名稱，利?這個功能僅需要在定義的 Repository 中添加對應的?法名即可，使?時 Spring Boot 會?動幫我們實現 --- @Query 上?的 1 代表的是?法參數??的順序，如果有多個參數也可以按照這個?式添加 1、2、3....。除了按照這種?式傳參外，還可以使? @Param 來?持 ~~~ @Modifying @Query(value = "update User u set u.userName = ?1 where u.id = ?2") int modifyByIdAndUserId(String userName, Long id); @Transactional @Modifying @Query(value = "delete from User where id = ?1") void deleteByUserId(Long id); @Transactional(timeout = 10) @Query(value = "select u from User u where u.emailAddress = ?1") User findByEmailAddress(String emailAddress); ~~~ ~~~ @Query(value = "select u from User u where u.nickName = :nickName") Page<User> findByNickName(@Param("nickName") String nickName, Pageable pageable); ~~~ 還可以使用@Query來指定本地查詢，只要設置nativeQuery為true，比如： ~~~ @Query(value="select * from tbl_user where name like %?1" , nativeQuery=true) public List<UserModel> findByUuidOrAge(String name); ~~~ 基本上 SQL 體系中的關鍵詞都可以使?，如 LIKE 、IgnoreCase、OrderBy ~~~ List<User> findByEmailLike(String email); User findByUserNameIgnoreCase(String userName); List<User> findByUserNameOrderByEmailDesc(String email); ~~~ 可以根據查詢的條件不斷地添加和拼接，Spring Boot 都可以正確解析和執? ### 排序 ~~~ public interface UserRepository extends JpaRepository<User, Long> { @Query("select u from User u where u.id > :id ") List<User> QueryUserName(@Param("id") Long id, Sort sort); ~~~ ~~~ Sort sort = new Sort(Sort.Direction.DESC, "id"); List<User> res = userRepository.QueryUserName(1L, sort); ~~~ 多字段排序 ~~~ Sort sort = new Sort(Sort.Direction.DESC, "name").and(new Sort(Sort.Direction.ASC, "rangeMileage")); //按線路降序和里程升序排序 Order nameOrder = new Order(Direction.DESC, "sname"); Order rangeOrder = new Order(Direction.ASC, "rangeMileage"); List<Order> orders = new ArrayList<Order>(); orders.add(nameOrder);//先按線路降序 orders.add(rangeOrder);//再按里程升序 Sort sort2 = new Sort(orders); Pageable pageable = new PageRequest(pageNum, size, sort2); ~~~ ### SPEL表達式首先更新方法傳遞一個對象參數，然后使用SpEL表達式從對象中動態獲取已經設置的屬性的值，最后再動態組裝本地Sql ~~~ //更新用戶信息表的狀態和描述（使用參數對象） @Query(value = "update user_info set state=:#{#userInfo.state},user_info_desc=:#{#userInfo.userInfoDesc} where oid =:#{#userInfo.oid}",nativeQuery = true) @Modifying int updateUserInfo(@Param("userInfo") UserInfo userInfo); ~~~ --- `'#{#entityName}'`值為'Book'對象對應的數據表名稱(book) ~~~ public interface BookQueryRepositoryExample extends Repository<Book, Long>{ @Query(value = "select * from #{#entityName} b where b.name=?1", nativeQuery = true) List<Book> findByName(String name); } ~~~ 有同學提出來了，例子中用`'#{#entityName}'`為啥取不到值啊？先來說一說`'#{#entityName}'`到底是個啥。從字面來看，`'#{#entityName}'`不就是實體類的名稱么，對，他就是。實體類Book,使用@Entity注解后，spring會將實體類Book納入管理。默認`'#{#entityName}'`的值就是'Book'。但是如果使用了`@Entity(name = "book")`來注解實體類Book,此時`'#{#entityName}'`的值就變成了'book'。到此，事情就明了了，只需要在用@Entity來注解實體類時指定name為此實體類對應的表名。在原生sql語句中，就可以把`'#{#entityName}'`來作為數據表名使用 ### hql模糊查詢 ~~~ @Query(value = "select u from SysUser u where u.username like %:skey% or u.nickname = %:skey%", 　　　　countQuery = "select count(u) from SysUser u where u.username like %:skey% or u.nickname = %:skey%") Page<SysUser> pageQuery(@Param("skey") String skey, Pageable pageable); ~~~ 原生sql的模糊查詢下面有 ### 參數為空判斷工作中遇到一個多條件查詢的需求，需要根據名字，性別，年齡以及序號查詢數據，名字需要模糊查詢，參數有可能為空。模糊查詢 ?`like? '%:descripe%'` 會出現無法識別descripe? 所以要寫成??`like CONCAT('%',:descripe,'%')` ~~~ @Query(value = "select * from people where if(?1 !='',name like concat('%',?1,'%'),1=1) and if(?2 !='',sex=?2,1=1)"+ " and if(IFNULL(?3,'') !='',age=?3,1=1) and if(IFNULL(?4,'') !='',num=?4,1=1) ",nativeQuery = true) List<People> find(String name,String sex,Integer age,Integer num); ~~~ 1. `nativeQuery = true`的含義是使用原生SQL，即注解中的SQL語句會生效，false的話就不會生效。 2. SQL語句中`?1、?2、?3、?4`的意思是代表方法中的第幾個參數 3. SQL中模糊查詢的寫法為`like concat('%', ?1, '%')` 4. `if(?1 !='',name like concat('%',?1,'%'),1=1)`代表傳入的參數name如果不為""（Spring類型空是""而不是null）將參數傳入name，如果為空時顯示1=1 代表參數為真，對查詢結果不產生作用。IF 的語法滿足mysql的基本語法，`IF(expr1,expr2,expr3)`，**如果 expr1 為真(expr1 NULL)，那么 IF() 返回 expr2，否則返回expr3** 5. `if(IFNULL(?3,'') !='',age=?3,1=1)`表示如果傳入的年齡是null，則替換成空字符串，然后判斷是否為空，不為空則將參數傳入age，否則忽略不對查詢結果產生影響。`IFNULL`是mysql中的一個函數，這個函數一般用來替換 NULL 值的。`IFNULL(value1,value2)`，**判斷value1是否為null，如果為null則用value2替換** ### 注意 ~~~ javax.persistence.TransactionRequiredException: Executing an update/delete query at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1496) ~~~ **因為jpa要求，’沒有事務支持，不能執行更新和刪除操作’** 所以反過來講，就是在Service層或者Repository層上必須加@Transactional，來代表這是一個事務級別的操作，增刪改查除了查都是事務級別的，就當這是一個規范也是ok的 ## 已命名查詢NamedQuery和NamedQueries 除了使? @Query 注解外，還可以預先定義好?些查詢，并為其命名，然后再 Repository 中添加相同命名的?法定義命名的 Query @NamedQuery中的屬性name指定命名查詢的名稱，query屬性指定命名查詢的語句。如果要定義多個命名查詢，需要使用@NamedQueries ~~~ import javax.persistence.*; import java.sql.Timestamp; @Entity @NamedQueries({ @NamedQuery(name = "User.findByPassWord", query = "select u from User u wh ere u.passWord = ?1"), @NamedQuery(name = "User.findByNickName", query = "select u from User u wh ere u.nickName = ?1"), }) public class User { //.... } ~~~ 單個的可以這樣 ~~~ @Data @Entity @Table(name = "user") @NamedQuery(name = "User.findByUserName", query = "select u from User u where u.username = ?1") public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(nullable = true, unique = false, name = "userName") private String username; ~~~ 通過 @NamedQueries 注解可以定義多個命名Query， @NamedQuery的name 屬性定義了 Query 的名稱，注意加上 Entity 名稱 . 作為前綴，query屬性定義查詢語句。 Repository定義對應的方法 ~~~ List<User> findByPassWord(String passWord); List<User> findByNickName(String nickName); ~~~ ## 多表查詢多表查詢在spring data jpa中有兩種實現方式，第一種是利用hibernate的級聯查詢來實現，第二種是創建一個結果集的接口來接收連表查詢后的結果，這里主要第二種方式。首先需要定義一個結果集的接口類 ~~~ public interface HotelSummary { City getCity(); String getName(); Double getAverageRating(); default Integer getAverageRatingRounded() { return getAverageRating() == null ? null : (int) Math.round(getAverageRating()); } } ~~~ 查詢的方法返回類型設置為新創建的接口 ~~~ @Query("select h.city as city, h.name as name, avg(r.rating) as averageRating " - "from Hotel h left outer join h.reviews r where h.city = ?1 group by h") Page<HotelSummary> findByCity(City city, Pageable pageable); @Query("select h.name as name, avg(r.rating) as averageRating " - "from Hotel h left outer join h.reviews r group by h") Page<HotelSummary> findByCity(Pageable pageable); ~~~ 使用 ~~~ Page<HotelSummary> hotels = this.hotelRepository.findByCity(new PageRequest(0, 10, Direction.ASC, "name")); for(HotelSummary summay:hotels){ System.out.println("Name" +summay.getName()); } ~~~ > 在運行中Spring會給接口（HotelSummary）自動生產一個代理類來接收返回的結果，代碼匯總使用`getXX`的形式來獲取