在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_>=1
1位置:起始索引位置。
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 }">
<a
href="${page.url}?pg=${p }">${p}
</a>
</c:forEach>
共[${page.totalPage }]页 共${page.totalRec }条记录
跳转到 <input type="text" id="page" style="width:70px"
οnchange="doChange(this, '${page.totalPage }')" />
<input type="button" name="go" οnclick="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><a
href="${pageContext.request.contextPath }/servlet/UpdateServlet?id=${user.id}">修改</a>
<a
href="${pageContext.request.contextPath }/servlet/DeleteServlet?id=${user.id}">删除</a>
<a
href="${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 block
e.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 block
throw 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)
*/
@Override
public void add(User user) throws DaoException{
UserDao userDao = UserDaoFactory.newInstance().createUserDao();
userDao.add(user);
}
@Override
public List<User> getAll() throws DaoException {
// TODO Auto-generated method stub
UserDao 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);
}
@Override
public User getUserById(String id) throws DaoException {
// TODO Auto-generated method stub
UserDao userDao = UserDaoFactory.newInstance().createUserDao();
return userDao.getUserById(id);
}
@Override
public void update(User user) throws DaoException {
// TODO Auto-generated method stub
UserDao userDao = UserDaoFactory.newInstance().createUserDao();
userDao.update(user);
}
@Override
public void delete(String id) throws DaoException {
// TODO Auto-generated method stub
UserDao 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 block
throw 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)
*/
@Override
public User find(String id){
return null;
}
/* (non-Javadoc)
* @see cn.itcast.dao.impl.UserDao#add(cn.itcast.domain.User)
*/
@Override
public 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)
*/
@Override
public 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)
*/
@Override
public List<User> getAll() throws DaoException {
// TODO Auto-generated method stub
Connection 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 stub
Connection 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);
}
}
@Override
public User getUserById(String id) throws DaoException {
// TODO Auto-generated method stub
Connection 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);
}
}
@Override
public void delete(String id) throws DaoException {
// TODO Auto-generated method stub
Connection 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.Driver
username=root
password=root
url=jdbc\:mysql\://localhost\:3306/mydb1