分页

# pageSize, 每页显示多少条记录
# pageNow, 用户指定的当前页
# rowCount, 总记录条数, 查数据库
# pageCount, 多少页, 由pageSize和rowCount计算获取

总之, 显示第 n 页的sql: SELECT * FROM user LIMIT pageSize*(n - 1), pageSize;

说明: 以下的代码只是为了说明分页的原理, 并没有考虑代码的布局和结构

数据库表:

CREATE TABLE `user` (
`userId` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(32) NOT NULL,
`password` varchar(64) NOT NULL,
`email` varchar(50) DEFAULT NULL,
`grade` char(2) DEFAULT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=1010 DEFAULT CHARSET=utf8;

DBHelper.java:

package com.itcast.servlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBHelper {

// 数据库连接参数
public static final String DRIVERCLASS = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/servlet";
public static final String USRENAME = "root";
public static final String PASSWORD = "123456";

// Connection
public static Connection getConn() {
Connection conn = null;
try {
Class.forName(DRIVERCLASS);
conn = DriverManager.getConnection(URL, USRENAME, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}

// PrepareStatement
public static PreparedStatement prepare(Connection conn, String sql) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}

// 关闭数据库, 释放资源
public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement ps) {
if (ps != null) {
try {
ps.close();
ps = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

LoginServlet.java:

package com.itcast.servlet;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@SuppressWarnings("serial")
public class LoginServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// 设置编码
resp.setCharacterEncoding("GBK");

// 创建输出流
PrintWriter out = resp.getWriter();

// 创建登录页面
out.print("<html>");
out.print("<body>");
out.print("<form action='loginCl' method='POST'>");
out.print("用户名: <input type='text' name='userName'/><br/>");
out.print("密码: <input type='password' name='password'/><br/>");
out.print("<input type='submit' value='登录'/>");
out.print("</form>");
out.print("</body>");
out.print("</html>");
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
this.doGet(req, resp);
}
}

web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>

<servlet>
<servlet-name>login</servlet-name>
<servlet-class>com.itcast.servlet.LoginServlet</servlet-class>
</servlet>

<servlet>
<servlet-name>loginCl</servlet-name>
<servlet-class>com.itcast.servlet.LoginClServlet</servlet-class>
</servlet>

<servlet>
<servlet-name>welcome</servlet-name>
<servlet-class>com.itcast.servlet.WelcomeServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>login</servlet-name>
<url-pattern>/login</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>loginCl</servlet-name>
<url-pattern>/loginCl</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>welcome</servlet-name>
<url-pattern>/welcome</url-pattern>
</servlet-mapping>
</web-app>

LoginClServlet.java:

package com.itcast.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 javax.servlet.http.HttpSession;
@SuppressWarnings("serial")
public class LoginClServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// 获取参数
String userName = req.getParameter("userName");
String password = req.getParameter("password");

// 简单校验
if ("Peter".equals(userName) && "123".equals(password)) {
// 将用户信息存入session[在欢迎页面就可以通过查看session中是否存在该值
// 判断用户是否为非法登录]
HttpSession session = req.getSession(true);
session.setAttribute("userName", userName);
session.setAttribute("password", password);
session.setMaxInactiveInterval(30*60);

// 跳转到成功页面
resp.sendRedirect("welcome?userName=" + userName + "&password=" + password);
} else {
// 跳转到登录界面
resp.sendRedirect("login");
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
this.doGet(req, resp);
}
}

WelcomeServlet.java:

package com.itcast.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
@SuppressWarnings("serial")
public class WelcomeServlet extends HttpServlet {
@SuppressWarnings("resource")
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {

// 判断用户是否为非法登录
HttpSession session = req.getSession(true);
String userNameSession = (String) session.getAttribute("userName");
if (userNameSession == null) {
// 用户非法登录
resp.sendRedirect("login");
}

resp.setCharacterEncoding("GBK");
resp.setContentType("text/html;charset=UTF-8");
String userName = userNameSession;
String password = (String) session.getAttribute("password");
PrintWriter out = resp.getWriter();
out.print("<br/>");
out.print("<br/>");
out.print("<h4 align='center'>欢迎来访! 用户名: " + userName
+ ", 密码: " + password + "</h4>");

// ==================== 分页功能 ============================
int pageSize = 3; // 每页显示多少条记录, 可自定义
int pageNow = 1; // 当前页, 用户指定
int rowCount = 0; // 共有多少条记录, 查数据库
int pageCount = 0; // 共有多少页, 由pageSize和rowCount计算获得

// 数据库参数
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
conn = DBHelper.getConn();

// 获取rowCount
String sql_rowCount = "SELECT COUNT(*) FROM user";
ps = DBHelper.prepare(conn, sql_rowCount);
rs = ps.executeQuery();
if (rs.next()) {
rowCount = rs.getInt(1);
}

// 计算pageCount
if (rowCount % pageSize == 0) {
pageCount = rowCount / pageSize;
} else {
pageCount = rowCount / pageSize + 1;
}

// 进行分页
String dynamic_pageNow = req.getParameter("pageNow");
// 第一次进来是dynamic_pageNow=null, 则什么都不做, 保持pageNow=1
if (dynamic_pageNow != null) {
pageNow = Integer.parseInt(dynamic_pageNow);
}
String sql_page = "SELECT * FROM user LIMIT "
+ pageSize*(pageNow - 1)
+ ", "
+ pageSize;
ps = DBHelper.prepare(conn, sql_page);
rs = ps.executeQuery();

// 打印
out.print("<table border='1' cellspacing='0' "
+ "cellpadding='3' align='center'>");
out.print("<tr>"
+ "<th>userId</th>"
+ "<th>userName</th>"
+ "<th>password</th>"
+ "<th>eamil</th>"
+ "<th>grade</th>"
+ "</tr>");
while (rs.next()) {
out.print("<tr align='center'>");
out.print("<td>" + rs.getInt(1) + "</td>");
out.print("<td>" + rs.getString(2) + "</td>");
out.print("<td>" + rs.getString(3) + "</td>");
out.print("<td>" + rs.getString(4) + "</td>");
out.print("<td>" + rs.getInt(5) + "</td>");
out.print("</tr>");
}
out.print("</table><br/>");

// 显示页码超链接
out.print("<div align='center'>");
out.print("<a href='welcome?pageNow="
+ 1 + "'>首页</a>   ");
if (pageNow > 1) {
out.print("<a href='welcome?pageNow="
+ (pageNow - 1) + "'>上一页</a>   ");
}
// 声明一个参数, 作用: 最多显示到pageCount
int dy_pageNow = ((pageCount - pageNow) <= 5) ? pageCount : (pageNow + 5);
for (int i=pageNow; i<=dy_pageNow; i++) {
out.print("<a href='welcome?pageNow=" + i
+ "'>[" + i + "]</a>   ");
}
if (pageNow < pageCount) {
out.print("<a href='welcome?pageNow="
+ (pageNow + 1) + "'>下一页</a>");
}
out.print("   <a href='welcome?pageNow="
+ pageCount + "'>尾页</a><br/><br/>");
out.print("<form action='welcome' method='POST'>");
out.print("跳转至第 <input type='text' name='pageNow' style='width: 50px;'/>"　　//
+ " 页   ");
out.print("<input type='submit' value='Go'/>");
out.print("    当前页  " + pageNow);
out.print(" , 共  " + pageCount + "  页");
out.print("</form>");
out.print("</div>");
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.close(rs);
DBHelper.close(ps);
DBHelper.close(conn);
}

// ==================== 分页功能 ============================
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
this.doGet(req, resp);
}
}

测试:

posted @ 2017-03-12 17:47 半生戎马，共话桑麻、阅读(121) 评论(0) 收藏举报

刷新页面返回顶部

Hello_Joseph

分页

公告