Springboot整合SpringDataJpa（二）

一、概述

首先要重述下SpringDataJpa，SpringDataJpa是springdata的一个子模块。Jpa是一个标准接口，Hibernate是Jpa的实现。而SpringDataJpa底层实现了Hibernate.

使用Springdatajpa,第一步就是要实现Repository。它是一个标记接口，只要实现这个 接口就能使用“按照方法命名规则”。这种方法虽然简便，但也有很大的弊端，

如果一旦查询的条件一多，就会使方法名字显得很长。而且较为复杂的查询就很难实现。

二、代码实现

/**根据名字查询前五条记录
      */
    public List<Towns> findFirst5ByCityName(String cityName);

　　controller:

@RequestMapping("/findByNameonly5")
public List<Towns> findByNameonly5(){
    List<Towns> list = new ArrayList<>();
    String cityName ="桂林";
    list = townsRepository.findFirst5ByCityName(cityName) ;
    return list;
}

　关键字between使用：

 @RequestMapping("/findBetween")
    public List<Towns> findBetween(){
        List<Towns> list = new ArrayList<>();
        int i = 1 ,j =3;
        list = townsRepository.findByIdBetween(i,j);
        return list;
    }

　

 /** 关键字Between使用
     */
     public List<Towns> findByIdBetween(int pre,int last);
    

还有很多根据关键字命名的查询语句就不一一列举了。以下是常用的关键字和对应SQL语法：

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

 

实现update：

 @Modifying
     @Transactional
    @Query(value = "update Towns set townsName = :townsName where id =:id")
     /**Not supported for DML operations 没有加 @ Modifying 会报错
      * "Executing an update/delete query  没有加 @Transactional，实际上这个注解应该加在Service层，这里是测试 并没有写
      * **/
    public void updateTowns(@Param("id")int id ,@Param("townsName") String townsName);

　　

  @RequestMapping("/updateTowns")
    public void updateTowns(){
        int id = 2;
        String townsName = "gggggg"  ;
        townsRepository.updateTowns(id,townsName);
    }

　　实现分页排序：

 @RequestMapping("/findTownsSort")
    public Page<Towns> findTownsSort(){
        ArrayList<Towns> arrayList = new ArrayList<>();
        Sort.Order  order= new Sort.Order(Sort.Direction.DESC,"id");
        //Sort.Order order =  Sort.Order.desc("id");
        Sort sort = Sort.by(order) ;
        int page = 0, size = 5;
        Pageable pageable =  PageRequest.of(page,size,sort);
        Page<Towns> pagelist = townsRepository.findAllPage(pageable);
        return pagelist;
    }

　　

   @Query(value = "select t from Towns t")
    Page<Towns> findAllPage(Pageable pageRequest);

　　这里我没有全部使用用关键字命名查询的方法。一般单表查询用关键字，稍微复杂的语句用@query。