IT序号网

spring data jpa 分页查询知识解答

xmjava 2021年06月14日 数据库 150 0

法一(本地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> 
复制代码

发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

MySQL类型float double decimal的区别知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。