首页 > 代码库 > sqlserver实现分页的几种方式
sqlserver实现分页的几种方式
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 = http://www.mamicode.com/"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 = http://www.mamicode.com/"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;
}
}
sqlserver实现分页的几种方式
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。