servlet+jdbc+sql分页

NewsServlet.java

package com.zucc.servlet;


import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.zucc.model.News;
import com.zucc.service.NewsService;
import com.zucc.service.ThemeService;
import com.zucc.util.PageUtil;

public class NewsServlet extends HttpServlet{
	
	News news = new News();
	ThemeService themeService = new ThemeService();
	NewsService newsService = new NewsService();
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		req.setCharacterEncoding("utf-8");
		//获得页面传递过来的当前页数，如果为空值，则赋值为1；不为空，赋值为传递的值
			int pageNum = req.getParameter("pageNum")!=null ? Integer.parseInt(req.getParameter("pageNum")) : 1;
			int listSum = newsService.listSum();//获取新闻总数
			int pageSum = PageUtil.getPageSum(listSum);//获取页面总数
			req.setAttribute("pageSum", pageSum);
			req.setAttribute("pageNum", pageNum);
			req.setAttribute("newsList",newsService.getNewsList(pageNum));
			req.getRequestDispatcher("newsList.jsp").forward(req, resp);


	}


	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doGet(req, resp);
	}

}

NewsService.java

package com.zucc.service;

import java.util.List;

import com.zucc.dao.NewsDao;
import com.zucc.model.News;

public class NewsService {

	NewsDao newsDao = new NewsDao();

	public List<News> getNewsList(int pageNum){
		return newsDao.newsList(pageNum);
	}
	
	public int listSum(){
		return newsDao.listCount();
	}
}

NewsDao.java

package com.zucc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zucc.model.News;
import com.zucc.util.PageUtil;

public class NewsDao {

	private PreparedStatement prst;
	private ResultSet rs;
	private Connection conn;

	public NewsDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public void myClose() {
		try {
			if (prst != null) {
				prst.close();
			}
			if (conn != null) {
				conn.close();
			}
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/*
	 * 使用limit对新闻list进行拆分
	 */
	publ
	public List<News> newsList(int pageNum){
		List<News> newsList = new ArrayList<News>();
		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/news?characterEncoding=utf8", "root", "123456");
			String sql = "select * from news limit ?,?";
			prst = conn.prepareStatement(sql);
			prst.setInt(1, (pageNum-1)*PageUtil.listNum);
			prst.setInt(2, PageUtil.listNum);
			rs = prst.executeQuery();
			while(rs.next()){
				News news = new News();
				news.setAuthor(rs.getString("author"));
				news.setContent(rs.getString("content"));
				news.setId(rs.getInt("id"));
				news.setTheme_id(rs.getInt("theme_id"));
				news.setTime(rs.getDate("time"));
				news.setTitle(rs.getString("title"));
				
				newsList.add(news);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			myClose();
		}
		return newsList;
	}

	/*
	 * 计算新闻总数
	 */
	public int listCount(){
		int listSum = 0;
		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/news?characterEncoding=utf8", "root", "123456");
			String sql = "select count(*) from news";
			prst = conn.prepareStatement(sql);
			rs = prst.executeQuery();
			while(rs.next()){
				listSum = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			myClose();
		}
		return listSum;
	}
}

PageUtil.java

package com.zucc.util;

public class PageUtil {

	public final static int listNum = 2; //每页显示的数量
	
	/*
	 * 计算总页数
	 */
	public static int getPageSum(int listSum){
		if(listSum%listNum==0){
			return listSum/listNum;
		}
		return listSum/listNum+1;
	}
}

newsList.jsp

<%@page import="com.zucc.model.Theme"%>
<%@page import="com.zucc.model.News"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <%
    	List<News> list = (List<News>)request.getAttribute("newsList");
    	int pageSum = (Integer)request.getAttribute("pageSum");
    	int pageNum = (Integer)request.getAttribute("pageNum");
    %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>编辑新闻--管理后台</title>
<link href="css/admin.css" rel="stylesheet" type="text/css" />
</head>
<body>
<%@ include file="util/top.jsp" %>
<div id="main">
 <%@ include file="util/left.jsp" %>
  <div id="opt_area">
		<%
			for(int i=0;i<list.size();i++){
				News news = list.get(i);
		%>
		<ul class="classlist">
		 <li><%=news.getTitle()%><span> 作者：
        <%=news.getAuthor()%>                                             
             <a href='newsUpdate.do?f=toUpdate&id=<%=news.getId()%>'>修改</a>      <a href='newsList.do?f=delete&id=<%=news.getId()%>'>删除</a> </span> </li>
		<% 
			}
		%>
		</ul>
	<li class='space'></li>
      <p align="right"> 
      	<%
      		if(pageNum>1){
      	%>
      	<a href="newsList.do?f=list&pageNum=1">首页</a>
      	<a href="newsList.do?f=list&pageNum=<%=pageNum-1%>">上一页</a> 
       	<%
      		}
       	%>
      	当前页数:[<%=pageNum%>/<%=pageSum%>] 
      	<%
      		if(pageNum<pageSum){
      	%>
       	<a href="newsList.do?f=list&pageNum=<%=pageNum+1%>">下一页</a> 
       	<a href="newsList.do?f=list&pageNum=<%=pageSum%>">末页</a> </p>
       	<%
      		}
       	%>
    </div>
</div>
<%@include file="util/botton.jsp" %>
</body>
<ml>