数据库分页技术
来源：互联网 发布：86版西游记源码 编辑：程序博客网 时间：2024/06/11 22:34
在web页面上，当查询某项内容时，有时候会显示出很多内容，一个页面难于显示完全。需要分页显示，但是一次性将这些技术都读出来存放在内存，将耗费大量内存。主流的一些数据库，提供了分页技术：
MySQL分页技术实现:
Select * from table limit M,N M：记录开始索引位置N：取多少条记录Oracle分页语句：
select * from (select rownum r_, row_.* from (select * from student order by id) row_ where rownum <=5 ) where r_>=11位置：起始索引位置。5位置：结束索引位置完成WEB页面分页显示，需要：
-先获取需要分页显示的记录总数，然后再WEB页面中显示页码
-根据页码，从数据库中查询相应的记录显示在web页面中
以上两项操作通常使用Page对象进行封装。
//下面是封装的Page对象
//Page 类
package cn.itcast.domain;import java.util.List;public class Page {private int startPage; //WEB页面上显示的起始页和结束页private int endPage;private List<Object> list; //WEB页面上显示的对象列表public static int numPerPage; //每页需要显示的Object数量public static int pageShows; //每页显示页码数量public int totalRec; //数据库中Object存放的记录总数量public int curPage; //当前页面public int totalPage; //数据库中存放的Object对象记录总数，需要多少页来显示public String url; //在jsp页面超链接，跳转地址的URL对象/** * @return the url */public String getUrl() {return url;}/** * @param url the url to set */public void setUrl(String url) {this.url = url;}/** * @return the totalPage */public int getTotalPage() {return totalPage;}/** * @param totalPage the totalPage to set */public void setTotalPage(int totalPage) {this.totalPage = totalPage;}/** * @return the totalRec */public int getTotalRec() {return totalRec;}/** * @return the curPage */public int getCurPage() {return curPage;}/** * @param curPage the curPage to set */public void setCurPage(int curPage) {this.curPage = curPage;}/** * @param totalRec the totalRec to set */public void setTotalRec(int totalRec) {this.totalRec = totalRec;}static{numPerPage = 4;pageShows = 5;}public int getStartPage() {return startPage;}public void setStartPage(int startPage) {this.startPage = startPage;}public int getEndPage() {return endPage;}public void setEndPage(int endPage) {this.endPage = endPage;}public List<Object> getList() {return list;}public void setList(List<Object> list) {this.list = list;}}//WEB 页面分页显示jsp部分
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>当前页 ${page.curPage } <c:forEach var="p" begin="${page.startPage }" end="${page.endPage }"><ahref="${page.url}?pg=${p }">${p}</a></c:forEach> 共[${page.totalPage }]页 共${page.totalRec }条记录 跳转到 <input type="text" id="page" style="width:70px"onchange="doChange(this, '${page.totalPage }')" /><input type="button" name="go" onclick="goPage()" value="Go">//一个实际Demo，WEB页面显示User信息
//JSP显示层
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"contentType="text/html; charset=utf-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%><%@ taglib uri="/itcast" prefix="itcast" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><title>客户信息列表</title><script type="text/javascript"src="${pageContext.request.contextPath }/js/customer.js"></script><script type="text/javascript">function goPage(){var page = document.getElementById("page");var value = page.value;window.location.href='${pageContext.request.contextPath}/servlet/GoPageServlet?pg='+value;return;}</script></head><body style="text-align: center"><c:choose><c:when test="${empty(page.list)}"> 没有客户信息 </c:when><c:otherwise><table frame="border" width="80%" align="center"><hr><td>姓名</td><td>性别</td><td>类型</td><td>手机</td><td>邮箱</td><td>生日</td><td>爱好</td><td>备注</td><td>操作</td></hr><c:forEach var="user" items="${page.list }"><tr><td><c:out escapeXml="true" value="${user.name }" /></td><td><c:out escapeXml="true" value="${user.gender }" /></td><td><c:out escapeXml="true" value="${user.type }" /></td><td><c:out escapeXml="true" value="${user.cellphone }" /></td><td><c:out escapeXml="true" value="${user.email }" /></td><td><c:out escapeXml="true" value="${user.birthday }" /></td><td><c:out escapeXml="true" value="${user.prefences }" /></td><td><c:out escapeXml="true" value="${itcast:subString(user.description,10)}" /></td><td><ahref="${pageContext.request.contextPath }/servlet/UpdateServlet?id=${user.id}">修改</a><ahref="${pageContext.request.contextPath }/servlet/DeleteServlet?id=${user.id}">删除</a><ahref="${pageContext.request.contextPath }/servlet/QueryOneServlet?id=${user.id }">查看</a></td></tr></c:forEach></table><%@ include file="/WEB-INF/public/Page.jsp" %></c:otherwise></c:choose></body></html>//WEB层
public class QueryCustomerServlet extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {String cPage = (String)request.getParameter("pg");int pg = 1;if (cPage != null && !cPage.trim().equals("")){pg=Integer.parseInt(cPage);}BusinessService service = BusinessServiceFactory.newInstance().createService();List<User> list;int totalRec = 0;Page p = null;try {totalRec = service.getTotalRec();p = service.getPage(pg, totalRec);request.setAttribute("page", p);String url = request.getContextPath()+"/servlet/"+this.getServletName();p.setUrl(url);request.getRequestDispatcher("/WEB-INF/jsp/ListCustomer.jsp").forward(request, response);} catch (DaoException e) {// TODO Auto-generated catch blocke.printStackTrace();request.setAttribute("message", "查询失败");request.getRequestDispatcher("/message.jsp").forward(request, response);}}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}}//Service层
ServiceFactory类
public class BusinessServiceFactory {private static BusinessService service = null;private BusinessServiceFactory(){try {InputStream in = BusinessServiceFactory.class.getClassLoader().getResourceAsStream("service.properties");Properties prop = new Properties();prop.load(in);String className = prop.getProperty("service");service = (BusinessService) Class.forName(className).newInstance();} catch (Exception e) {// TODO Auto-generated catch blockthrow new RuntimeException(e);}}private static BusinessServiceFactory instance = new BusinessServiceFactory();public static BusinessServiceFactory newInstance(){return instance;}public BusinessService createService(){return service;}}//BusinessServiceImpl
public class BusinessServiceImpl implements BusinessService{/* (non-Javadoc) * @see cn.itcast.service.impl.BusinessService#add(cn.itcast.domain.User) */@Overridepublic void add(User user) throws DaoException{UserDao userDao = UserDaoFactory.newInstance().createUserDao();userDao.add(user);}@Overridepublic List<User> getAll() throws DaoException {// TODO Auto-generated method stubUserDao userDao = UserDaoFactory.newInstance().createUserDao();return userDao.getAll();}public int getTotalRec() throws DaoException{UserDao userDao = UserDaoFactory.newInstance().createUserDao();return userDao.getTotalRec();}public Page getPage(int page, int totalRec) throws DaoException{UserDao userDao = UserDaoFactory.newInstance().createUserDao();return userDao.getPage(page, totalRec);}@Overridepublic User getUserById(String id) throws DaoException {// TODO Auto-generated method stubUserDao userDao = UserDaoFactory.newInstance().createUserDao();return userDao.getUserById(id);}@Overridepublic void update(User user) throws DaoException {// TODO Auto-generated method stubUserDao userDao = UserDaoFactory.newInstance().createUserDao();userDao.update(user);}@Overridepublic void delete(String id) throws DaoException {// TODO Auto-generated method stubUserDao userDao = UserDaoFactory.newInstance().createUserDao();userDao.delete(id);}}//dao层
public class UserDaoFactory {private static UserDao userDao = null;private UserDaoFactory(){try {InputStream in = UserDaoFactory.class.getClassLoader().getResourceAsStream("dao.properties");Properties prop = new Properties();prop.load(in);String className = prop.getProperty("userDao");userDao = (UserDao) Class.forName(className).newInstance();} catch (Exception e) {// TODO Auto-generated catch blockthrow new RuntimeException(e);}}private static UserDaoFactory instance = new UserDaoFactory();public static UserDaoFactory newInstance(){return instance;}public static UserDao createUserDao(){return userDao;}}//DaoImpl
public class UserDaoJdbcImpl implements UserDao {/* (non-Javadoc) * @see cn.itcast.dao.impl.UserDao#find(java.lang.String) */@Overridepublic User find(String id){return null;}/* (non-Javadoc) * @see cn.itcast.dao.impl.UserDao#add(cn.itcast.domain.User) */@Overridepublic void add(User user) throws DaoException {Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();String sql = "insert into customer(id,name,gender,type,cellphone,birthday,email,prefences,description) value(?,?,?,?,?,?,?,?,?)";st = conn.prepareStatement(sql);st.setString(1, user.getId());st.setString(2, user.getName());st.setString(3, user.getGender());st.setString(4, user.getType());st.setString(5, user.getCellphone());st.setDate(6, new java.sql.Date(user.getBirthday().getTime()));st.setString(7, user.getEmail());st.setString(8, user.getPrefences());st.setString(9, user.getDescription());st.executeUpdate();} catch (Exception e) {//throw new RuntimeException(e);throw new DaoException(e);}finally{JdbcUtils.release(conn, st, rs);}}/* (non-Javadoc) * @see cn.itcast.dao.impl.UserDao#update(cn.itcast.domain.User) */@Overridepublic void update(User user) throws DaoException{Connection conn = null;PreparedStatement st = null;ResultSet rs = null;System.out.println(user.getCellphone());try {conn = JdbcUtils.getConnection();//String sql = "insert into customer(name,gender,type,cellphone,birthday,email,prefences,description,id) value(?,?,?,?,?,?,?,?,?)";String sql = "update customer set name=?, gender=?, type=?, cellphone=?, birthday=?, email=?, prefences=?, description=? where id=?";//String sql = "update customer set description=? where id=?";st = conn.prepareStatement(sql);st.setString(1, user.getName());st.setString(2, user.getGender());st.setString(3, user.getType());st.setString(4, user.getCellphone());st.setDate(5, new java.sql.Date(user.getBirthday().getTime()));st.setString(6, user.getEmail());st.setString(7, user.getPrefences());st.setString(8, user.getDescription());st.setString(9, user.getId());//System.out.println(user.getName()+" "+user.getGender()+" "+user.getType()//+ " "+user.getCellphone()+" "+new java.sql.Date(user.getBirthday().getTime())//+ " "+user.getEmail()+" "+user.getPrefences()+" "+user.getDescription()//+ " "+user.getId());st.executeUpdate();} catch (Exception e) {//throw new RuntimeException(e);throw new DaoException(e);}finally{JdbcUtils.release(conn, st, rs);}}/* (non-Javadoc) * @see cn.itcast.dao.impl.UserDao#delete(java.lang.String) */@Overridepublic List<User> getAll() throws DaoException {// TODO Auto-generated method stubConnection conn = null;PreparedStatement st = null;ResultSet rs = null;List<User> list = new ArrayList();try {conn = JdbcUtils.getConnection();String sql = "select * from customer";st = conn.prepareStatement(sql);st.executeQuery();rs = st.getResultSet();while(rs.next()){User user = new User();user.setName(rs.getString("name"));user.setGender(rs.getString("gender"));user.setType(rs.getString("type"));user.setEmail(rs.getString("email"));user.setCellphone(rs.getString("cellphone"));user.setPrefences(rs.getString("prefences"));user.setDescription(rs.getString("description"));user.setBirthday(rs.getDate("birthday"));list.add(user);}return list;} catch (Exception e) {//throw new RuntimeException(e);throw new DaoException(e);}finally{JdbcUtils.release(conn, st, rs);}}public int getTotalRec() throws DaoException{Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();String sql = "select count(*) from customer";st = conn.prepareStatement(sql);st.executeQuery();rs = st.getResultSet();if(rs!=null && rs.next()){return rs.getInt(1);}return 0;}catch (Exception e) {//throw new RuntimeException(e);throw new DaoException(e);}finally{JdbcUtils.release(conn, st, rs);}}public Page getPage(int page, int totalRec) throws DaoException {// TODO Auto-generated method stubConnection conn = null;PreparedStatement st = null;ResultSet rs = null;List<Object> list = new ArrayList();int startPage = 0;int endPage = 0;Page p = new Page();int totalPage = totalRec/Page.numPerPage;int lastRec = totalRec%Page.numPerPage;totalPage=(lastRec>0)?(totalPage+1):totalPage;try {conn = JdbcUtils.getConnection();String sql = "select * from customer limit ?,?";st = conn.prepareStatement(sql);if(totalPage<Page.pageShows){startPage = 1;endPage = totalPage;}else if(page-Page.pageShows/2<=0){startPage = 1;endPage = Page.pageShows;}else if(page+Page.pageShows/2>totalPage){endPage = totalPage;startPage = endPage-Page.pageShows;}else{startPage=page - Page.pageShows/2;endPage = page+Page.pageShows/2;}st.setInt(1, (page-1)*Page.numPerPage);st.setInt(2, Page.numPerPage);st.executeQuery();rs = st.getResultSet();while(rs.next()){User user = new User();user.setId(rs.getString("id"));user.setName(rs.getString("name"));user.setGender(rs.getString("gender"));user.setType(rs.getString("type"));user.setEmail(rs.getString("email"));user.setCellphone(rs.getString("cellphone"));user.setPrefences(rs.getString("prefences"));user.setDescription(rs.getString("description"));user.setBirthday(rs.getDate("birthday"));list.add(user);}p.setList(list);p.setEndPage(endPage);p.setStartPage(startPage);p.setTotalRec(totalRec);p.setCurPage(page);p.setTotalPage(totalPage);return p;} catch (Exception e) {//throw new RuntimeException(e);throw new DaoException(e);}finally{JdbcUtils.release(conn, st, rs);}}@Overridepublic User getUserById(String id) throws DaoException {// TODO Auto-generated method stubConnection conn = null;PreparedStatement st = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();String sql = "select * from customer where id='"+id+"'";st = conn.prepareStatement(sql);st.executeQuery();rs = st.getResultSet();if(rs.next()){User user = new User();user.setId(id);user.setName(rs.getString("name"));user.setGender(rs.getString("gender"));user.setType(rs.getString("type"));user.setEmail(rs.getString("email"));user.setCellphone(rs.getString("cellphone"));user.setPrefences(rs.getString("prefences"));user.setDescription(rs.getString("description"));user.setBirthday(rs.getDate("birthday"));return user;}return null;} catch (Exception e) {//throw new RuntimeException(e);throw new DaoException(e);}finally{JdbcUtils.release(conn, st, rs);}}@Overridepublic void delete(String id) throws DaoException {// TODO Auto-generated method stubConnection conn = null;PreparedStatement st = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();String sql = "delete from customer where id=?";st = conn.prepareStatement(sql);System.out.println(id);st.setString(1, id);st.executeUpdate();} catch (Exception e) {//throw new RuntimeException(e);throw new DaoException(e);}finally{JdbcUtils.release(conn, st, rs);}}}//User对象
public class User {private String id;private String name;private String gender;private String type;private String cellphone;private Date birthday;private String email;private String prefences;private String description;public String getId() {return id;}public void setId(String id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public String getType() {return type;}public void setType(String type) {this.type = type;}public String getCellphone() {return cellphone;}public void setCellphone(String cellphone) {this.cellphone = cellphone;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getPrefences() {return prefences;}public void setPrefences(String prefences) {this.prefences = prefences;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}}//配置文件
dao.properties
userDao=cn.itcast.dao.impl.UserDaoJdbcImpl
service.properties
service=cn.itcast.service.impl.BusinessServiceImpl
//driver.properties
driver=com.mysql.jdbc.Driverusername=rootpassword=rooturl=jdbc\:mysql\://localhost\:3306/mydb1
- 数据库分页技术
- 数据库中的分页技术
- 数据库分页技术
- jsp数据库分页技术
- 数据库分页技术
- 数据库分页技术
- 数据库分页技术
- 不同数据库分页技术
- 数据库分页技术总结:
- 数据库分页技术
- SQL 数据库分页技术
- 数据库分页技术
- jsp数据库查询分页技术
- sqlserver数据库的分页技术
- 不同数据库的分页技术
- Hibernate 数据库分页查询技术
- 简单的数据库分页技术
- 不同数据库的分页技术
- poj 2195 最小费用最大流
- XML语法基础(一)
- 集合的取出方式和保证元素的唯一性方式方法
- 为什么vb的load事件不能显示输出
- 运行时多态与编译时多态
- 数据库分页技术
- ubuntu升级后删除老版本的内核
- java静态代理和动态代理
- 基于visual Studio2013解决C语言竞赛题之1003字母打印
- face recognition matlab code 人脸识别
- java 线程同步
- 套接字IO模型
- java.lang.OutOfMemoryError: Java heap space
- Python - while语句和if语句 的 用法 及 代码
