sqlserver實現分頁的幾種方式

第一種:使用org.springframework.data.domain.Page來進行分頁

package com.cellstrain.icell.repository.repositoryImpl;

import com.cellstrain.icell.entity.V_Paper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;

@Repository(value = "vPaperRepository")
public class VPaperRepositoryImpl {
@Autowired
private JdbcTemplate jdbcTemplate;

@PersistenceContext
private EntityManager entityManager;

/**
* 后臺分頁查詢
* @param condition
* @param pageable
* @return
*/
public Page findByCondition(String condition, Pageable pageable){
StringBuffer hql = new StringBuffer("from V_Paper p where ");
if(!StringUtils.isEmpty(condition)){
hql.append("p.title like '%"+condition+"%' or p.entryName like '%"+condition+"%' or p.pName like '%"+condition+"%' or p.auth like '%"+condition+"%' order by p.paperId desc");
}else{
hql.append("1=1 order by p.paperId desc");
}
Query query = entityManager.createQuery(hql.toString());
//得到符合記錄的總數
int count = query.getResultList().size();
Long total = (long)count;
//分頁查詢
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
List<V_Paper> proteinRanksList = query.getResultList();
//封裝Page
Page<V_Paper> page =new PageImpl<V_Paper>(proteinRanksList,pageable,total);
}

}

第二種:使用top關鍵字來進行分頁
package com.cellstrain.icell.repository.repositoryImpl;

import com.cellstrain.icell.entity.V_Paper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;

@Repository(value = "vPaperRepository")
public class VPaperRepositoryImpl {
@Autowired
private JdbcTemplate jdbcTemplate;

@PersistenceContext
private EntityManager entityManager;

/**
* 后臺分頁查詢
* @param condition
* @param pageable
* @return
*/
public Page findByCondition(String condition, Pageable pageable){
if(StringUtils.isEmpty(condition)){
condition="";
}
int pageSize=pageable.getPageSize();
int pageNow=pageable.getPageNumber();
String sql="select top "+pageSize+" vp.paperId,vp.title,vp.entryName,vp.source,vp.recordDate,vp.url,vp.auth,dbo.JoinStr(vp.paperId) as pNames" +
" from V_Paper vp where vp.paperId not in(select top "+pageSize*pageNow+" paperId from V_Paper order by paperId desc) and (title like '%"+condition+"%' or contents like '%"+condition+"%') " +
"group by vp.paperId,vp.title,vp.entryName,vp.source,vp.recordDate,vp.url,vp.auth order by vp.paperId desc";
List paperList=null;
Long total = jdbcTemplate.queryForObject("select count(*) from V_Paper",Long.class);
try{
paperList=jdbcTemplate.queryForList(sql);
}catch (Exception e){
e.printStackTrace();
}
Page page = new PageImpl(paperList, pageable, total);
return page;
}
}
?

轉載于:https://www.cnblogs.com/qianzf/p/6781618.html

總結

以上是生活随笔為你收集整理的sqlserver实现分页的几种方式的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。