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

来源：互联网发布：产品设计学什么软件编辑：程序博客网时间：2024/05/21 22:24

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

这里写图片描述

数据库脚本:

DROP DATABASE csdpsystem;CREATE DATABASE csdpsystemCHARACTER 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%>页。--%>

1 0