【JAVA技术库】分页模糊查询

最新推荐文章于 2023-11-25 16:49:07 发布

jluzh04140717

最新推荐文章于 2023-11-25 16:49:07 发布

阅读量2.7k

点赞数 3

分类专栏：技术库文章标签： java 数据库技术分页

本文链接：https://blog.csdn.net/jluzh04140717/article/details/70200863

版权

技术库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

先上效果图
这里写图片描述

这里写图片描述

数据库脚本:

DROP DATABASE csdpsystem;

CREATE DATABASE csdpsystem
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';

USE csdpsystem;

CREATE TABLE csdpsystem.student
(
   studentID            VARCHAR(8) NOT NULL,
   studentName          VARCHAR(12),
   studentPassword      VARCHAR(32),
   sStatus              INT ,
   sIdentity            VARCHAR(6)  DEFAULT '学生',
   sLastTime            DATETIME,
   CONSTRAINT pk_studentID  PRIMARY KEY (studentID),
);

COMMIT ;

一共三个页面:使用技术是JSP+JDBC
page.jsp主页面

<%@ page import="java.sql.*" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%
    request.setCharacterEncoding("UTF-8");
%>
<html>
<head>
    <%
        String path = request.getContextPath();
        String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
    %>
    <meta http-equiv="Content-Type" content="text/html; charset='utf-8'" />
    <base href="<%=basePath%>">
    <title>学生查询</title>
</head>
<body>
<%!//设置连接数据库的参数
    private static final String DBDRIVER = "com.mysql.jdbc.Driver";
    private static final String DBURL = "jdbc:mysql://localhost:3306/csdpsystem";
    private static final String DBUSER = "root";
    private static final String DBPASSWORD = "";
%>

<%
    String url = "/jsp/cdsp_information/manager/page.jsp";
    int currentPage = 1;
    String keyWord = "";//默认的关键字
    int lineSize = 5;//每页显示的数据数
    int allRecorders = 0;//保存总记录数
    String column = "studentID";//定义默认的查询列
    String columnData = "用户用户名:studentID|用户姓名:studentName|用户状态:sStatus";//可操作的查询列

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
%>

<%
    try{//如果没有输入参数那么就会是null,null无法变为数字
        currentPage = Integer.parseInt(request.getParameter("cp"));
    }catch(Exception e){}
    try{//如果没有输入参数那么就会是null,null无法变为数字
        lineSize = Integer.parseInt(request.getParameter("ls"));
    }catch(Exception e){}
    if(request.getParameter("kw")!=null){//表示有查询的关键字
        System.out.println("取得关键字:"+new String(request.getParameter("kw").getBytes("iso-8859-1"), "utf-8"));
        keyWord = new String(request.getParameter("kw").getBytes("iso-8859-1"), "utf-8");//将取得的关键字进行转换
    }
    if(request.getParameter("col")!=null){//表示有查询的检索列
        column = request.getParameter("col");
    }
%>

<%//查询总记录数
    String sql = " SELECT COUNT(*) FROM student WHERE "+column+" LIKE ? ";
    Class.forName(DBDRIVER);//加载数据库驱动
    conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);//设置数据库连接
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1,"%"+keyWord+"%");
    rs = pstmt.executeQuery();//将查询的结果返回
    if (rs.next()){
        allRecorders = rs.getInt(1);//返回总记录数
//        System.out.println(allRecorders);
    }
%>

<%//模糊查找数据
    sql = " SELECT studentID,sStatus,sIdentity,studentName,studentPassword,sLastTime " +
            " FROM student " +
            " WHERE "+ column +"  LIKE ? LIMIT ?,? ";
//        select * from tablename limit 2,4
//        即取出第3条至第6条，4条记录
//    0,5   即取出第1条到第5条 , 5条记录
//    5,5   即取出第6条到第10条, 5条记录
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1,"%"+keyWord+"%");// 模糊查询  %关键词%
    pstmt.setInt(2,(currentPage-1)*lineSize);//(当前所在的页 - 1) * 每页显示数据行数 0 5
    pstmt.setInt(3,lineSize);//每页最多显示数据行数 5 5
    rs = pstmt.executeQuery();
%>
<%
    System.out.println("page.jsp的keyWord:"+keyWord);
%>
<%--搜索栏--%>
<div id="spiltSearchDiv">
    <jsp:include page="split_page_search.jsp">
        <jsp:param name="columnData" value="<%=columnData%>"></jsp:param>
        <jsp:param name="keyWord" value="<%=keyWord%>"></jsp:param>
        <jsp:param name="allRecorders" value="<%=allRecorders%>"></jsp:param>
        <jsp:param name="column" value="<%=column%>"></jsp:param>
    </jsp:include>
</div>

<%--数据显示栏--%>
<div id="dataDiv">
    <table border="1" width="100%" bgcolor="#F2F2F2">
        <tr>
            <td>学生学号:</td>
            <td>学生姓名:</td>
            <td>学生状态:</td>
            <td>学生最后一次登录时间:</td>
        </tr>
        <%
            while (rs.next()){
                //studentID,sStatus,sIdentity,studentName,studentPassword,sLastTime
                String studentID = rs.getString(1);
                String studentName = rs.getString(4);
                int sStatus = rs.getInt(2);
                Date sLastTime = rs.getDate(6);
        %>
            <tr>
                <td><%=studentID%></td>
                <td><%=studentName%></td>
                <td>
                    <%
                        if (sStatus == 1){
                    %>
                            在线
                    <%
                        }else{
                    %>
                            不在线
                    <%
                        }
                    %>
                </td>
                <td><%=sLastTime%></td>
            </tr>
        <%
            }
            conn.close();
        %>
    </table>
</div>

<%--分页栏--%>
<div id="splitBarDiv">
    <jsp:include page="split_page_bar.jsp">
        <jsp:param name="currentPage" value="<%=currentPage%>"></jsp:param>
        <jsp:param name="lineSize" value="<%=lineSize%>"></jsp:param>
        <jsp:param name="column" value="<%=column%>"></jsp:param>
        <jsp:param name="keyWord" value="<%=keyWord%>"></jsp:param>
        <jsp:param name="allRecorders" value="<%=allRecorders%>"></jsp:param>
        <jsp:param name="url" value="<%=url%>"></jsp:param>
    </jsp:include>
</div>
</body>
</html>

split_page_bar.jsp分页栏页面

<%@ page pageEncoding="UTF-8"%>
<%
    request.setCharacterEncoding("UTF-8");
%>
<%--
    代码的引入过程
    <div id="splitBarDiv">
    <jsp:include page="split_page_bar.jsp">
        <jsp:param name="currentPage" value="<%=currentPage%>"></jsp:param>
        <jsp:param name="lineSize" value="<%=lineSize%>"></jsp:param>
        <jsp:param name="column" value="<%=column%>"></jsp:param>
        <jsp:param name="keyWord" value="<%=keyWord%>"></jsp:param>
        <jsp:param name="allRecorders" value="<%=allRecorders%>"></jsp:param>
        <jsp:param name="url" value="<%=url%>"></jsp:param>
    </jsp:include>
</div>
--%>

<%//设置由外部接收的数据
    String url = null;
    int currentPage = 1;//当前页
    int lineSize = 5;//每页数据数
    String column = null;//检索列
    String keyWord = null;//关键词
    int allRecorders = 0;//总记录数
    int pageSize = 0 ;//总页数
    int lsData [] = new int [] {1,5,10,15,20,30,50,100} ;//每页显示多少条数据
%>

<%//接收外部传递的参数
    try {
        currentPage = Integer.parseInt(request.getParameter("currentPage"));
    } catch (Exception e) {}
    try {
        allRecorders = Integer.parseInt(request.getParameter("allRecorders"));
        System.out.println("jsp/cdsp_information/manager/split_page_bar.jsp:"+allRecorders);
    } catch (Exception e) {}
    try {
        lineSize = Integer.parseInt(request.getParameter("lineSize"));
    } catch (Exception e) {}
    column = request.getParameter("column") ;
    keyWord = request.getParameter("keyWord") ;
    url = request.getParameter("url") ;
%>

<%//计算总页数
    if (allRecorders > 0) {
        pageSize = (allRecorders + lineSize - 1) / lineSize ;
    } else {    // 没有记录
        pageSize = 1 ;
    }
%>
<%
    System.out.println("split_page_bar.jsp的keyWord;"+keyWord);
%>
<script type="text/javascript">
    function goSplit(vcp) { // 根据外部传递的cp内容进行操作
        var eleLs = document.getElementById("lsSel").value ;
        try {
            var eleKw = document.getElementById("kw").value ;
            var eleCol = document.getElementById("colSel").value ;
            window.location = "<%=url%>?cp=" + vcp + "&ls=" + eleLs + "&kw=" + eleKw + "&col=" + eleCol  ;
        } catch (Exception) {//如果出现异常,说明没有关键字和检索列
            window.location = "<%=url%>?cp=" + vcp + "&ls=" + eleLs  ;
        }
    }
</script>

    <input type="button" class="btn btn-default" value="首页" onclick="goSplit(1)" <%=currentPage == 1 ? "disabled" : ""%>>
    <input type="button" class="btn btn-default" value="上一页" onclick="goSplit(<%=currentPage-1%>)" <%=currentPage == 1 ? "disabled" : ""%>>
    <input type="button" class="btn btn-default" value="下一页" onclick="goSplit(<%=currentPage+1%>)" <%=currentPage == pageSize ? "disabled" : ""%>>
    <input type="button" class="btn btn-default" value="尾页" onclick="goSplit(<%=pageSize%>)" <%=currentPage == pageSize ? "disabled" : ""%>>
    <%--//添加一个下拉列表框--%>
    跳转到:<select id="cpSel" onchange="goSplit(this.value)">
    <%--动态控制的option--%>
    <%
        for (int x = 1;x <= pageSize;x++){
    %>
    <option value="<%=x%>" <%=currentPage == x ? "selected" : ""%>><%=x%></option>
    <%
        }
    %>
</select>页&nbsp;每页显示:
    <select id="lsSel" onchange="goSplit(1)">
        <%
            for (int x = 0 ; x<lsData.length ;x++){
        %>
        <option value="<%=lsData[x]%>" <%=lineSize == lsData[x] ? "selected" : ""%>><%=lsData[x]%></option>
        <%
            }
        %>
    </select>
    行记录

split_page_search.jsp检索栏页面

<%@ page pageEncoding="UTF-8"%>
<%
    request.setCharacterEncoding("UTF-8");
%>
<%
    String columnData = null;//查询的数据列
    String keyWord = null;//查询的关键字
    String column = null;//查询的
    int allRecorders = 0;//查询的总数据量
%>
<%//接收页面的接收列
    try{
        allRecorders = Integer.parseInt(request.getParameter("allRecorders"));
    }catch (Exception e){}
    columnData = request.getParameter("columnData");
    keyWord = request.getParameter("keyWord");
    column = request.getParameter("column");
%>
请输入查询关键字:
<%
    if (columnData!=null){
%>
<select id="colSel">
    <%
        String result[] = columnData.split("\\|");
        for (int x = 0 ; x < result.length ; x ++){
            String temp[] = result[x].split(":");
    %>
        <option value="<%=temp[1]%>" <%=column.equals(temp[1])?"selected":""%>><%=temp[0]%></option>
    <%
        }
    %>
</select>
<%
    }
%>
<input type="text" name="kw" id="kw" value="<%=keyWord%>">
<%
    System.out.println("split_page_search.jsp的keyWord;"+keyWord);
%>

<input type="button" value="检索" onclick="goSplit(1)"><br>
<span>一共查询到<%=allRecorders%>条记录</span><br>
<%--,一共有<%=pageSize%>页。--%>

jluzh04140717

关注

3
点赞
踩
7

收藏

觉得还不错? 一键收藏
3
评论
【JAVA技术库】分页模糊查询

先上效果图数据库脚本:DROP DATABASE csdpsystem;CREATE DATABASE csdpsystemCHARACTER SET 'utf8'COLLATE 'utf8_general_ci';USE csdpsystem;CREATE TABLE csdpsystem.student( studentID VARCHAR(8) NOT N
复制链接

扫一扫