JSP+Servlet+JDBC实现数据分页

快来打我* 2022-04-01 03:10 162阅读 0赞

## 环境 ##

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 version	5.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";
    	//连接数据库URL
    	private 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][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">
    	
    
      </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][http_localhost_8088_JSP_UserAction_cur_1]

![测试页面显示效果][1358910170_2842.jpg]

## 参考资料 ##

Mysql导出表结构及表数据mysqldump用法

[http://www.cnblogs.com/yuanyouqi/archive/2010/04/28/1722738.html][http_www.cnblogs.com_yuanyouqi_archive_2010_04_28_1722738.html]

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a> &nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[http_blog.csdn.net_wentasy_article_details_8200512]: http://blog.csdn.net/wentasy/article/details/8200512
[http_localhost_8088_JSP_UserAction_cur_1]: http://localhost:8088/JSP/UserAction?cur=1
[1358910170_2842.jpg]: https://img-my.csdn.net/uploads/201301/23/1358910170_2842.jpg
[http_www.cnblogs.com_yuanyouqi_archive_2010_04_28_1722738.html]: http://www.cnblogs.com/yuanyouqi/archive/2010/04/28/1722738.html