實現分頁顯示資料[並按關鍵字查詢]

mlinge-奮鬥吧發表於2012-08-01

前面的文章實現了分頁顯示，現在把分頁顯示的進行了整合，整合到了一個單獨的頁面中，加入了按關鍵字查詢，具體實現如下：

用於實現分頁和查詢的頁面檔案：

split_page_plugin.jsp

<%@ page contentType="text/html" pageEncoding="GBK"%>
<%
	int currentPage = 1 ;	// 為當前所在的頁，預設在第1頁
	int lineSize = 3 ;		// 每次顯示的記錄數
	long allRecorders = 0 ;	// 表示全部的記錄數
	long pageSize = 1 ;		// 表示全部的頁數（尾頁）
	int lsData[] = {1,3,5,7,9,10,15,20,25,30,50,100} ;
	String keyWord = request.getParameter("kw") ;	// 接收查詢關鍵字
	String url = request.getParameter("url") ;
%>
<%
	try{
		currentPage = Integer.parseInt(request.getParameter("cp")) ;
	} catch(Exception e) {}
	try{
		lineSize = Integer.parseInt(request.getParameter("ls")) ;
	} catch(Exception e) {}
	try{
		allRecorders = Long.parseLong(request.getParameter("allRecorders")) ;
	} catch(Exception e) {}
	if(keyWord == null){
		keyWord = "" ;	// 如果模糊查詢沒有關鍵字，則表示查詢全部
	}
%>
<%
	pageSize = (allRecorders + lineSize -1) / lineSize ;
	if(pageSize == 0){
		pageSize = 1 ;
	}
%>

<script language="javascript">
	function go(num){
		document.getElementById("cp").value = num ;
		document.spform.submit() ;	// 表單提交
	}
</script>
<form name="spform" action="<%=url%>" method="post">
	輸入查詢關鍵字：<input type="text" name="kw" value="<%=keyWord%>">
	<input type="submit" value="查詢"><br>
	<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>


emp_list_true.jsp進行資料庫操作和顯示的頁面檔案：

emp_list_true.jsp

<%@ page contentType="text/html" pageEncoding="GBK"%>
<%@ page import="java.sql.*" %>
<html>
<head><title>僱員列表顯示</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 ;	// 表示全部的記錄數
	String keyWord = request.getParameter("kw") ;	// 接收查詢關鍵字
%>
<%
	try{
		currentPage = Integer.parseInt(request.getParameter("cp")) ;
	} catch(Exception e) {}
	try{
		lineSize = Integer.parseInt(request.getParameter("ls")) ;
	} catch(Exception e) {}
	if(keyWord == null){
		keyWord = "" ;	// 如果模糊查詢沒有關鍵字，則表示查詢全部
	}
%>
<%
	Class.forName(DBDRIVER) ;
	conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD) ;
	String sql = 	"SELECT COUNT(empno) FROM emp " + 
					" WHERE empno LIKE ? OR ename LIKE ? OR job LIKE ? OR TO_CHAR(hiredate,'yyyy-mm-dd') LIKE ? " + 
					" OR sal LIKE ? OR comm LIKE ? " ;
	pstmt = conn.prepareStatement(sql) ;
	pstmt.setString(1,"%"+keyWord+"%") ;
	pstmt.setString(2,"%"+keyWord+"%") ;
	pstmt.setString(3,"%"+keyWord+"%") ;
	pstmt.setString(4,"%"+keyWord+"%") ;
	pstmt.setString(5,"%"+keyWord+"%") ;
	pstmt.setString(6,"%"+keyWord+"%") ;
	rs = pstmt.executeQuery() ;
	if(rs.next()){	// 取得全部的記錄數
		allRecorders = rs.getInt(1) ;
	}
%>
<center> 
	<h1>僱員列表</h1>
<jsp:include page="split_page_plugin.jsp">
	<jsp:param name="allRecorders" value="<%=allRecorders%>"/>
	<jsp:param name="url" value="<%=URL%>"/>
</jsp:include>
<%
	sql = 	"SELECT * FROM (	" + 
			" SELECT empno,ename,job,hiredate,sal,comm,ROWNUM rn " +
			" FROM emp WHERE (empno LIKE ? OR ename LIKE ? OR job LIKE ? OR TO_CHAR(hiredate,'yyyy-mm-dd') LIKE ? OR sal LIKE ? OR comm LIKE ?) AND " + 
			" ROWNUM<=? ORDER BY empno) temp " + 
			" WHERE temp.rn>? " ;
	pstmt = conn.prepareStatement(sql) ;
	pstmt.setString(1,"%"+keyWord+"%") ;
	pstmt.setString(2,"%"+keyWord+"%") ;
	pstmt.setString(3,"%"+keyWord+"%") ;
	pstmt.setString(4,"%"+keyWord+"%") ;
	pstmt.setString(5,"%"+keyWord+"%") ;
	pstmt.setString(6,"%"+keyWord+"%") ;
	pstmt.setInt(7,currentPage * lineSize) ;
	pstmt.setInt(8,(currentPage-1) * lineSize) ;
	rs = pstmt.executeQuery() ;
%>

<TABLE BORDER="1" cellpadding="5" cellspacing="0" bgcolor="F2F2F2" width="80%">
	<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>
<%
	int count = 0 ;
	while(rs.next()){
		count ++ ;
		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>
<%
	}
%>
<%
	if(count == 0){	// 沒有任何的資料
%>
	<TR onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
		<td align="center" valign="middle" colspan="6"><span class="STYLE6">沒有任何的查詢結果！</span></td>
	</TR>
<%
	}
%>
</table>
<%
	conn.close() ;
%>
</center>
</body>
</html>


相關文章