学生信息显示，翻页显示&Google Suggest

User.java(Bean):

package beans;

public class User {
    private String uid;
    private String name;
    private double score;
    public void setUid(String uid){this.uid=uid;}

    public void setName(String name) {
        this.name = name;
    }

    public void setScore(double score) {
        this.score = score;
    }

    public String getName() {
        return name;
    }

    public String getUid() {
        return uid;
    }

    public double getScore() {
        return score;
    }
}

UserDao(DAO):

package Dao;
import Dao.UserDao;
import beans.User;
import jdbc.JDBCUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class UserDao {
    public int PageAll(int pageSize) throws Exception{
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        int t2;
        int recordCount;
        try{
        String sql = "select count(*) from user";
        ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        rs.next();
        recordCount = rs.getInt(1);
        t2 = recordCount / pageSize;
        }finally {JDBCUtil.free(null,ps, conn);}
        return t2;
    }

    public List<User> OnePage(int startRecno,int pageSize) throws Exception{
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        List<User> userList=new ArrayList<User>();
        int p;
        try{
            p=(startRecno-1)*pageSize;
            String sql = "select * from user  order by uid limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, p);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                User user = new User();
                user.setUid(rs.getString(1));
                user.setName(rs.getString(2));
                user.setScore((rs.getDouble(3)));
                userList.add(user);
            }
        }finally {JDBCUtil.free(null,ps, conn);}
        return userList;
    }
    public List<User> FindByName(String name) throws  Exception{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> userList=new ArrayList<User>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select * from user where name like ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            rs=ps.executeQuery();
            while(rs.next()){
                User b=new User();
                b.setUid(rs.getString(1));
                b.setName(rs.getString(2));
                b.setScore(rs.getDouble(3));
                userList.add(b);
            }}finally {
            JDBCUtil.free(null,ps, conn);
        }
    return userList;
}

}

JDBCUtil.java:(连接数据库用)

package jdbc;
import java.sql.*;
import java.util.Properties;
public final class JDBCUtil {
    private static String driver ;
    private static String url ;
    private static String user ;
    private static String password ;

    private JDBCUtil() {}
    //设计该工具类的静态初始化器中的代码，该代码在装入类时执行，且只执行一次
    static {
        try {
            Properties pro=new Properties();
            pro.load(JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            driver=pro.getProperty("driver");
            url=pro.getProperty("url");
            user=pro.getProperty("user");
            password=pro.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    //设计获得连接对象的方法getConnection()
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    //设计释放结果集、语句和连接的方法free()
    public static void free(ResultSet rs, Statement st, Connection conn) {
        try { if (rs != null) rs.close();   }
        catch (SQLException e) {    e.printStackTrace(); }
        finally {
            try { if (st != null) st.close();  }
            catch (SQLException e) {    e.printStackTrace(); }
            finally {
                try { if (conn != null)  conn.close();  }
                catch (SQLException e) {    e.printStackTrace();   }
            }
        }
    }
}

jdbc.properties:

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/students
user=root
password=12345678

main.jsp:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><meta charset="utf-8">
    <title>学生信息查询</title>
</head>
<frameset rows="80,*">
    <frame src="_top.jsp" name="top" scrolling="no">
    <frameset cols="200,*">
        <frame src="left.jsp" name="left" scrolling="no">
        <frame src="right.jsp" name="right" scrolling="auto">
    </frameset>
</frameset>
</html>

left.jsp:

<%--
  Created by IntelliJ IDEA.
  User: ssssssbbbbbb
  Date: 2022/11/6
  Time: 1:44 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <meta charset="utf-8">
    <title>菜单页面</title>
</head>
<body>
<br><br><br>
<br><br><br>
<p><a href="ShowAllUserSumbit.jsp?id=1" target="right">查询所有学生信息</a></p>
<p><a href="Search.jsp" target="right">按姓名查询学生信息</a></p>
</body>
</html>

right.jsp:

<%--
  Created by IntelliJ IDEA.
  User: ssssssbbbbbb
  Date: 2022/11/6
  Time: 2:01 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>

</body>
</html>

_top.jsp:

<%--
  Created by IntelliJ IDEA.
  User: ssssssbbbbbb
  Date: 2022/11/6
  Time: 1:43 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>学生信息查询系统</title>
</head>
<body>
<center>
    <h1><font color="red">学生信息查询</font></h1>
</center>
</body>
</html>

ShowAllUserSubmit.jsp:

<%--
  Created by IntelliJ IDEA.
  User: ssssssbbbbbb
  Date: 2022/11/4
  Time: 9:19 AM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title></title>
</head>
<body>
<%
    String currentpage=request.getParameter("id");
    int currentPage=Integer.parseInt(currentpage);
    int pagesize=12;//一页显示的信息条数
    try{
%>
<jsp:forward  page="findAll">
    <jsp:param name="pageSize" value="<%=pagesize%>" />
    <jsp:param name="currentpage" value="<%=currentPage%>" />
</jsp:forward>
<%}catch (Exception e){out.print(e.getMessage());}%>



</body>
</html>

findAllUserServlet.java(/findAll):

package servelt;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import beans.User;
import java.io.PrintWriter;
import Dao.UserDao;
import java.util.Map;
import javax.servlet.annotation.WebServlet;
@WebServlet("/findAll")

public class FindAllUserServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();
        UserDao u1=new UserDao();
        String currentPage_=request.getParameter("currentpage");
        int currentPage=Integer.parseInt(currentPage_);
        String Page_= request.getParameter("pageSize");
        int Page=Integer.parseInt(Page_);
        int totalpage=0;
        List<User> userList=new ArrayList<User>();
        try{
            userList=u1.OnePage(currentPage,Page);
            totalpage=u1.PageAll(Page);
        }catch (Exception e){System.out.print(e.getMessage()); }
        if(currentPage<1)
        {
            out.print("<script>alert('已在第一页!');window.location.href='ShowAllUserSumbit.jsp?id=1'</script>");
            return;
        }
        else if(currentPage>totalpage+1)
        {
            out.print("<script>alert('已在最后一页!');window.location.href='ShowAllUserSumbit.jsp?id="+(totalpage+1)+"'</script>");
            return;
        }
        request.setAttribute("pagesize",Page);
        request.setAttribute("currentpage",currentPage);
        request.setAttribute("result",userList);
        request.setAttribute("totalpage",totalpage);
        request.getRequestDispatcher("ShowAll.jsp").forward(request, response);
    }
}

ShowAll.jsp:

<%--
  Created by IntelliJ IDEA.
  User: ssssssbbbbbb
  Date: 2022/11/4
  Time: 9:27 AM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib  uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<html>
<head>
    <title>Title</title>
</head>
<body>
<center>
    <h1>查询所有学生信息</h1>
    第${currentpage}页/共${totalpage+1}页
    <a href="ShowAllUserSumbit.jsp?id=1">首页</a>
    <a href="ShowAllUserSumbit.jsp?id=${currentpage-1}">上一页</a>
        <a href="ShowAllUserSumbit.jsp?id=${currentpage+1}">下一页</a>
    <a href="ShowAllUserSumbit.jsp?id=${totalpage+1}">尾页</a>


    <table border="1" width="300">
        <tr><th>序号</th><th>学号</th><th>姓名</th><th>成绩</th></tr>
        <c:forEach items="${result}" var="user" varStatus="count">
            <tr>
                <td>${count.count+(currentpage-1)*pagesize}</td>
                <td>${user.uid}</td>
                <td>${user.name}</td>
                <td>${user.score}</td>
            </tr>
        </c:forEach>
    </table>
</center>
</body>
</html>

Search.jsp:

<!DOCTYPE html>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>

    <title>Search Suggest</title>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <script src="js/jquery-3.6.1.js" ></script>
    <script type="text/javascript">

        function search(){
            var inputWord =$('#inputWord').val();
            var url="searchsuggest";
            var params = 'inputWord=' + inputWord;
            $.post(url, params, function (xmlDoc, status, xhr) {
                if(xhr.readyState != 4 || status != "success") { //页面不正常
                    alert("您请求的页面有异常");
                    return;
                }
                var tbody=$("#wordsListTbody");
                tbody.empty();          //清除下拉提示框中已有的数据
                console.log(xmlDoc);   //打印调试信息

                var words = $(xmlDoc).find("word");
                words.each(function (index, item) { //index为序号(从0开始), item是js对象
                    var line = $("<tr id='" + (index+1) + "'><td>" + item.innerHTML + "</td></tr>");
                    tbody.append(line);  //添加一行

                    line.click(function () {  //单击时，把提示信息显示在输入框中
                        var aword = $(this).find("td").eq(0).html();
                        $("#inputWord").val(aword);
                        $("#wordsListDiv").css("visibility","hidden");
                    });

                    line.mouseover(function () {  //鼠标移入时时背景为灰色
                        this.style.backgroundColor="grey";
                    });

                    line.mouseleave(function () {  //鼠标移开时背景为白色
                        this.style.backgroundColor="white";
                    });
                });

                if(words.length>0){
                    $("#wordsListDiv")[0].style.visibility='visible'   //通过js对象的属性设置
                }else{
                    $("#wordsListDiv")[0].style.visibility='hidden';
                }


            },"xml");  //发送请求
        }
        //通过jq对象设置下拉提示框的位置
        function setDivPositionjq(){
            var input = $("#inputWord");
            var listdiv = $("#wordsListDiv");
            var offset = input.offset();
            listdiv.offset({left:offset.left, top:offset.top+input.height()+4});
            listdiv.width(input.width());
            listdiv.css("border","blue 2px solid");
        }
    </script>
</head>

<body  onload="setDivPositionjq()">
<center>
    <h1>按姓名查询学生信息</h1>
<form action="search">
    请输入姓名:<input type="text" id="inputWord" name="inputWord" onKeyUp="search()"/>
    <input type="submit" value="搜索">
</form>
<div id="wordsListDiv" style="position:absolute;visibility:hidden">
    <table id="wordsListTable">
        <tbody id="wordsListTbody"><tr><td>test</td></tr></tbody>
    </table>
</div>
</center>
</body>
</html>

SearchSuggest.java(/searchsuggest):

package servelt;

import Dao.UserDao;
import beans.User;

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 java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@WebServlet("/searchsuggest")
public class SearchSuggest extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();
        // response.setContentType(“text/html;charset=GBK”);
       // PrintWriter out = response.getWriter();
        String inputWord = request.getParameter("inputWord");
        if(inputWord==null||inputWord=="") return;
        List<User> userList=new ArrayList<User>();
        UserDao user=new UserDao();
        try{
            userList=user.FindByName(inputWord);
        }catch (Exception e){System.out.print(e.getMessage());}
        String info="<words>";

        for(int i=0;i<userList.size();i++)
        {
            info=info+"<word>"+userList.get(i).getName()+"</word>";
        }
        info=info+"</words>";
        out.print(info);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

FindAll.java(/search):

package servelt;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import beans.User;
import Dao.UserDao;
import java.util.Map;
import javax.servlet.annotation.WebServlet;
@WebServlet("/search")
public class FindAll extends HttpServlet{
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        UserDao u1=new UserDao();
        String name=request.getParameter("inputWord");
        List<User> userList=new ArrayList<User>();
        try{
           userList=u1.FindByName(name);
          //  System.out.print(userList.get(1).getName());
        }catch (Exception e){System.out.print(e.getMessage()); }
        request.setAttribute("result",userList);
        request.getRequestDispatcher("Search_.jsp").forward(request, response);
    }
}

Search_.jsp:

<%--
  Created by IntelliJ IDEA.
  User: ssssssbbbbbb
  Date: 2022/11/4
  Time: 9:27 AM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<%@ page import="Dao.UserDao" %>
<%@page import="beans.User"%>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.List" %>
<%@ page import="java.util.ArrayList" %>

<%@ taglib  uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<html>
<head>
    <title>Title</title>
</head>
<body>

<center>
    <h1>按姓名查询学生信息</h1>
    <table border="1" width="300">
        <tr><th>序号</th><th>学号</th><th>姓名</th><th>成绩</th></tr>
        <c:forEach items="${result}" var="user" varStatus="count" >
            <tr>
                <td>${count.count}</td>
                <td>${user.uid}</td>
                <td>${user.name}</td>
                <td>${user.score}</td>
            </tr>
        </c:forEach>
    </table>
</center>
</body>
</html>

数据库结构：

 项目结构：