jpa规则

文章字数：236,阅读全文大约需要1分钟

常用规则

NO	关键词	实例	sql
1	And	findByNameAndAge	where name=?1 and age=?2
2	Or	findByLastnameOrFirstname	where lastname=?1 or firstname=2?
3	Is,Equals	findByFirstnameIs;等于findByFirstname	where name=?1
4	Between	findByStartDateBetween	where startDate between ?1 and ?2
5	LessThan	findByAgeLessThan	where age < ?1
6	LessThanEqual	findByAgeLessThanEqual	where age<= ?1
7	GreaterThan	findByAgeGreaterThan	where age >?1
8	GreaterThanEqual	findByAgeGreaterThanEqual	where age >= ?1
9	After	findByStartDateAfter	where startDate > ?1
10	Before	findByStartDateBefore	where startDate < ?1
11	IsNull	findByAgeIsNull	where age is null
12	IsNotNull/NotNull	findByAge(Is)NotNull	where age not null
13	Like	findByFirstnameLike	where firstname like ?1
14	NotLike	findByFirstnameNotLike	where firstname not like ?1
15	StartingWith	findByFirstnameStartingWith	where firstname like ?1(参数前加上%)
16	EndingWith	findByFirstnameEndingWith	where firstname like ?1(参数后加%)
17	Containing	findByFirstnameContaining	where first like ?1(参数前后%)
18	OrderBy	findByAgeOrderByLastNameDesc	where age = ?1 order by lastname desc
19	Not	findByLastnameNot	where lastname != ?1
20	In	findByAgeIn(Collection ages)	where age in ?1
21	NotIn	findByAgeNotIn(Collection age)	where age not in ?1
22	TRUE	findByActiveTrue()	where active = true
23	FALSE	findByActiveFalse()	where active=false
24	IgnoreCase(忽略大小写)	findByFirsrnameIgnoreCase	where UPPER(firstname) = UPPER(?1)//转为大写
25	existsBy	existsByUserId(long userId)	where exists(..where userid=?1)

排序分页

方法最后一个参数是Sort或者Pageable会提取信息进行排序或分页

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
public Page findAll(new PageRequest(pageNo,pageSize))

• 排序

  List<Cus> findBySexOrderByName(String sex); //名称正序（正序时，推荐此方式，简单）
  List<Cus> findBySexOrderByNameAsc(String sex); //名称正序（效果同上）
  List<Cus> findBySexOrderByNameDesc(String sex); //名称倒序

• 条数限制

  Resource findFirstByFather_idOrderByOrderNumDesc(Long fatherId);
  
  User findFirstByOrderByLastnameAsc();
  
  User findTopByOrderByAgeDesc();
  
  Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);
  
  Slice<User> findTop3ByLastname(String lastname, Pageable pageable);
  
  List<User> findFirst10ByLastname(String lastname, Sort sort);
  
  List<User> findTop10ByLastname(String lastname, Pageable pageable);

• 计数

  Long countByLastname(String lastname);

• 删除

  void deleteByProject_Id(Long id);
  void deleteByProject_Cus_id(Long id);

---