JSP+Servlet+JDBC实现数据分页
环境
MyEclipse 8.6 + MySQL 5.1 + MySQL命令行工具+ JDK 1.6
问题
JSP+Servlet+JDBC实现分页
解决
第一步 创建数据库
以下为数据库导出脚本。
test.sql
-- MySQL dump 10.13 Distrib 5.1.43, for Win32 (ia32)---- Host: localhost Database: test-- -------------------------------------------------------- Server version5.1.43-community/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `test`--DROP TABLE IF EXISTS `test`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `test`--LOCK TABLES `test` WRITE;/*!40000 ALTER TABLE `test` DISABLE KEYS */;INSERT INTO `test` VALUES (1,'test1'),(2,'test2'),(3,'test3'),(4,'test4'),(5,'test5'),(6,'test6'),(7,'test7'),(8,'test8'),(9,'test9'),(10,'test10'),(11,'test1'),(12,'test2'),(13,'test3'),(14,'test4'),(15,'test5'),(16,'test6'),(17,'test7'),(18,'test8'),(19,'test9'),(20,'test10'),(26,'test1'),(27,'test2'),(28,'test3'),(29,'test4'),(30,'test5'),(31,'test6'),(32,'test7'),(33,'test8'),(34,'test9'),(35,'test10'),(36,'test1'),(37,'test2'),(38,'test3'),(39,'test4'),(40,'test5'),(41,'test6'),(42,'test7'),(43,'test8'),(44,'test9'),(45,'test10'),(57,'test1'),(58,'test2'),(59,'test3'),(60,'test4'),(61,'test5'),(62,'test6'),(63,'test7'),(64,'test8'),(65,'test9'),(66,'test10'),(67,'test1'),(68,'test2'),(69,'test3'),(70,'test4'),(71,'test5'),(72,'test6'),(73,'test7'),(74,'test8'),(75,'test9'),(76,'test10'),(77,'test1'),(78,'test2'),(79,'test3'),(80,'test4'),(81,'test5'),(82,'test6'),(83,'test7'),(84,'test8'),(85,'test9'),(86,'test10'),(87,'test1'),(88,'test2'),(89,'test3'),(90,'test4'),(91,'test5'),(92,'test6'),(93,'test7'),(94,'test8'),(95,'test9'),(96,'test10'),(97,'900');/*!40000 ALTER TABLE `test` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2013-01-22 11:17:45
Test.java
package com.wgb.bean;public class Test {private int id;private String name;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}}
第三步 编写数据库连接工具类
ConnDB.java
package com.wgb.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/** * 类名：ConnDB * 功能：连接数据库的类 * 开发日期：2012年5月21日 * 修复日期：2012年6月9日16:44:01 2012年6月14日20:26:25 * 原因：修改注释 换另一种方式连接数据库 * @since JDK 1.6 * @author Wentasy * @version 1.0 */public class ConnDB {//数据库连接对象private static Connection conn = null;//连接数据库驱动名private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";//连接数据库URLprivate static final String URL = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF-8";//数据库用户名private static final String USER_NAME = "root";//用户密码private static final String PASSWORD = "root";/** * 得到连接 * @return 连接对象 */public static Connection getConn(){try {Class.forName(DRIVER_NAME);conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);} catch (Exception e) {e.printStackTrace();}return conn;}/** * 关闭结果集对象 * @param rs 结果集 * @throws SQLException */public static void close(ResultSet rs) throws SQLException{if(rs != null){rs.close();rs = null;}}/** * 关闭PreparedStatement对象 * @param pstmt 处理数据的对象 * @throws SQLException */public static void close(PreparedStatement pstmt) throws SQLException{if(pstmt != null){pstmt.close();pstmt = null;}}/** * 关闭连接对象 * @param conn 连接对象 * @throws SQLException */public static void close(Connection conn) throws SQLException{if(conn != null){conn.close();conn = null;}}}
第四步 编写Servlet并在web.xml中注册
UserAction.java
package com.test.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.servlet.RequestDispatcher;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.wgb.bean.Test;import com.wgb.util.ConnDB;public class UserAction extends HttpServlet {private static final int DATA_PER_PAGE = 5;/** * Constructor of the object. */public UserAction() {super();}/** * Destruction of the servlet. <br> */public void destroy() {super.destroy(); // Just puts "destroy" string in log// Put your code here}/** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doPost(request, response);}/** * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {String cur = (String)request.getParameter("cur");List<Test> list = new ArrayList<Test>();list = new UserAction().getAllData(Integer.parseInt(cur));int totalPage = new UserAction().getTotalPage();request.setAttribute("tests", list);request.setAttribute("totalPage", totalPage);RequestDispatcher rd = request.getRequestDispatcher("test_list.jsp");rd.forward(request, response);}/** * Initialization of the servlet. <br> * * @throws ServletException if an error occurs */public void init() throws ServletException {// Put your code here}public int getTotalPage(){Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;String sql = "";int count = 0;try {sql = "select count(*) from test";conn = ConnDB.getConn();pstmt = conn.prepareStatement(sql);rs = pstmt.executeQuery();while(rs.next()){count = rs.getInt(1);}count = (int)Math.ceil((count + 1.0 - 1.0) / DATA_PER_PAGE);} catch (Exception e) {e.printStackTrace();}finally{try {ConnDB.close(rs);ConnDB.close(pstmt);ConnDB.close(conn);} catch (SQLException e) {e.printStackTrace();}}return count;}public List<Test> getAllData(int cur){List<Test> list = new ArrayList<Test>();Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;String sql = "";try {sql = "select * from test where 1 limit ?,?";conn = ConnDB.getConn();pstmt = conn.prepareStatement(sql);pstmt.setInt(1, (cur - 1) * DATA_PER_PAGE);pstmt.setInt(2, DATA_PER_PAGE);rs = pstmt.executeQuery();while(rs.next()){Test test = new Test();int id = rs.getInt(1);String name = rs.getString(2);test.setId(id);test.setName(name);list.add(test);}} catch (Exception e) {e.printStackTrace();}finally{try {ConnDB.close(rs);ConnDB.close(pstmt);ConnDB.close(conn);} catch (SQLException e) {e.printStackTrace();}}return list;}public static void main(String[] args) {System.out.println(new UserAction().getTotalPage());List<Test> list = new ArrayList<Test>();list = new UserAction().getAllData(3);for (Test test : list) {System.out.println(test.getName());}}}
web.xml
<?xml version="1.0" encoding="UTF-8"?><web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <servlet-name>UserAction</servlet-name> <servlet-class>com.test.servlet.UserAction</servlet-class> </servlet> <servlet-mapping> <servlet-name>UserAction</servlet-name> <url-pattern>/UserAction</url-pattern> </servlet-mapping> </web-app>
因测试，此处把分页要用到的方法写到了Servlet里。MySQL分页无非用到limit关键字，需要当前是多少页，总共的页数和每页显示的页数。当前是多少页可以由页面传递，总共的页数可以有数据库的记录数除以每页显示的页数得到，每页显示的页数此处固定。所以getTotalPage方法用于获得总页数，getAllData根据当前的页数获得数据。MySQL分页参考本文：http://blog.csdn.net/wentasy/article/details/8200512
第五步 编写显示页面
test_list.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <base href="<%=basePath%>"> <title>My JSP 'test_list.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">--> </head> <body> <ul><c:forEach items="${requestScope.tests}" var="p"><li>用户名：${p.name}</li></c:forEach></ul><c:if test="${param.cur == 1}"><a>首页</a><a>上一页</a></c:if><c:if test="${param.cur != 1}"><a href="UserAction?cur=1">首页</a><a href="UserAction?cur=${param.cur - 1}">上一页</a></c:if><c:if test="${param.cur == requestScope.totalPage}"><a>下一页</a><a>尾页</a></c:if><c:if test="${param.cur != requestScope.totalPage}"><a href="UserAction?cur=${param.cur + 1}">下一页</a><a href="UserAction?cur=${requestScope.totalPage}">尾页</a></c:if><p>当前第${param.cur}页 总共${requestScope.totalPage}页</p> </body></html>
访问URL：http://localhost:8088/JSP/UserAction?cur=1
参考资料
Mysql导出表结构及表数据mysqldump用法
http://www.cnblogs.com/yuanyouqi/archive/2010/04/28/1722738.html
@Wentasy 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]