分页

oracle分页

分页类

package cn.et.conf;

import java.util.List;

public class PartPageDate {
	/**
	 * 当前页(动态 由页面传递)
	 */
	private Integer curPage;
	/**
	 * 每页显示的条数
	 */
	private Integer pageCount=10;
	/**
	 * 上一页
	 * prPage = curPage-1==0?1:curPage-1;
	 */
	private Integer prePage;
	/**
	 * 下一页
	 * nextPage = curPage==totalPage?totalPage:curPage+1;
	 */
	private Integer nextPage;
	/**
	 * 总页数
	 * totalPage = total%pageCount==0?total/pageCount:total/pageCount+1
	 */
	private Integer totalPage;
	/**
	 * 总记录数(从数据库查询)
	 */
	private Integer totalCount;
	/**
	 * 开始索引
	 * 	startIndex=(curPage-1)*pageCount+1
	 */
	private Integer startIndex;
	
	/**
	 * 结束索引
	 * 	endIndex=curPage*pageCount;
	 */
	private Integer endIndex;
	
	/**
	 * 存储最终查询的数据
	 */
	@SuppressWarnings("unchecked")
	private List list;

	public Integer getCurPage() {
		return curPage;
	}

	public void setCurPage(Integer curPage) {
		this.curPage = curPage;
	}

	public Integer getPageCount() {
		return pageCount;
	}

	public void setPageCount(Integer pageCount) {
		this.pageCount = pageCount;
	}

	public Integer getPrePage() {
		return prePage;
	}

	public void setPrePage(Integer prePage) {
		this.prePage = prePage;
	}

	public Integer getNextPage() {
		return nextPage;
	}

	public void setNextPage(Integer nextPage) {
		this.nextPage = nextPage;
	}

	public Integer getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(Integer totalPage) {
		this.totalPage = totalPage;
	}

	public Integer getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(Integer totalCount) {
		this.totalCount = totalCount;
	}

	@SuppressWarnings("unchecked")
	public List getList() {
		return list;
	}

	@SuppressWarnings("unchecked")
	public void setList(List list) {
		this.list = list;
	}
	
	public Integer getStartIndex() {
		return startIndex;
	}

	public void setStartIndex(Integer startIndex) {
		this.startIndex = startIndex;
	}

	public Integer getEndIndex() {
		return endIndex;
	}

	public void setEndIndex(Integer endIndex) {
		this.endIndex = endIndex;
	}
}

创建工具类

package cn.et.conf;

public class PartPageTool {
	/**
	 * 创建一个工具类  用于生成分页
	 * @param curPage 当前页
	 * @param totalCount 当前页
	 * @param pageCount 每页显示的条数
	 * @return
	 */
	public static PartPageDate getPartPageDate(Integer curPage,Integer totalCount,Integer pageCount){
		PartPageDate ppd = new PartPageDate();
		//当前页
		ppd.setCurPage(curPage);
		//当前页
		ppd.setTotalCount(totalCount);
		//每页显示的条数
		ppd.setPageCount((pageCount==null)?ppd.getPageCount():pageCount);
		//上一页
		ppd.setPrePage((ppd.getCurPage()-1)==0?1:ppd.getCurPage()-1);
		int mode = ppd.getTotalCount()%ppd.getPageCount();
		int count = ppd.getTotalCount()/ppd.getPageCount();
		//总页数
		ppd.setTotalPage(mode==0?count:(count+1));
		//下一页
		ppd.setNextPage((ppd.getCurPage().equals(ppd.getTotalPage()))?ppd.getTotalPage():ppd.getCurPage()+1);
		//开始索引
		ppd.setStartIndex((ppd.getCurPage()-1)*ppd.getPageCount()+1);
		//结束索引
		ppd.setEndIndex(ppd.getPageCount()*ppd.getCurPage());
		return ppd;
	}
}

dao层

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cn.et.conf.PartPageDate;
import cn.et.conf.User;
import cn.et.dao.UserDao;

@Repository
public class UserDaoImpl implements UserDao{
	@Autowired
	private JdbcTemplate jdbc;
	/**
	 * 根据学生名字查询学生  模糊查询
	 * 
	 * partPageDate.getStartIndex()：开始索引 ，partPageDate.getEndIndex() 结束索引
	 * @param name 学生姓名
	 * @param partPageDate 分页类
	 * @return	学生信息
	 */
	public List<Map<String, Object>> queryUser(String uname,PartPageDate partPageDate){
		String sql = "SELECT * FROM (SELECT u.*,ROWNUM RN FROM users u) WHERE RN>="+partPageDate.getStartIndex()+" AND RN<="+partPageDate.getEndIndex();
		return jdbc.queryForList(sql);
	}
	/**
	 * 获取当前学生信息总页数
	 * @param uname 学生姓名
	 * @return 学生信息
	 */
	public List<Map<String, Object>> getUserTotalPage(String uname){
		String sql = "select count(uid) CU from users where uname like '%"+uname+"%'";
		return jdbc.queryForList(sql);
	}
}

Controller层

import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import cn.et.conf.PartPageDate;
import cn.et.service.UserService;
@Controller
public class UserController {
	@Autowired
	UserService userService;
	/**
	 * 查询学生
	 * @param uname
	 * @param model
	 * @return
	 */
	@RequestMapping("/queryUser")
	public String queryUser(String uname,Integer curPage,Model model){
		PartPageDate partPageDate = userService.queryUser(uname,curPage);
		model.addAttribute("partPageDate",partPageDate);
		return "/user.jsp";
	}
}

mysql

由于分页差别不大所以分页类无需更改只需要修改一下工具类

package cn.et.food.utils;

public class PartPageTool {
	public static PartPageDate getPartPageDate(Integer curPage,Integer total,Integer pageCount){
		PartPageDate ppd = new PartPageDate();
		//当前页
		ppd.setCurPage(curPage);
		//当前页
		ppd.setTotal(total);
		//每页显示的条数
		ppd.setPageCount((pageCount==null)?ppd.getPageCount():pageCount);
		//上一页
		ppd.setPrePage((ppd.getCurPage()-1)==0?1:ppd.getCurPage()-1);
		int mode = ppd.getTotal()%ppd.getPageCount();
		int count = ppd.getTotal()/ppd.getPageCount();
		//总页数
		ppd.setTotalPage(mode==0?count:(count+1));
		//下一页
		ppd.setNextPage((ppd.getCurPage().equals(ppd.getTotalPage()))?ppd.getTotalPage():ppd.getCurPage()+1);
		//开始索引 由于开始索引从0开始所有无需加1
		ppd.setStartIndex((ppd.getCurPage()-1)*ppd.getPageCount());
		//结束索引 mysql后一个数据不是结束索引而是显示的数量，所以这里将每页显示的数量直接赋值
		ppd.setEndIndex(ppd.getPageCount());
		return ppd;
	}
}

dao层

public List<Map<String, Object>> queryUser(String uname,PartPageDate partPageDate){
	String sql = "SELECT * FROM users WHERE uname LIKE '%"+uname+"%'  LIMIT "+partPageDate.getStartIndex()+","+partPageDate.getEndIndex();
	return jdbc.queryForList(sql);
}

Controller层一样的