mysql实现分页的几种方式

mysql实现分页的几种方式:

第一种:使用框架自带的pageable来进行分页

 

package com.cellstrain.icell.repository.repositoryImpl;

import com.cellstrain.icell.entity.LunBoTu;

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

public class LunBoTuRepositoryImpl {

    @PersistenceContext
    private EntityManager entityManager;

    public List<LunBoTu> findTopThree(){
        StringBuffer sql = new StringBuffer("from LunBoTu lbt where 1=1 order by lbt.id desc");
        Query query = entityManager.createQuery(sql.toString());
        query.setFirstResult(0);
        query.setMaxResults(6);
        List<LunBoTu> lunbotuList  = query.getResultList();
        return lunbotuList;
    }
}

 

第二种:使用limit关键字来进行分页

public Page<News> FileDownloadByConditions(String condition, Pageable pageable) {
    StringBuffer sql = new StringBuffer("from news n left join newscategory c on n.newsCategoryId=c.id where c.id = 12  and ");
    if(!StringUtils.isEmpty(condition)){
        sql.append(" n.newsAuthor like '%"+condition+"%' or n.newsTitle like '%"+condition+"%' or c.categoryName like '%"+condition+"%' or n.roundup like '%"+condition+"%'  and 1=1 ");
    }else{
        sql.append(" 1=1");
    }
    String fen_ye_sql = sql.toString() + " limit " + pageable.getOffset() + "," + pageable.getPageSize();
    Long total = jdbcTemplate.queryForObject("select count(*) " + sql.toString(), Long.class);
    List<News> newsList = null;
    try {
        newsList = jdbcTemplate.query("select n.*,c.categoryName " + fen_ye_sql.toString(), new BeanPropertyRowMapper(News.class));
    } catch (Exception e) {
        e.printStackTrace();
    }
    Page<News> page = new PageImpl(newsList, pageable, total);
    return page;
}