Spring Data JPA02 Repository-查询

结构 

1．Repository: 仅仅是一个标识，表明任何继承它的均为仓库接口类

2．CrudReponsitory:实现了一组CRUD相关的方法

3．PagingAndSortingRepository:实现了一组分页排序相关的方法

4．JpaRepository: 实现了一组JPA规范的方法

 

继承JpaRepository 

继承JpaRepository 就具有了通用的数据访问控制层的能力

@Entity
@Table(name = "tbl_user")
@Data
public class User {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Column(name = "last_name",length = 50)
  private String lastName;

  @Column
  private String email;
}

public interface UserRepository extends JpaRepository<User,Integer> {
    User findByIdAndLastName(int i, String s);
}

JPA Query Methods :: Spring Data JPA

Defining Query Methods :: Spring Data JPA

Repository query keywords :: Spring Data JPA

关键词 查询

1. 查询方法以 find | read | get 开头

2. 涉及条件查询时，条件的属性用条件关键字连接,比如[And,Or , IsNull等等]条件的关键字

And

findByLastnameAndFirstname

where x.lastname=? 1 and x.frstname=?2

public List<Customer> findByCustNameAndCustAddress(String custName,String custAddress);

Or

findByLastnameOrFirstname
where x.lastname=?1 or x.frstname=?2

 Between

findByStartDateBetween
Where x.startDate between 1? and ?2

LessThan

findByAgeLessThan
where x.age <?1

GreaterThan

findByAgeGreaterThan
Where x.age>?1

After

findByStartDateAfter
Where x.startDate>?1

Before

findByStartDateBefore
where x. startDate<?1

isNull

findByAgelsNull
Where x.age is null

IsNotNul,NotNull

findByAge(Is)NotNull
where 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%

EndingWith

findByFirstnameEndingWith
where x.firstname like %?1

Containing

findByFirstnameContaining 
where x.firstname like %?1%

OrderBy

findByAgeOrderByLastnameDesc 
where x.age ?1 order by x.lastname desc

Not

findByLastnameNot 
where x.lastname <>?1

 In

findByAgeln(Collection<Age>ages) 
where x.age in？1

Not ln

findByAgeNotln(Collection<Age>age) 
where x.age not in ?1

TRUE

findByActiveTrue()
where x.active true

FALSE

findByActiveFalse()
where x.active false

count

long countByUsername(String username);

@Query

索引参数

索引值从1开始，查询中 ”?X” 个数需要与方法定义的参数个数相一致，并且顺序也要一致

@Query("from Customer where  custId=?1")
public List<Customer> gete(Long i);

@Query("from  Customer where custName like %?1%")
List<Customer> testLikeParam( String custName);

命名参数

@Query(" FROM Person WHERE email = :email")
List<Person> testQueryAnnotationParams2(@Param("email") String email );

@Query(" FROM Person  WHERE lastName LIKE %:lastName% )
List<Person> testQueryAnnotationLikeParam2( @Param("lastName") String lastName);

nativeQuery

@Query(value="select * from cst_customer",nativeQuery=true)
public void findSql();

分页

PagingAndSortingRepository
Sort id = Sort.by(Sort.Direction.DESC, "id");
PageRequest pageRequest = PageRequest.of(1, 3,id);
Page<User> all = userRepository.findAll(pageRequest);
int number = all.getNumber();