JSP+Servlet+mysql实现原生分页

   

简单实现了分页、删除、更新操作，原生分页其实很简单，只需要获取当前页数以及每页显示条数，通过后台查出当前页数据存入List集合中，然后request.setAttribute()将结果反馈给jsp

整个工程输入localhost:8080/Page/PageServlet即可访问

数据库cby    localhost：3306

user    user_id    username

数据库连接

package com.dao;

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

public class DbConnection {
	
	private static Connection conn=null;  
	private String driver="com.mysql.jdbc.Driver";  
	private String url="jdbc:mysql://localhost:3306/cby?useUnicode=true&characterEncoding=UTF-8";  
	private String user="root";  
	private String password="123";  
	   
	private DbConnection(){  
	  try {  
	   Class.forName(driver);  
	   conn=DriverManager.getConnection(url, user, password);  
	  } catch (ClassNotFoundException e) {  
	   e.printStackTrace();  
	  } catch (SQLException e) {  
	   e.printStackTrace();  
	  }  
	 }  
	   
	 public static Connection getConnection() {  
	  if(conn==null){  
	   new DbConnection();  
	  }  
	  return conn;  
	 }
	

}

DAO

package com.dao;

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

import com.bean.User;

public class PageDao {
	
	private static Connection conn;  
	private static ResultSet rs;  
	private static PreparedStatement ps;
	private static int pagesize = 3;
	
	static{
		conn = DbConnection.getConnection();
	}

	//遍历当前页信息
	public static ArrayList<User> getUserList(int pageNo) {
		// TODO Auto-generated method stub
		ArrayList<User> list = new ArrayList<User>();
		int begin = (pageNo-1)*pagesize;
		int end = pagesize;
		try {
			ps = conn.prepareStatement("SELECT user_id,username from user LIMIT "+begin+","+end);
			rs = ps.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setUser_id(rs.getInt(1));
				user.setUsername(rs.getString(2));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	//获取总信息条数，计算总页数
	public static int getTotalPage() {
		// TODO Auto-generated method stub
		int totalCount = 0;
		int totalPage = 0;
		try {
			ps = conn.prepareStatement("select count(user_id) from user");
			rs = ps.executeQuery();
			if(rs.next()){
				totalCount = rs.getInt(1);
				totalPage = (totalCount-1)/pagesize+1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return totalPage;
	}
	
	//删除
	public static void delete(int id){
		try {
			ps = conn.prepareStatement("delete from user where user_id = "+id);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//更新
	public static void update(int id,String name){
		try {
			ps = conn.prepareStatement("update user set username = "+name+" where user_id = "+id);
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

实体类

package com.bean;

public class User {
	
	private int user_id;
	
	private String username;

	public int getUser_id() {
		return user_id;
	}

	public void setUser_id(int user_id) {
		this.user_id = user_id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

}

分页Servlet

package com.servlet;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bean.User;
import com.dao.PageDao;

public class PageServlet extends HttpServlet{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
		//获取当前页码
		String currentPageNo = request.getParameter("currentpageno");
		int pageNo = 1;
		if(currentPageNo!=null){
			pageNo = Integer.parseInt(currentPageNo);
		}
		
		ArrayList<User> userList=PageDao.getUserList(pageNo);
			
		request.setAttribute("userList", userList);  
		request.setAttribute("currentpageno", pageNo);  
		request.setAttribute("totalPage", PageDao.getTotalPage());
		request.getRequestDispatcher("/showUserList.jsp").forward(request, response);  
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response)  
			   throws ServletException, IOException {  
			           doGet(request,response);  
	} 

}

删除Servlet

package com.servlet;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bean.User;
import com.dao.PageDao;

public class deleteServlet extends HttpServlet{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	public void doGet(HttpServletRequest request,HttpServletResponse response)throws ServletException, IOException{
		
		String id = request.getParameter("user_id");
		//删除操作
		PageDao.delete(Integer.parseInt(id));
		//仍然是分页
		String currentPageNo = request.getParameter("currentpageno");
		int pageNo = 1;
		if(currentPageNo!=null){
			pageNo = Integer.parseInt(currentPageNo);
		}
		ArrayList<User> userList=PageDao.getUserList(pageNo);
		request.setAttribute("userList", userList);
		request.setAttribute("currentpageno", pageNo);  
		request.setAttribute("totalPage", PageDao.getTotalPage());
		request.getRequestDispatcher("/showUserList.jsp").forward(request, response);
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response)  
			   throws ServletException, IOException {  
			           doGet(request,response);  
	}

}

跳转更新页面

package com.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class updateForward extends HttpServlet{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	public void doGet(HttpServletRequest request,HttpServletResponse response)throws ServletException, IOException{
		String id = request.getParameter("user_id");
		request.setAttribute("userId", id);
		request.getRequestDispatcher("/updateUser.jsp").forward(request, response);
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response)  
			   throws ServletException, IOException {  
			           doGet(request,response);  
	}

}

更新Servlet

package com.servlet;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bean.User;
import com.dao.PageDao;

public class updateServlet extends HttpServlet{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public void doGet(HttpServletRequest request,HttpServletResponse response)
			throws ServletException, IOException{
		this.doPost(request,response);
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response)  
			   throws ServletException, IOException {  
		request.setCharacterEncoding("UTF-8"); 
		String userId = request.getParameter("userId");
		String username = request.getParameter("userName");
		PageDao.update(Integer.parseInt(userId), username);
		
		String currentPageNo = request.getParameter("currentpageno");
		int pageNum = 1;
		if(currentPageNo!=null){
			pageNum = Integer.parseInt(currentPageNo);
		}
		ArrayList<User> userList=PageDao.getUserList(pageNum);
		request.setAttribute("userList", userList);
		request.setAttribute("currentpageno", pageNum);  
		request.setAttribute("totalPage", PageDao.getTotalPage());
		request.getRequestDispatcher("/showUserList.jsp").forward(request, response);
	}

}

分页JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c"  uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Page</title>
</head>
<body>
<table align="center" style="width: 60%;" border="1">
	<tr>
		<th>用户编号</th>
		<th>用户姓名</th>
		<th>操作</th>
	</tr>
	<c:forEach var="list" items="${userList}">
		<tr>
			<td class="altbg2" align="center">${list.user_id}</td>
			<td align="center" class="altbg2">${list.username}</td>
			<td><a href="deleteServlet?user_id=${list.user_id}">删除</a>
				<a href="updateForward?user_id=${list.user_id}">修改</a>
			</td>
		</tr>
	</c:forEach>
</table>
<c:if test="${currentpageno>1&¤tpageno<totalPage}">  
   <a href="PageServlet?currentpageno=1">首页</a>  
   <a href="PageServlet?currentpageno=${currentpageno-1}">上一页</a>
   <a href="PageServlet?currentpageno=${currentpageno+1}">下一页</a>
   <a href="PageServlet?currentpageno=${totalPage}">尾页</a>
</c:if>
<c:if test="${currentpageno==totalPage}">
	<a href="PageServlet?currentpageno=1">首页</a>  
	<a href="PageServlet?currentpageno=${currentpageno-1}">上一页</a>
</c:if>
<c:if test="${currentpageno==1}">  
   <a href="PageServlet?currentpageno=${currentpageno+1}">下一页</a>  
   <a href="PageServlet?currentpageno=${totalPage}">尾页</a>  
</c:if>

</body>

</html>

更新页面

<%@ 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">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>addUser</title>
</head>
<body>
<form action="updateUser" method="post">
	用户编号：<input type="text" name="userId" readonly="readonly" value="${userId}"/>
	用户姓名：<input type="text" name="userName"/>
	<input id="update" type="submit"/>

</form>
</body>
</html>

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"> 
  <servlet>
  	<servlet-name>PageServlet</servlet-name>
  	<servlet-class>com.servlet.PageServlet</servlet-class>  
  </servlet>
  
  <servlet-mapping>
  	<servlet-name>PageServlet</servlet-name>
  	<url-pattern>/PageServlet</url-pattern>  
  </servlet-mapping>
  
  <servlet>
  	<servlet-name>deleteServlet</servlet-name>
  	<servlet-class>com.servlet.deleteServlet</servlet-class>  
  </servlet>
  
  <servlet-mapping>
  	<servlet-name>deleteServlet</servlet-name>
  	<url-pattern>/deleteServlet</url-pattern>  
  </servlet-mapping>
  
  <servlet>
  	<servlet-name>updateForward</servlet-name>
  	<servlet-class>com.servlet.updateForward</servlet-class>  
  </servlet>
  
  <servlet-mapping>
  	<servlet-name>updateForward</servlet-name>
  	<url-pattern>/updateForward</url-pattern>  
  </servlet-mapping>
  
  <servlet>
  	<servlet-name>updateServlet</servlet-name>
  	<servlet-class>com.servlet.updateServlet</servlet-class>  
  </servlet>
  
  <servlet-mapping>
  	<servlet-name>updateServlet</servlet-name>
  	<url-pattern>/updateUser</url-pattern>  
  </servlet-mapping>
  
</web-app>