springdatajpa

ゝ一世哀愁。 2022-01-29 23:59 330阅读 0赞

#### 一 概念 ####

jpa:sun官方提出的java持久化规范，其他的持久化框架可以看做是jpa的实现

#### 二 原理 ####

#### 三 使用 ####

##### 1）默认的数据库操作方法 #####

继承JpaRepository接口即可

public interface UserRepository extends JpaRepository<User, Long> { 
    }

默认的方法

//查询所有
    findAll();
    //按照id查询
    findOne(1l);
    //添加一个
    save(user);
    //删除一个
    delete(user);
    //查询数量
    count();
    //是否存在
    exists(1l);
        ...

##### 2）自定义简单查询 #####

自定义的简单查询就是根据方法名来自动生成 SQL，主要的语法是findXXBy,readAXXBy,queryXXBy,countXXBy, getXXBy后面跟属性名称

User findByUserName(String userName);
    User findByUserNameOrEmail(String username, String email);
    Long deleteById(Long id);
    Long countByUserName(String userName)
    List<User> findByEmailLike(String email);
    User findByUserNameIgnoreCase(String userName);
    List<User> findByUserNameOrderByEmailDesc(String email);

<table> 
 <thead> 
  <tr> 
   <th>Keyword</th> 
   <th align="center">Sample</th> 
   <th align="right">JPQL snippet</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>And</td> 
   <td align="center">findByLastnameAndFirstname</td> 
   <td align="right">… where x.lastname = ?1 and x.firstname = ?2</td> 
  </tr> 
  <tr> 
   <td>Or</td> 
   <td align="center">findByLastnameOrFirstname</td> 
   <td align="right">… where x.lastname = ?1 or x.firstname = ?2</td> 
  </tr> 
  <tr> 
   <td>Is,Equals</td> 
   <td align="center">findByFirstnameIs,findByFirstnameEquals</td> 
   <td align="right">… where x.firstname = ?1</td> 
  </tr> 
  <tr> 
   <td>Between</td> 
   <td align="center">findByStartDateBetween</td> 
   <td align="right">… where x.startDate between ?1 and ?2</td> 
  </tr> 
  <tr> 
   <td>LessThan</td> 
   <td align="center">findByAgeLessThan</td> 
   <td align="right">… where x.age &lt; ?1</td> 
  </tr> 
  <tr> 
   <td>LessThanEqual</td> 
   <td align="center">findByAgeLessThanEqual</td> 
   <td align="right">… where x.age ⇐ ?1</td> 
  </tr> 
  <tr> 
   <td>GreaterThan</td> 
   <td align="center">findByAgeGreaterThan</td> 
   <td align="right">… where x.age &gt; ?1</td> 
  </tr> 
  <tr> 
   <td>GreaterThanEqual</td> 
   <td align="center">findByAgeGreaterThanEqual</td> 
   <td align="right">… where x.age &gt;= ?1</td> 
  </tr> 
  <tr> 
   <td>After</td> 
   <td align="center">findByStartDateAfter</td> 
   <td align="right">… where x.startDate &gt; ?1</td> 
  </tr> 
  <tr> 
   <td>Before</td> 
   <td align="center">findByStartDateBefore</td> 
   <td align="right">… where x.startDate &lt; ?1</td> 
  </tr> 
  <tr> 
   <td>IsNull</td> 
   <td align="center">findByAgeIsNull</td> 
   <td align="right">… where x.age is null</td> 
  </tr> 
  <tr> 
   <td>IsNotNull,NotNull</td> 
   <td align="center">findByAge(Is)NotNull</td> 
   <td align="right">… where x.age not null</td> 
  </tr> 
  <tr> 
   <td>Like</td> 
   <td align="center">findByFirstnameLike</td> 
   <td align="right">… where x.firstname like ?1</td> 
  </tr> 
  <tr> 
   <td>NotLike</td> 
   <td align="center">findByFirstnameNotLike</td> 
   <td align="right">… where x.firstname not like ?1</td> 
  </tr> 
  <tr> 
   <td>StartingWith</td> 
   <td align="center">findByFirstnameStartingWith</td> 
   <td align="right">… where x.firstname like ?1 (parameter bound with appended %)</td> 
  </tr> 
  <tr> 
   <td>EndingWith</td> 
   <td align="center">findByFirstnameEndingWith</td> 
   <td align="right">… where x.firstname like ?1 (parameter bound with prepended %)</td> 
  </tr> 
  <tr> 
   <td>Containing</td> 
   <td align="center">findByFirstnameContaining</td> 
   <td align="right">… where x.firstname like ?1 (parameter bound wrapped in %)</td> 
  </tr> 
  <tr> 
   <td>OrderBy</td> 
   <td align="center">findByAgeOrderByLastnameDesc</td> 
   <td align="right">… where x.age = ?1 order by x.lastname desc</td> 
  </tr> 
  <tr> 
   <td>Not</td> 
   <td align="center">findByLastnameNot</td> 
   <td align="right">… where x.lastname &lt;&gt; ?1</td> 
  </tr> 
  <tr> 
   <td>In</td> 
   <td align="center">findByAgeIn(Collection ages)</td> 
   <td align="right">… where x.age in ?1</td> 
  </tr> 
  <tr> 
   <td>NotIn</td> 
   <td align="center">findByAgeNotIn(Collection age)</td> 
   <td align="right">… where x.age not in ?1</td> 
  </tr> 
  <tr> 
   <td>TRUE</td> 
   <td align="center">findByActiveTrue()</td> 
   <td align="right">… where x.active = true</td> 
  </tr> 
  <tr> 
   <td>FALSE</td> 
   <td align="center">findByActiveFalse()</td> 
   <td align="right">… where x.active = false</td> 
  </tr> 
  <tr> 
   <td>IgnoreCase</td> 
   <td align="center">findByFirstnameIgnoreCase</td> 
   <td align="right">… where UPPER(x.firstame) = UPPER(?1)</td> 
  </tr> 
 </tbody> 
</table>

##### 3）分页查询 #####

在查询的方法中，需要传入参数Pageable ,当查询中有多个参数的时候Pageable建议做为最后一个参数传入

Page<User> findALL(Pageable pageable);
    Page<User> findByUserName(String userName,Pageable pageable);

Pageable 是 Spring 封装的分页实现类，使用的时候需要传入页数、每页条数和排序规则

int page=1,size=10;
    Sort sort = new Sort(Direction.DESC, "id");
    Pageable pageable = new PageRequest(page, size, sort);
    userRepository.findALL(pageable);
    userRepository.findByUserName("testName", pageable);

##### 4）限制查询 #####

只需要查询前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);

##### 5）自定义SQL查询 #####

由于某些原因我们想使用自定义的 SQL 来查询，Spring Data 也是完美支持的；在 SQL 的查询方法上面使用@Query注解，如涉及到删除和修改在需要加上@Modifying.也可以根据需要添加 @Transactional对事物的支持，查询超时的设置等。

@Modifying
    @Query("update User u set u.userName = ?1 where u.id = ?2")
    int modifyByIdAndUserId(String  userName, Long id);
        
    @Transactional
    @Modifying
    @Query("delete from User where id = ?1")
    void deleteByUserId(Long id);
      
    @Transactional(timeout = 10)
    @Query("select u from User u where u.emailAddress = ?1")
    User findByEmailAddress(String emailAddress);

##### 6）多表查询 #####

多表查询 Spring Boot 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);

###### 使用 ######

//在运行中 Spring 会给接口（HotelSummary）自动生产一个代理类来
    //接收返回的结果，代码汇总使用 getXX的形式来获取
    Page<HotelSummary> hotels = this.hotelRepository.findByCity(new PageRequest(0, 10, Direction.ASC, "name"));
    for(HotelSummary summay:hotels){ 
            System.out.println("Name" +summay.getName());
        }

***参考***[地址][Link 1]

[Link 1]: https://www.cnblogs.com/ityouknow/p/5891443.html