jsp+mysql分页实现

最新推荐文章于 2022-08-13 10:41:27 发布

原创最新推荐文章于 2022-08-13 10:41:27 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #jsp #insert #string #sql #button

魔乐先生 JAVA-WEB开发实战专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种基于Java和MySQL的分页查询实现方法，通过设定每页显示的记录数和当前页数来实现数据的分页展示。该方法包括获取总记录数、计算总页数、设置SQL查询语句等功能。

<%@page contentType="text/html;charset=gb2312" %>
<%@ page import="java.sql.*"%>
<html>
<head>
<title>分页显示</title>
</head>
<body>
<center>
<h1>人员列表</h1>
<hr>
<br>
<%!
final String jspURL="list_person_true_spage.jsp";
%>
<%
  int lineSize = 10;//定业每页显示的记录数
  int currentPage = 1; //定义当前是第几页
  int pageSize = 0; //总页数=总记录数/每页显示数
  int allRecorders =0;//总记录数
  String keyword = null;
  keyword = request.getParameter("kw");
  request.setCharacterEncoding("gb2312");
  try
  {
   currentPage = Integer.parseInt(request.getParameter("cp")) ;

  }
  catch(Exception e)
  {}
%>
<%
  final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
  final String DBURL = "jdbc:mysql://localhost/spage" ;
  final String DBUSER = "root" ;
  final String DBPASSWORD = "mysql" ;
  Connection conn = null ;
%>
<%
  try
  {
   Class.forName(DBDRIVER) ;
   conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD) ;
   PreparedStatement pstmt = null ;
   ResultSet rs =null;

   String sql = null ;
   if(keyword==null)
   {
    sql = "SELECT COUNT(id) from persondemo01";
   }
   else
   {
    sql = "select count(id) from persondemo01 where uid like ? or name like ?";
   }
   pstmt = conn.prepareStatement(sql) ;
   if(keyword!=null)
   {
    pstmt.setString(1,"%"+keyword+"%");
    pstmt.setString(2,"%"+keyword+"%");
   }
   rs = pstmt.executeQuery() ;
   if(rs.next())
   {
    allRecorders = rs.getInt(1) ;
    pageSize=(allRecorders)/lineSize+1;
   }
   rs.close() ;
   pstmt.close() ;

   if(keyword==null || keyword=="")
   {
    //假分页替换：sql = "SELECT * from persondemo01";
    sql = "SELECT * from persondemo01 limit "+(currentPage-1)*lineSize+","+lineSize;
   }
   else
   {
    //假分页替换：sql = "select * from persondemo01 where uid like ? or name like ?";
    sql = "select * from persondemo01 where uid like ? or name like ? limit "+(currentPage-1)*lineSize+","+lineSize;
   }
   pstmt = conn.prepareStatement(sql) ;
   if(!(keyword==null||"".equals(keyword)))
   {
    pstmt.setString(1,"%"+keyword+"%");
    pstmt.setString(2,"%"+keyword+"%");

   }
   rs = pstmt.executeQuery() ;
%>
<script language="javaScript">
  function openPage(curpage)
  {
   document.spage.cp.value = curpage ;
   //document.spage.cp.value=curpage;
   // alert(cupage) ;
   document.spage.submit() ;
  }
  function selOPenPage()
  {
   document.spage.cp.value = document.spage.selpage.value;
   document.spage.submit();
  }
</script>
<h2>总记录数为：<%=allRecorders %></h2>
<form name="spage" action="<%=jspURL %>">
输入查询关键字：<input type="text" name="kw" value="<%=keyword==null?"":keyword%>"/>
<input type="submit" value="submit"/>
<br><br>
<input type="button" value="首页" onclick="openPage(1)"/>
<input type="button" value="上一页" onclick="openPage(<%=(currentPage-1)>=1?(currentPage-1):1 %>)"/>
<input type="button" value="下一页" onclick="openPage(<%=(currentPage+1)<pageSize?(currentPage+1):pageSize %>)"/>
<input type="button" value="尾页" onclick="openPage(<%=pageSize %>)"/>
<input type="hidden" name="cp" value="" />
<font color="red"><%=currentPage %></font>/<font color="red"><%=pageSize %></font>
跳转到
<select name="selpage" onChange="selOPenPage()">
<%
  for(int x=1;x<=pageSize;x++)
  {
   %>
   <option value="<%=x %>" <%=currentPage==x?"selected":"" %>><%=x %></option>
   <%
  }
%>

</select>
页
</form>

<table border="1" width="80%">
<tr>
  <td>编号</td>
  <td>登陆名称</td>
  <td>姓名</td>
  <td>密码</td>
  <td colspan="2">操作</td>
</tr>
<%
   int i = 0 ;
   /*假分页增加如下代码
    for(int x=0;x<(currentPage-1)*lineSize;x++)
   {
    rs.next();
   }
   */
   for(int x=0;x<lineSize;x++)
   {
    if(rs.next())
   {
    i++ ;
    int id = rs.getInt(1) ;
    String userid = rs.getString(2) ;
    String name = rs.getString(3) ;
    String password = rs.getString(4) ;
%>
   <tr>
    <td><%=id%></td>
    <td><%=userid%></td>
    <td><%=name%></td>
    <td><%=password%></td>
    <td>更新</td>
    <td>删除</td>
   </tr>
<%
   }

   }
   rs.close() ;
   pstmt.close() ;
   if(i==0)
   {
%>
    <tr>
     <td colspan="6">没有任何数据！！</td>
    </tr>
<%
   }
%>
</table>
<%
  }
  catch(Exception e)
  {
%>
   <h2>系统出错！！！</h2>
<%
  }
  finally
  {
   conn.close() ;
  }
%>
</center>
</body>
</html>

使用的数据库脚本如下：

-- 本分页系列程序数据库使用spage数据库
-- 删除表
drop table persondemo01;
-- 生成persondemo01表
create table persondemo01
(
id int auto_increment not null primary key,
uid varchar(32),
name varchar(32),
password varchar(20)
);
-- 插入数据
insert into persondemo01(uid,name,password) values('wjr01','王**01','530');
insert into persondemo01(uid,name,password) values('wjr02','王**02','530');
insert into persondemo01(uid,name,password) values('wjr03','王**03','530');
insert into persondemo01(uid,name,password) values('wjr04','王**04','530');
insert into persondemo01(uid,name,password) values('wjr05','王**05','530');
insert into persondemo01(uid,name,password) values('wjr06','王**06','530');
insert into persondemo01(uid,name,password) values('wjr07','王**07','530');
insert into persondemo01(uid,name,password) values('wjr08','王**08','530');
insert into persondemo01(uid,name,password) values('wjr09','王**09','530');
insert into persondemo01(uid,name,password) values('wjr10','王**10','530');
insert into persondemo01(uid,name,password) values('wjr11','王**11','530');
insert into persondemo01(uid,name,password) values('wjr12','王**12','530');
insert into persondemo01(uid,name,password) values('wjr13','王**13','530');
insert into persondemo01(uid,name,password) values('wjr14','王**14','530');
insert into persondemo01(uid,name,password) values('wjr15','王**15','530');
insert into persondemo01(uid,name,password) values('wjr16','王**16','530');
insert into persondemo01(uid,name,password) values('wjr17','王**17','530');
insert into persondemo01(uid,name,password) values('wjr18','王**18','530');
insert into persondemo01(uid,name,password) values('wjr19','王**19','530');
insert into persondemo01(uid,name,password) values('wjr20','王**20','530');
insert into persondemo01(uid,name,password) values('wjr21','王**21','530');
insert into persondemo01(uid,name,password) values('wjr22','王**22','530');
insert into persondemo01(uid,name,password) values('wjr23','王**23','530');

commit;