法一（本地sql查询,注意表名啥的都用数据库中的名称，适用于特定数据库的查询）
public interface UserRepository extends JpaRepository<User, Long> {@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
法二（jpa已经实现的分页接口，适用于简单的分页查询）
public interface PagingAndSortingRepository<T, ID extends Serializable> extends CrudRepository<T, ID> {Iterable<T> findAll(Sort sort);
Page<T> findAll(Pageable pageable);
}Accessing the second page of User by a page size of 20 you could simply do something like this:
PagingAndSortingRepository<User, Long> repository = // … get access to a bean
Page<User> users = repository.findAll(new PageRequest(1, 20));
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);
//service Sort sort = new Sort(Sort.Direction.DESC,"createTime"); //创建时间降序排序 Pageable pageable = new PageRequest(pageNumber,pageSize,sort); this.depositRecordRepository.findAllByUserIdIn(userIds,pageable);//repository
Page<DepositRecord> findAllByUserIdIn(List<Long> userIds,Pageable pageable);
法三（Query注解,hql语局，适用于查询指定条件的数据）
@Query(value = "select b.roomUid from RoomBoard b where b.userId=:userId and b.lastBoard=true order by b.createTime desc") Page<String> findRoomUidsByUserIdPageable(@Param("userId") long userId, Pageable pageable);
Pageable pageable = new PageRequest(pageNumber,pageSize); Page<String> page = this.roomBoardRepository.findRoomUidsByUserIdPageable(userId,pageable); List<String> roomUids = page.getContent();
可以自定义整个实体(Page<User>)，也可以查询某几个字段（Page<Object[]>），和原生sql几乎一样灵活。
法四（扩充findAll，适用于动态sql查询）
public interface UserRepository extends JpaRepository<User, Long> {Page</span><User> findAll(Specification<User><span style="color: #000000;"> spec, Pageable pageable);}
@Service public class UserService {@Autowired </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> UserRepository userRepository; </span><span style="color: #0000ff;">public</span> Page<User><span style="color: #000000;"> getUsersPage(PageParam pageParam, String nickName) { </span><span style="color: #008000;">//</span><span style="color: #008000;">规格定义</span> Specification<User> specification = <span style="color: #0000ff;">new</span> Specification<User><span style="color: #000000;">() { </span><span style="color: #008000;">/**</span><span style="color: #008000;"> * 构造断言 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> root 实体对象引用 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> query 规则查询对象 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> cb 规则构建对象 * </span><span style="color: #808080;">@return</span><span style="color: #008000;"> 断言 </span><span style="color: #008000;">*/</span><span style="color: #000000;"> @Override </span><span style="color: #0000ff;">public</span> Predicate toPredicate(Root<User> root, CriteriaQuery<?><span style="color: #000000;"> query, CriteriaBuilder cb) { List</span><Predicate> predicates = <span style="color: #0000ff;">new</span> ArrayList<>(); <span style="color: #008000;">//</span><span style="color: #008000;">所有的断言</span> <span style="color: #0000ff;">if</span>(StringUtils.isNotBlank(nickName)){ <span style="color: #008000;">//</span><span style="color: #008000;">添加断言</span> Predicate likeNickName = cb.like(root.get("nickName").as(String.<span style="color: #0000ff;">class</span>),nickName+"%"<span style="color: #000000;">); predicates.add(likeNickName); } </span><span style="color: #0000ff;">return</span> cb.and(predicates.toArray(<span style="color: #0000ff;">new</span> Predicate[0<span style="color: #000000;">])); } }; </span><span style="color: #008000;">//</span><span style="color: #008000;">分页信息</span> Pageable pageable = <span style="color: #0000ff;">new</span> PageRequest(pageParam.getPage()-1,pageParam.getLimit()); <span style="color: #008000;">//</span><span style="color: #008000;">页码：前端从1开始，jpa从0开始，做个转换 </span><span style="color: #008000;">//</span><span style="color: #008000;">查询</span> <span style="color: #0000ff;">return</span> <span style="color: #0000ff;">this</span><span style="color: #000000;">.userRepository.findAll(specification,pageable); }}
法五（使用entityManager，适用于动态sql查询）
@Service @Transactional
public class IncomeService{</span><span style="color: #008000;">/**</span><span style="color: #008000;"> * 实体管理对象 </span><span style="color: #008000;">*/</span><span style="color: #000000;"> @PersistenceContext</span><span style="color: #000000;"> EntityManager entityManager; </span><span style="color: #0000ff;">public</span> Page<IncomeDaily><span style="color: #000000;"> findIncomeDailysByPage(PageParam pageParam, String cpId, String appId, Date start, Date end, String sp) { StringBuilder countSelectSql </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> StringBuilder(); countSelectSql.append(</span>"select count(*) from IncomeDaily po where 1=1 "<span style="color: #000000;">); StringBuilder selectSql </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> StringBuilder(); selectSql.append(</span>"from IncomeDaily po where 1=1 "<span style="color: #000000;">); Map</span><String,Object> params = <span style="color: #0000ff;">new</span> HashMap<><span style="color: #000000;">(); StringBuilder whereSql </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> StringBuilder(); </span><span style="color: #0000ff;">if</span><span style="color: #000000;">(StringUtils.isNotBlank(cpId)){ whereSql.append(</span>" and cpId=:cpId "<span style="color: #000000;">); params.put(</span>"cpId"<span style="color: #000000;">,cpId); } </span><span style="color: #0000ff;">if</span><span style="color: #000000;">(StringUtils.isNotBlank(appId)){ whereSql.append(</span>" and appId=:appId "<span style="color: #000000;">); params.put(</span>"appId"<span style="color: #000000;">,appId); } </span><span style="color: #0000ff;">if</span><span style="color: #000000;">(StringUtils.isNotBlank(sp)){ whereSql.append(</span>" and sp=:sp "<span style="color: #000000;">); params.put(</span>"sp"<span style="color: #000000;">,sp); } </span><span style="color: #0000ff;">if</span> (start == <span style="color: #0000ff;">null</span><span style="color: #000000;">) { start </span>= DateUtil.getStartOfDate(<span style="color: #0000ff;">new</span><span style="color: #000000;"> Date()); } whereSql.append(</span>" and po.bizDate >= :startTime"<span style="color: #000000;">); params.put(</span>"startTime"<span style="color: #000000;">, start); </span><span style="color: #0000ff;">if</span> (end != <span style="color: #0000ff;">null</span><span style="color: #000000;">) { whereSql.append(</span>" and po.bizDate <= :endTime"<span style="color: #000000;">); params.put(</span>"endTime"<span style="color: #000000;">, end); } String countSql </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> StringBuilder().append(countSelectSql).append(whereSql).toString(); Query countQuery </span>= <span style="color: #0000ff;">this</span>.entityManager.createQuery(countSql,Long.<span style="color: #0000ff;">class</span><span style="color: #000000;">); </span><span style="color: #0000ff;">this</span><span style="color: #000000;">.setParameters(countQuery,params); Long count </span>=<span style="color: #000000;"> (Long) countQuery.getSingleResult(); String querySql </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> StringBuilder().append(selectSql).append(whereSql).toString(); Query query </span>= <span style="color: #0000ff;">this</span>.entityManager.createQuery(querySql,IncomeDaily.<span style="color: #0000ff;">class</span><span style="color: #000000;">); </span><span style="color: #0000ff;">this</span><span style="color: #000000;">.setParameters(query,params); </span><span style="color: #0000ff;">if</span>(pageParam != <span style="color: #0000ff;">null</span>){ <span style="color: #008000;">//</span><span style="color: #008000;">分页</span>query.setFirstResult(pageParam.getStart());
query.setMaxResults(pageParam.getLength());
}List</span><IncomeDaily> incomeDailyList =<span style="color: #000000;"> query.getResultList(); </span><span style="color: #0000ff;">if</span>(pageParam != <span style="color: #0000ff;">null</span>) { <span style="color: #008000;">//</span><span style="color: #008000;">分页</span> Pageable pageable = <span style="color: #0000ff;">new</span><span style="color: #000000;"> PageRequest(pageParam.getPage(), pageParam.getLength()); Page</span><IncomeDaily> incomeDailyPage = <span style="color: #0000ff;">new</span> PageImpl<IncomeDaily><span style="color: #000000;">(incomeDailyList, pageable, count); </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> incomeDailyPage; }</span><span style="color: #0000ff;">else</span>{ <span style="color: #008000;">//</span><span style="color: #008000;">不分页</span> <span style="color: #0000ff;">return</span> <span style="color: #0000ff;">new</span> PageImpl<IncomeDaily><span style="color: #000000;">(incomeDailyList); } } </span><span style="color: #008000;">/**</span><span style="color: #008000;"> * 给hql参数设置值 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> query 查询 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> params 参数 </span><span style="color: #008000;">*/</span> <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">void</span> setParameters(Query query,Map<String,Object><span style="color: #000000;"> params){ </span><span style="color: #0000ff;">for</span>(Map.Entry<String,Object><span style="color: #000000;"> entry:params.entrySet()){ query.setParameter(entry.getKey(),entry.getValue()); } }<br>}</span></pre>
