jsp操作数据库，并将结果分页显示

来源：互联网发布：各国语言翻译软件编辑：程序博客网时间：2024/06/05 16:07

最近在学李兴华的javaweb，第五章课程实践内容：分页显示数据库查询结果（这个东西非常有用，虽然还没有完全掌握）

首先声明数据库用的是mysql

1.创建数据库->创建数据表emp 结构如下：

2.向emp中插入数据：

3.编写select_emp.jsp

<%@ page contentType="text/html" pageEncoding="utf-8"%><%@ page import="java.sql.*"%><html><head><title>雇员列表显示</title><script type="text/javascript">function go(num){document.getElementById("_cp").value = num;document.spform.submit();}</script></head><body><%!public static final String URL = "select_emp.jsp";%><%!public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;public static final String DBURL = "jdbc:mysql://localhost:3306/databasename" ;public static final String DBUSER = "root" ;public static final String DBPASS = "" ;%><%Connection conn = null ;PreparedStatement pstmt = null ;ResultSet rs = null ;%><%int currentPage = 3;int lineSize = 3;int allRecorders = 0;int pageSize = 1;int lsData[] = {1,3,5,7,9,10,15,20};%><%try{currentPage = Integer.parseInt(request.getParameter("cp"));lineSize = Integer.parseInt(request.getParameter("ls"));}catch(Exception e){System.out.println(e);}%><%try{Class.forName(DBDRIVER) ;conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;String sql = "SELECT COUNT(empno) FROM emp" ;pstmt = conn.prepareStatement(sql) ;rs = pstmt.executeQuery() ;if(rs.next()){allRecorders = rs.getInt(1); } pageSize = (allRecorders+lineSize-1) / lineSize;%><center><h1>雇员列表</h1></center><center><form name="spform" action="<%=URL%>" method="post">      <input type="button" value="首页" onclick="go(1)" <%=currentPage==1?"DISABLED":""%>>      <input type="button" value="上一页" onclick="go(<%=currentPage-1%>)" <%=currentPage==1?"DISABLED":""%>>      <input type="button" value="下一页" onclick="go(<%=currentPage+1%>)" <%=currentPage==pageSize?"DISABLED":""%>>      <input type="button" value="尾页" onclick="go(<%=pageSize%>)" <%=currentPage==pageSize?"DISABLED":""%>>      跳转到第<select name="selcp" onchange="go(this.value)">          <%              for(int x=1;x<=pageSize;x++){          %>                  <option value="<%=x%>" <%=x==currentPage?"SELECTED":""%>><%=x%></option>          <%              }          %>      </select>页      每页显示          <select name="ls" onchange="go(1)">          <%              for(int x=0;x<lsData.length;x++){          %>              <option value="<%=lsData[x]%>" <%=lsData[x]==lineSize?"SELECTED":""%>><%=lsData[x]%></option>          <%              }          %>          </select>      条      <input type="hidden" id="_cp" name="cp" value="1">  </form></center><center><table border="1" cellpadding="5" cellspacing="0" width="80%" bordercolor="#bbbbbb"><tr><th>雇员编号</th><th>雇员姓名</th><th>雇员工作</th><th>雇员工资</th><th>雇佣日期</th></tr><%sql = "select * from emp where empno<? and empno>=?";pstmt = conn.prepareStatement(sql) ; pstmt.setInt(1,6060+currentPage * lineSize) ;pstmt.setInt(2,6060+(currentPage-1) * lineSize) ;  rs = pstmt.executeQuery() ; %> <%while(rs.next()){          int empno = rs.getInt(1) ;          String ename = rs.getString(2) ;          String job = rs.getString(3) ;          Date date = rs.getDate(4) ;          double sl = rs.getDouble(5) ;  %><tr><td><%=empno%></td><td><%=ename%></td><td><%=job%></td><td><%=sl%></td><td><%=date%></td></tr><%}%></table></center><%}catch(Exception e){System.out.println(e) ;}finally{rs.close() ;pstmt.close() ;conn.close() ;}%></body></html>

4.实现效果：

希望对学习javaweb的伙伴们有所帮助。

0 0