spring data jpa 分页查询

法一（本地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>&lt;User&gt; findAll(Specification&lt;User&gt;<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&lt;User&gt;<span style="color: #000000;"> getUsersPage(PageParam pageParam, String nickName) {
    </span><span style="color: #008000;">//</span><span style="color: #008000;">规格定义</span>
    Specification&lt;User&gt; specification = <span style="color: #0000ff;">new</span> Specification&lt;User&gt;<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&lt;User&gt; root, CriteriaQuery&lt;?&gt;<span style="color: #000000;"> query, CriteriaBuilder cb) {
            List</span>&lt;Predicate&gt; predicates = <span style="color: #0000ff;">new</span> ArrayList&lt;&gt;(); <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&lt;IncomeDaily&gt;<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>&lt;String,Object&gt; params = <span style="color: #0000ff;">new</span> HashMap&lt;&gt;<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 &gt;= :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 &lt;= :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>&lt;IncomeDaily&gt; 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>&lt;IncomeDaily&gt; incomeDailyPage = <span style="color: #0000ff;">new</span> PageImpl&lt;IncomeDaily&gt;<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&lt;IncomeDaily&gt;<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&lt;String,Object&gt;<span style="color: #000000;"> params){
    </span><span style="color: #0000ff;">for</span>(Map.Entry&lt;String,Object&gt;<span style="color: #000000;"> entry:params.entrySet()){
        query.setParameter(entry.getKey(),entry.getValue());
    }
}<br>}</span></pre>