jsp分页（使用sql语句实现，并非easyui的json）-优快云博客

本文介绍了一种使用JSP进行数据库分页查询的方法，并详细解释了如何避免因连续使用同一个Statement和ResultSet对象而导致的问题。此外，还展示了如何通过设置每页显示的数据数量来实现分页效果。

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

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@page import="java.sql.*"%>

/*author Evangelion
心得：由于statement和resultset只用了一个连续连了多次数据库导致每次在执行的时候都关闭了数据库。所以每次连接和每次结果都要定义resultset和statement。

不然会出现指针为空的错误，检查这个错误的时候，由于是在jsp的连接数据中debug并不好用，所有打印在控制台上来看是好的选择。

*/
<!DOCTYPE html>

<html>
<head>
  <title></title>
  <link rel="stylesheet" type="text/css" href="http://www.jeasyui.com/easyui/themes/default/easyui.css" />
   <link rel="stylesheet" type="text/css" href="http://www.jeasyui.com/easyui/themes/icon.css" />
     <link rel="stylesheet" type="text/css" href="http://www.jeasyui.com/easyui/demo/demo.css" />
    <script type="text/javascript" src="http://code.jquery.com/jquery-1.6.min.js"></script>
      <script type="text/javascript" src="http://www.jeasyui.com/easyui/jquery.easyui.min.js"></script>
  <script type="text/javascript"
   src="scripts/js/WdatePicker.js"></script>

      <script type="text/javascript" >

        $(document).ready(function() {
            $("#pager").pager({ pagenumber: 1, pagecount: 15, buttonClickCallback: PageClick });
        });

        PageClick = function(pageclickednumber) {
            $("#pager").pager({ pagenumber: pageclickednumber, pagecount: 15, buttonClickCallback: PageClick });
            $("#result").html("Clicked Page " + pageclickednumber);
        }

    </script>
  <script type="text/javascript">
     $(function(){
     $('tbody>tr').click(function() {
  $(this)
   .addClass('selected')
    .siblings().removeClass('selected');

  });

})
</script>

</head>
<body>

   <div>




       e套餐名称：
       <input type="text" name="plan_name" />

       BOSS编码：
       <input type="text" name="boss_code" />

       套餐类型：
       <select name="plan_type"></select>



       品牌:
       <select name="brand_id"></select>

       开始时间：
       <input name="plan_startdate" class="Wdate" type="text"
        onfocus="WdatePicker()" />

       结束时间：
       <input name="plan_enddate" class="Wdate" type="text"
        onfocus="WdatePicker()" />


       <input name="info" type="button" value="查询" />



   </div>

      <table>

<thead>

    <tr>
     <th width="200">
      套餐名称
     </th>
     <th width="200">
      BOSS编码
     </th>
     <th width="200">
      套餐类型
     </th>
     <th width="200">
      品牌
     </th>
     <th width="200">
      开始时间
     </th>
     <th width="200">
      结束时间
     </th>
    </tr>
   </thead>

   <%
       int pageSize=10;
       int pageNow=1;
       int rowCount=0;
       int pageCount=0;
       String s_pageNow=request.getParameter("pageNow");
       if(s_pageNow!=null)
        {
           pageNow=Integer.parseInt(s_pageNow);
       }
    Connection conn = null;
    Statement stmt = null,stmt1=null,stmt2=null;
    //ResultSet rs = null;
    ResultSet rsCount=null;
    ResultSet rsRownum=null;
    try {System.out.println(pageSize);
     Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
     conn = DriverManager.getConnection(
       "jdbc:oracle:thin:@:orcl", "",
       "");

     //stmt = conn.createStatement();
     stmt1=conn.createStatement();
     stmt2=conn.createStatement();
     //rs = stmt.executeQuery("select plan_name,boss_code,plan_type,brand_id,plan_startdate,plan_enddate from pmp_plan t ");
     rsCount=stmt1.executeQuery("select count(*) from pmp_plan t ");
     rsRownum=stmt2.executeQuery("select plan_name,boss_code,plan_type,brand_id,plan_startdate,plan_enddate from(select rownum rown,plan_name,boss_code,plan_type,brand_id,plan_startdate,plan_enddate from pmp_plan t where rownum<="+pageNow*pageSize+")where rown>"+(pageNow-1)*pageSize);
     out.println("<tbody>");
     if(rsCount.next()){ rowCount=rsCount.getInt(1);}
     //System.out.println(rowCount);
     //System.out.println(rsRownum.next());
        while (rsRownum.next()) {
      out.println("<tr>");
      out.println("<td id='plan_name'>"
        + rsRownum.getString("plan_name") + "</td>");
      out.println("<td id='boss_code'>"
        + rsRownum.getString("boss_code") + "</td>");
      out.println("<td id='plan_type'>"
        + rsRownum.getString("plan_type") + "</td>");
      out.println("<td id='brand_id'>" + rsRownum.getString("brand_id")
        + "</td>");
      out.println("<td id='plan_startdate'>"
        + rsRownum.getString("plan_startdate") + "</td>");
      out.println("<td id='plan_enddate'>"
        + rsRownum.getString("plan_enddate") + "</td>");
      out.println("</tr>");

}

    }
    catch (Exception ex) {
     out.println(ex.toString());
    } finally {
     //rs.close();rsCount.close();
     rsRownum.close();
     //stmt.close();
     stmt1.close();stmt2.close();
     conn.close();
    }
    out.println("</tbody>");

    //上一页

   %>
   </table>

   <%
   if(rowCount%pageSize==0)
   {
     pageCount=rowCount/pageSize;
     }
      else
   {
     pageCount=rowCount/pageSize+1;
     }
   if(pageNow!=1)
   {
    out.println("<a href=index.jsp?pageNow="+(pageNow-1)+">上一页</a>");
    }
      for(int i=1;i<=pageCount;i++)
      {
    out.println("<a href=index.jsp?pageNow="+i+">["+i+"]</a>");
    }
   if(pageNow!=pageCount){
                  out.println("<a href=index.jsp?pageNow="+(pageNow+1)+">下一页</a>");
                }
    %>

</body>
</html>

转载于:https://www.cnblogs.com/tom38/p/3429928.html