专业实训第五天 后台开发的最后一天 java+jsp+mysql+数据分页的实现(Limit) + 酒店管理系统

本文介绍了一个简单的酒店用户信息数据库构建过程,并通过分页查询实现了数据的有效展示。包括数据库结构设计、实体类定义、数据访问层实现及前端页面显示等关键步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

建立数据库

数据库 HotelDB
表 Users

列名类型属性1属性2描述
idint主键自增编号
namevarchar20非空姓名
telvarchar11非空手机号
cardIdvarchar18非空身份证号

要求
创建54条用户数据
只做查询

数据库文件

DROP DATABASE IF EXISTS HotelDB;
CREATE DATABASE HotelDB;
USE HotelDB;
CREATE TABLE Users
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    tel VARCHAR(11) NOT NULL,
    cardId VARCHAR(18) NOT NULL
);
INSERT INTO Users VALUES(NULL,'张1','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张2','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张3','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张4','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张5','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张6','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张7','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张8','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张9','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张10','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张11','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张12','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张13','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张14','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张15','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张16','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张17','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张18','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张19','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张20','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张21','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张22','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张23','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张24','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张25','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张26','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张27','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张28','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张29','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张30','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张31','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张32','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张33','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张34','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张35','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张36','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张37','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张38','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张39','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张40','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张41','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张42','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张43','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张44','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张45','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张46','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张47','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张48','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张49','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张50','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张51','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张52','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张53','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张54','13000000000','123456789012345678');
SELECT * FROM Users;
#第1页
SELECT * FROM Users LIMIT 0,5;
#第2页
SELECT * FROM Users LIMIT 5,5;
#第3页
SELECT * FROM Users LIMIT 10,5;
#第4页
SELECT * FROM Users LIMIT 15,5;

page
(page - 1) * 5

实体类User

public class Users {
    private int id;
    private String name;
    private String tel;
    private String cardId;
    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 getTel() {
        return tel;
    }
    public void setTel(String tel) {
        this.tel = tel;
    }
    public String getCardId() {
        return cardId;
    }
    public void setCardId(String cardId) {
        this.cardId = cardId;
    }
    public Users() {
        super();
    }
    public Users(int id, String name, String tel, String cardId) {
        super();
        this.id = id;
        this.name = name;
        this.tel = tel;
        this.cardId = cardId;
    }
}

基本工具类

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

public class BaseDao {
    private String driver="com.mysql.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/HotelDB";
    private String name="root";
    private String pwd="123456";

    public Connection getCon(){
        Connection con = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, name, pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public void closeAll(Connection con, PreparedStatement ps, ResultSet rs){
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

数据访问类

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

public class UsersDao extends BaseDao {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    public ArrayList<Users> getUsers(int page){
        ArrayList<Users> list = new ArrayList<Users>();
        String sql = "select * from Users limit ?,5";
        try {
            con = this.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, (page-1)*5);
            rs = ps.executeQuery();
            while(rs.next()){
                Users user = new Users(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(con, ps, rs);
        }
        return list;
    }

    public int getAllPage(){
        int allPage = 0;
        String sql = "select count(id) from Users";
        try {
            con = this.getCon();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                int count = rs.getInt(1);
                allPage = count / 5;
                if (count % 5 != 0) {
                    allPage++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(con, ps, rs);
        }
        return allPage;
    }
}

index.jsp

<%@ page language="java" import="java.util.*, dao.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

UsersDao ud = new UsersDao();

//获取总页数
int allPage = ud.getAllPage();

//获取当前页
int currentPage = 1;
if(request.getParameter("page") != null){
    currentPage = Integer.parseInt(request.getParameter("page"));
}

//获取上一页
int prePage = currentPage - 1;
if(prePage == 0){
    prePage = 1;
}

//获取下一页
int nextPage = currentPage + 1;
if(nextPage > allPage){
    nextPage = allPage;
}

ArrayList<Users> list = ud.getUsers(currentPage);

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">

    <title>My JSP 'index.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">
    -->
    <script type="text/javascript">
      function selectPage(){
          var page = document.getElementById("slt").value;
          location.href="index.jsp?page="+page;
      }

      function txtPage(allPage){
          var page = document.getElementById("txt").value;
          if(page == ""){
              alert("页码不能为空");
          } else if(isNaN(page) == true){
              alert("页码必须是纯数字");
          } else if(page < 1 || page > allPage) {
              alert("页码超出范围");
          } else {
              location.href="index.jsp?page="+page;
          }
      }
    </script>
  </head>

  <body>
    <table border="1" width="600px">
      <tr>
        <th>编号</th>
        <th>姓名</th>
        <th>手机号</th>
        <th>身份证号</th>
      </tr>

      <%for(Users user : list){ %>
        <tr>
          <td><%=user.getId() %></td>
          <td><%=user.getName() %></td>
          <td><%=user.getTel() %></td>
          <td><%=user.getCardId() %></td>
        </tr>
      <%} %>

    </table>
    <a href="index.jsp?page=1">首页</a>
    <a href="index.jsp?page=<%=prePage %>">上一页</a>

    <%if(currentPage <= 3){
        for(int i = 1; i <= currentPage + 2; i++){
            %>
            <a href="index.jsp?page=<%=i %>"><%=i %></a>
            <%
        }
        %>...<%
    } else if(currentPage >= allPage - 2){
        %>...<%
        for(int i = currentPage - 2; i <= allPage; i++){
            %>
            <a href="index.jsp?page=<%=i %>"><%=i %></a>
            <%
        }
    } else {
        %>...<%
        for(int i = currentPage - 2; i <= currentPage + 2; i++){
            %>
            <a href="index.jsp?page=<%=i %>"><%=i %></a>
            <%
        }
        %>...<%
    }
    %>

    <a href="index.jsp?page=<%=nextPage %>">下一页</a>
    <a href="index.jsp?page=<%=allPage %>">尾页</a>
    <br/>

    总页数:<%=currentPage %>/<%=allPage %><select onchange="selectPage()" id="slt">
      <%for(int i = 1; i <= allPage; i++){ %>
        <option value="<%=i %>" 
          <%if(i == currentPage){%>
            selected="selected"
          <%} %>
         ><%=i %></option>
      <%} %>
    </select>

    <input id="txt" style="width:40px" />
    <input type="button" value="Go" onclick="txtPage(<%=allPage %>)"/>
  </body>
</html>

多种分页方式不会一起全部使用,但是会组合使用,多敲代码,一定要多敲代码。。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值