【Servlet+JSP+MySQL】保姆级分页查询例子

最新推荐文章于 2023-05-06 22:56:01 发布

菜鸟冰冰

最新推荐文章于 2023-05-06 22:56:01 发布

阅读量259

点赞数

分类专栏：小栗子文章标签： mysql jsp servlet

本文链接：https://blog.csdn.net/yanglalababy/article/details/115361168

版权

小栗子专栏收录该内容

2 篇文章 0 订阅

订阅专栏

(•ө•)♡　bean包
- · User.java：user模型
(•ө•)♡　dao包
- · DaoConnection.java：与数据库连接的工具方法
- · UserDao.java：调用工具方法，与数据库交互获取数据
(•ө•)♡　servlet（sample）包
- · Search.java：调用UserDao的方法获取想要的数据，与jsp页面进行交互
- · InsertUser.java：往数据库插入随机测试数据的小工具
(•ө•)♡　WebContent
(•ө•)♡　<项目目录图>

(•ө•)♡　bean包

· User.java：user模型

public class User {

   private int id;
   private String name;
   private String sex;
   private int age;
   private String address;

   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;
   }
   public String getSex() {
   	return sex;
   }
   public void setSex(String sex) {
   	this.sex = sex;
   }
   public int getAge() {
   	return age;
   }
   public void setAge(int age) {
   	this.age = age;
   }
   public String getAddress() {
   	return address;
   }
   public void setAddress(String address) {
   	this.address = address;
   }
}

(•ө•)♡　dao包

· DaoConnection.java：与数据库连接的工具方法

package dao;

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

/**
 * 负责连接数据库
 */
public class DaoConnection {

	//自己数据库的用户名和密码
	static final String USER = "root";
	static final String PASS = "root";
	static final String URL = "jdbc:mysql://localhost:3306/space";
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

    public Connection getConnection() throws Exception{

		Connection conn = null;
		Class.forName(JDBC_DRIVER);
		conn = DriverManager.getConnection(URL,USER,PASS);

		return conn;
    }
}

· UserDao.java：调用工具方法，与数据库交互获取数据

package dao;

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

import bean.User;

public class UserDao extends DaoConnection {
	
	//查找符合条件的用户
	public List<User> search(String name, int startIndex, int pageSize) throws Exception {

		List<User> list = new ArrayList<>();
		Connection con = null;
		PreparedStatement st = null;

		try {
			con = getConnection();

			st = con.prepareStatement("select * from user where name like '%" + name + "%' limit " + startIndex + "," + pageSize);
			ResultSet rs = st.executeQuery();

			while (rs.next()) {
				User User = new User();
				User.setId(rs.getInt("id"));
				User.setName(rs.getString("name"));
				User.setSex(rs.getString("sex"));
				User.setAge(rs.getInt("age"));
				User.setAddress(rs.getString("address"));
				list.add(User);
			}
		} finally {
			if (st != null) {
				st.close();
			}
			if (con != null) {
				con.close();
			}
		}
		return list;
	}

	//查找符合条件的数据已共有多少条
	public int getCount(String name) throws Exception {

		int count = 0;
		List<User> list = new ArrayList<>();

		Connection con = null;
		PreparedStatement st = null;

		try {
			con = getConnection();

			st = con.prepareStatement("select * from user where name like '%" + name + "%'");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				User User = new User();
				User.setId(rs.getInt("id"));
				User.setName(rs.getString("name"));
				User.setSex(rs.getString("sex"));
				User.setAge(rs.getInt("age"));
				User.setAddress(rs.getString("address"));
				list.add(User);
			}
			count = list.size();

		} finally {
			if (st != null) {
				st.close();
			}
			if (con != null) {
				con.close();
			}
		}
		return count;
	}
}

(•ө•)♡　servlet（sample）包

· Search.java：调用UserDao的方法获取想要的数据，与jsp页面进行交互

package sample;

import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.User;
import dao.UserDao;

public class Search extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		// 从jsp取得需要的数据
		String name = request.getParameter("name");		// 检索的书名
		String currentPageStr = request.getParameter("currentPage");	// 现在在第几页
		// 如果没取到值，说明是第一次检索，代入1
		if(currentPageStr == null || currentPageStr == ""){
			currentPageStr = "1";
		}
		// 转成int类型
		int currentPage = Integer.parseInt(currentPageStr);

		// 定义分页需要的变量
		int pageSize = 8;	// 每页展示五行数据
		int totalCount;		// 共有几行数据
		int totalPage;		// 共有几页
		int startIndex;		// 现在这页展示的数据是从第几行开始的

		try {

			// 连接数据库，首先调用计算共有多少条符合条件的数据的方法，返回值是int类型的数据条数
			UserDao dao = new UserDao();
			totalCount = dao.getCount(name);

			// 根据获取的数据条数和每页显示的数据条数来计算共有多少页
			if(totalCount % pageSize == 0){
				totalPage = totalCount / pageSize;
			}
			else{
				totalPage = totalCount / pageSize + 1;
			}

			// 根据要显示第几页来计算应该显示的数据中，第一行数据的坐标
			startIndex = (currentPage - 1) * pageSize;
			// 传入检索名，开始坐标和每页显示多少条数据，调用查找方法，获取符合条件的数据
			List<User> list = dao.search(name, startIndex, pageSize);

			// 向jsp页面传送必要的数据
			request.setAttribute("list", list);
			request.setAttribute("name", name);
			request.setAttribute("currentPage", currentPage);
			request.setAttribute("totalPage", totalPage);
			request.setAttribute("totalCount", totalCount);

		} catch (Exception e) {
			e.printStackTrace();
		}

		// 处理全部完成后，跳转到对应jsp页面
		request.getRequestDispatcher("index.jsp").forward(request, response);
	}
}

· InsertUser.java：往数据库插入随机测试数据的小工具

package sample;

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

public class InsertUser {
	public static void main(String[] args) throws Exception{

		//连接数据库
        Connection conn = null;
    	PreparedStatement st = null;
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/space","root","root");

        //备用数据，这样写好麻烦，感觉可以从txt文件导入，但都写完了就这样吧
        String[] lastName = {"赵","钱","孙","李","周","吴","郑","王","冯","陈","褚","卫","蒋","沈","韩","杨","朱","秦","尤","许","何","吕","施","张","孔","曹","严","华","金","魏","陶","姜"};
        String[] firstName = {"志高","豫兴","桥","恒","甜","喜","雪智","恒德","进福","楷君","石泉","启枝","循流","展鹏","新明","龙菲","兢业","弘道","怀德","米欣欣","凯","飞","月","越","和","梦"};
        String[] province = {"A省","B省","C省","D省","E省","F省","G省","H省","I省","J省","K省","L省","M省","N省","O省","P省","Q省","R省","S省","T省","U省","V省","W省","X省","Y省","Z省"};
        String[] city = {"宁城","龙城","吉城","屏城","善城","永城","清城","黎城","晋城","云城","麻城","姚市","谷市","沐市","同心市","桥市","永和市","周宁市"};
        String[] num = {"xxx","○○","●●●","○○○○","★","★○○"};
        String[] addr = {"街","区","号"};
        String[] addr2 = {"都市新城小区","凤来别墅","都市新苑","附属小区","附属家属院","长河大厦","朝阳家园小区","朝阳新城","长河湾","创业园","点晶小筑","德秀轩","耀华公寓","东升园","嘉华大厦"};
        String[] sexSel = {"男","女"};

        //创建表，如果已存在就删掉再建
        st = conn.prepareStatement("DROP TABLE IF EXISTS `user`;");
        st.executeUpdate();
        st = conn.prepareStatement("CREATE TABLE `user` (" +
								"  `id` int(11) NOT NULL AUTO_INCREMENT," +
								"  `name` varchar(45) DEFAULT NULL," +
								"  `sex` varchar(45) DEFAULT NULL," +
								"  `age` int(11) DEFAULT NULL," +
								"  `address` varchar(45) DEFAULT NULL," +
								"  PRIMARY KEY (`id`)" +
								") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;");
        st.executeUpdate();

        //往建好的表里添加数据
        for(int i=1; i<=100; i++){

        	//调用获得乱数下标的方法，随机整一些数据
            String name = lastName[ran(lastName.length)] + firstName[ran(firstName.length)];
            String address = province[ran(province.length)] + city[ran(city.length)] + num[ran(num.length)]
            		+ addr[ran(addr.length)] + addr2[ran(addr2.length)] + num[ran(num.length)];
            String sex = sexSel[ran(sexSel.length)];
            int age = ran(35)+18;

	        st = conn.prepareStatement("insert into user(name,sex,age,address) value('" + name + "','" + sex + "','" + age + "','" + address + "')");
	        st.executeUpdate();
        }
    }

	//获得乱数下标的方法
	public static int ran(int length){
		int num = 0;
		num = (int) (Math.random() * length);
		return num;
	}
}

(•ө•)♡　WebContent

sample文件夹下

· index.jsp：显示查询数据和分页情况的页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="bean.User, java.util.List"%>

<!-- ↓要用到EL表达式导入jstl文件 -->
<!-- 别忘了往lib文件夹里追加jstl文件，如果你的library里有的话当我没说 -->
<%@ 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">
<link rel="stylesheet" href="style.css">
<title>Insert title here</title>
</head>
<body>
<%
	List<User> list = (List<User>) request.getAttribute("list");	// 符合条件的检索结果
	Integer totalPage = (Integer) request.getAttribute("totalPage");	// 共有多少页
	Integer currentPage = (Integer) request.getAttribute("currentPage");	// 现在在第几页
	Integer totalCount = (Integer) request.getAttribute("totalCount");		//共有多少条数据
%>
<!-- 左边部分 -->
<div class="left">
	<div class="title">
		xxx管理系统
	</div>
</div>

<!-- 右边部分 -->
<div class="right">
	<form action="search" method="get">
	<table style="height:70px;font-weight:bold" >
		<tr align="left" >
			<td align="center" style="width:8%">
				名前
			</td>
			<td style="width:10%">
				<input type="text" name="name" value="${name }">
			</td>
			<td>
				<button type="submit">search</button>
			</td>
		</tr>
	</table>
	</form>
		<%
		/**
		*	如果列表不为null，即查询了的话，才显示下面的内容（查询了但没有查询到对应值的话，列表里是空：""）
		*
		*	原因 ： 执行的时候我们是先进这个jsp页面，点击查找按钮后进到java页面
		*	一开始打开这个jsp页面的时候，下面不应该显示第几页什么的。可以想象成百度呀谷歌的主页，输入点啥点查找后才会显示结果和第几页第几页...
		*	如果写了初始化方法把它们分开，或者查询和结果不在一个文件里写着的话，可以不做这个判断
		*	大家应该都理解但是还是说一下如果从java文件的网址进去的话，会显示下面的信息，因为在java文件中做了查找
		*
		*	从jsp页面进 ： http://localhost:8080/sample/sample/index.jsp
		*	从java页面进 ： http://localhost:8080/sample/sample/search
		*/
			if(list != null){
		%>
			<table>
				<tr><td colspan="6" align="right" style="font-size:12px">共查找到 <%=totalCount %> 条结果</td></tr>
				<tr align="center" style="background-color:#dfe6e9" >
					<th class="smallTh">id</th>
					<th class="smallTh">姓名</th>
					<th class="smallTh">性别</th>
					<th class="smallTh">年龄</th>
					<th class="bigTh">住址</th>
					<th class="midTh">处理</th>
				</tr>

				<!-- el表达式遍历输出取到的值 -->
				<c:forEach items="${list}" var="user">
				<tr align="center" >
					<td><c:out value="${user.id}"></c:out></td>
					<td><c:out value="${user.name}"></c:out></td>
					<td><c:out value="${user.sex}"></c:out></td>
					<td><c:out value="${user.age}"></c:out></td>
					<td align="left" ><c:out value="${user.address}"></c:out></td>
					<td>
						<input type="button" name="update" value="更新">&nbsp;
						<input type="button" name="delete" value="删除">
					</td>
				</tr>
				</c:forEach>

			</table>
			<br>

			<%
				// 如果当前页不是第一页的话，给首页和上一页的按钮超链接
				if(currentPage != 1){
			%>
			<a href="search?currentPage=1&name=${name }">首页</a>
			&nbsp;<a href="search?currentPage=${currentPage-1 }&name=${name }">上一页</a>&nbsp;
			<%
				// 没进if进else了的话，就说明当前页是第一页，不给其超链接（这个逻辑很简单吧）
				}else{

			%>
					首页
					&nbsp;上一页&nbsp;
			<%
				}
			%>

			<!-- forEach展示页码 1～totalPage 例共五页的话： 1 2 3 4 5 -->
			<!-- 用choose的when和otherwise来做判断，类似if else，如果是当前页，则不能选择，不给它超链接 -->
			<c:forEach var="p" begin="1" end="${totalPage }">
				<c:choose>
					<c:when test="${p==currentPage }">
						${p }&nbsp;
					</c:when>
					<c:otherwise>
						<a href="search?currentPage=${p }&name=${name }">${p }</a>&nbsp;
					</c:otherwise>
				</c:choose>
			</c:forEach>

			<%
				// 同理，如果当前页不是最后一页的话，给下一页和尾页按钮加上超链接
				if(currentPage != totalPage){
			%>
			&nbsp;<a href="search?currentPage=${currentPage+1 }&name=${name }">下一页</a>
			&nbsp;<a href="search?currentPage=${totalPage }&name=${name }">尾页</a>
			<%
				}else{
				// 进else了说明当前页是最后一页，不给超链接
			%>
					&nbsp;下一页
					&nbsp;尾页
			<%
				}
			}
		%>

</div>
</body>
</html>

· style.css：样式表（不用也可）

@CHARSET "UTF-8";
.smallTh {
	width: 95px;
}
.midTh {
	width: 150px;
}
.bigTh {
	width: 400px;
}
.title {
	color: white;
	font-family: 黑体;
	font-weight: bold;
	font-size:xx-large;
}.foot {
	padding-right: 5px;
	padding-left: 5px;
	padding-bottom: 5px;
	padding-top: 5px;
	border-top: #ffffff 1px solid;
	border-bottom: #ffffff 1px solid;
	font-size: 80%;
	color:#808080;
	background: #e5ecf9;
	text-align: center;x
}
body {
	font-family: Arial;
	color: black;
}
h1{
	padding: 20px;
}
.left {
	height: 100%;
	width: 25%;
	position: fixed;
	z-index: 1;
	top: 0;
	overflow-x: hidden;
	padding-top: 20px;
	left: 0;
	background-color: #596275;
}
.right {
	height: 100%;
	width: 75%;
	position: fixed;
	z-index: 1;
	top: 0;
	overflow-x: hidden;
	padding-top: 20px;
	right: 0;
}
.centered {
	position: absolute;
	top: 50%;
	left: 50%;
	transform: translate(-50%, -50%);
	text-align: center;
}
.centered img {
	width: 150px;
	border-radius: 50%;
}

Web-INF文件夹下

web.xml：配置路径

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" version="3.1">
  <servlet>
    <servlet-name>search</servlet-name>
    <servlet-class>sample.Search</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>search</servlet-name>
    <url-pattern>/sample/search</url-pattern>
  </servlet-mapping>
</web-app>

lib文件夹里可能需要的jar包

jstl：用el表达式需要这个包
mysql：我用的5.1.xx…，8的话需要改DaoConnection里加载sql的驱动，加个cj还是jc来着
servlet-api.jar：没有可以从tomcat文件夹里复制

(•ө•)♡　<项目目录图>

项目目录
不可能再简单了

菜鸟冰冰

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
【Servlet+JSP+MySQL】保姆级分页查询例子

目录(•ө•)♡　bean包· User.java：user模型(•ө•)♡　dao包· DaoConnection.java：与数据库连接的工具方法· UserDao.java：调用工具方法，与数据库交互获取数据(•ө•)♡　servlet（sample）包· Search.java：调用UserDao的方法获取想要的数据，与jsp页面进行交互· InsertUser.java：往数据库插入随机测试数据的小工具(•ө•)♡　WebContentsample文件夹下· index.jsp：显示查询数据和分页
复制链接

扫一扫