复杂查询 · 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`的形式来获取