|
Ⅰ建一个jsp文件使其能够访问oracle数据库，查询emp雇员表，并实现分页查询功能。
Ⅱ开发分析：jsp连oracle查询数据的功能已经在昨天实现，今天重点
要实现的是分页查询功能，即把查询数据分页列出来，通过上一页，下一页来查看查询结果，所以这里需要用到jsp的表单来实现该操作。而分页查询又具体分为真分页查询和假分页查询，这里讨论的是真分页查询，若以需要用到rownum来实现，查询语句为
sql = "SELECT * FROM
( " +
" SELECT
empno,ename,job,hiredate,sal,comm,ROWNUM rn " +
" FROM emp
WHERE ROWNUM<=? ORDER BY empno) temp " +
" WHERE
temp.rn>? " ;
Ⅲ具体实现代码脚本如下：
<%@ page contentType="text/html"
pageEncoding="GBK"%>
<%@ page import="java.sql.*" %>
<html>
<head><title>锋叔子的bolog</title>
<style type="text/css">
<!--
.STYLE6 {font-size: 12px}
.STYLE10 {font-size: 14px; font-weight: bold; }
-->
</style>
</head>
<script
language="javascript">
function
changeColor(obj,color){
obj.bgColor =
color ;
}
</script>
<body>
<%!
public static final String URL =
"emp_list_true.jsp" ;
%>
<%!
public static final String DBDRIVER =
"oracle.jdbc.driver.OracleDriver" ;
public static final String DBURL =
"jdbc:oracle:thin:@localhost:1521:oracle" ;
public static final String DBUSER = "scott"
;
public static final String DBPASSWORD = "tiger"
;
%>
<%
Connection conn = null ;
PreparedStatement pstmt = null ;
ResultSet rs = null ;
%>
<%
int currentPage = 1 ; //
为当前所在的页，默认在第1页
int lineSize = 3
; // 每次显示的记录数
int allRecorders = 0 ; //
表示全部的记录数
int pageSize = 1
; // 表示全部的页数（尾页）
int lsData[] = {1,3,5,7,9,10,15,20,25,30,50,100}
;
%>
<%
try{
currentPage =
Integer.parseInt(request.getParameter("cp")) ;
} catch(Exception e) {}
try{
lineSize =
Integer.parseInt(request.getParameter("ls")) ;
} catch(Exception e) {}
%>
<%
Class.forName(DBDRIVER) ;
conn =
DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD) ;
String sql = "SELECT COUNT(empno) FROM emp"
;
pstmt = conn.prepareStatement(sql) ;
rs = pstmt.executeQuery() ;
if(rs.next()){ // 取得全部的记录数
allRecorders = rs.getInt(1)
;
}
%>
<center>
<h1>员工列表</h1>
<script language="javascript">
function go(num){
document.getElementByIdx_x("cp").value
= num ;
document.spform.submit()
; // 表单提交
}
</script>
<%
pageSize = (allRecorders + lineSize -1) /
lineSize ;
%>
<%
sql = "SELECT * FROM
( " +
" SELECT
empno,ename,job,hiredate,sal,comm,ROWNUM rn " +
" FROM emp
WHERE ROWNUM<=? ORDER BY empno) temp " +
" WHERE
temp.rn>? " ;
pstmt = conn.prepareStatement(sql) ;
pstmt.setInt(1,currentPage * lineSize) ;
pstmt.setInt(2,(currentPage-1) * lineSize)
;
rs = pstmt.executeQuery() ;
%>
<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" name="cp"
value="1">
</form>
<TABLE BORDER="1" cellpadding="5" cellspacing="0"
bgcolor="F2F2F2" width="100%">
<TR
onMouseOver="changeColor(this,'white')"
onMouseOut="changeColor(this,'F2F2F2')">
<td
align="center" valign="middle"><span
class="STYLE10">编号</span></td>
<td
align="center" valign="middle"><span
class="STYLE10">姓名</span></td>
<td
align="center" valign="middle"><span
class="STYLE10">职位</span></td>
<td
align="center" valign="middle"><span
class="STYLE10">雇佣日期</span></td>
<td
align="center" valign="middle"><span
class="STYLE10">工资</span></td>
<td
align="center" valign="middle"><span
class="STYLE10">奖金</span></td>
</TR>
<%
while(rs.next()){
int empno = rs.getInt(1)
;
String ename = rs.getString(2)
;
String job = rs.getString(3)
;
Date hiredate = rs.getDate(4)
;
double sal = rs.getDouble(5)
;
double comm = rs.getDouble(6)
;
%>
<TR
onMouseOver="changeColor(this,'white')"
onMouseOut="changeColor(this,'F2F2F2')">
<td
align="center" valign="middle"><span
class="STYLE6"><%=empno%></span></td>
<td
align="center" valign="middle"><span
class="STYLE6"><%=ename%></span></td>
<td
align="center" valign="middle"><span
class="STYLE6"><%=job%></span></td>
<td
align="center" valign="middle"><span
class="STYLE6"><%=hiredate%></span></td>
<td
align="center" valign="middle"><span
class="STYLE6"><%=sal%></span></td>
<td
align="center" valign="middle"><span
class="STYLE6"><%=comm%></span></td>
</TR>
<%
}
%>
</table>
<%
conn.close() ;
%>
</center>
</body>
</html>
Ⅳ程序如上，启动tomcat在主页上打开jsp文件运行效果如图所示：
Ⅴ程序员心情：jsp实现的分页查询，其实有些还不是很懂，但我想先把这一模式整明白也是好的，不懂的其实并不是分页查询的部分，而是对jsp不熟悉，确切的说是我还是一个java
web的菜鸟还有很长的一段路要走，好在还有时间，应该说我已经走在了好多人的前面这是我应该庆幸的地方，努力吧，锋叔。。。。
|
