說(shuō)明：
1.我的數(shù)據(jù)庫(kù)名叫aaa，密碼是123
2.確定數(shù)據(jù)庫(kù)中有數(shù)據(jù) 這是查詢功能+分頁(yè)
創(chuàng)建數(shù)據(jù)庫(kù)sql
CREATE TABLE users (
username varchar2(100),
department varchar2(100),
headship varchar2(100),
)
類放在src下的com包中
db.java
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class db {
public static Connection getConnection() throws ClassNotFoundException, SQLException
{
Connection con = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@你的ip:1521:aaa";
con = DriverManager.getConnection(url, "SYSTEM","123");
return con;
}
}
查詢頁(yè)面MyJsp.jsp
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<%@ page import="java.sql.*"%>
<jsp:directive.page import="com.*;"/>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'a.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<%!
int pageSize = 5;//每頁(yè)顯示的記錄數(shù)
int pageCount = 0;//總頁(yè)數(shù)
%>
<%!
Connection con;
Statement sql;
ResultSet rs;
%>
<%
try
{
con = db.getConnection();
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);//可滾動(dòng)查詢數(shù)據(jù)的結(jié)果集
request.setCharacterEncoding("GB2312");
rs = stmt.executeQuery("select * from users") ;
rs.last(); //讓游標(biāo)到表中的最后一行
int rowCount = rs.getRow(); //獲取記錄總數(shù).
pageCount = (rowCount % pageSize == 0) ? (rowCount / pageSize ) : (rowCount / pageSize +1);
int showPage = 1;//當(dāng)前頁(yè)
//取得用戶所指定的頁(yè)
String goToPage = request.getParameter("showPage");
if (goToPage == null){
goToPage = "1";
}
//轉(zhuǎn)換成整形
try{
showPage = Integer.parseInt(goToPage);
}
catch (NumberFormatException ex){
showPage = 1;
}
//當(dāng)前頁(yè)小于等于第一頁(yè)則按第一頁(yè)算 如果 當(dāng)前頁(yè)大于等于總頁(yè)數(shù)則為最后頁(yè)
if(showPage <=1){
showPage = 1;
}
else if(showPage >= pageCount){
showPage = pageCount;
}
//游標(biāo)的位置 (當(dāng)前頁(yè) - 1) * 頁(yè)面大小 + 1
int posion = (showPage -1 ) * pageSize + 1;
//設(shè)置游標(biāo)的位置
rs.absolute(posion);
%>
<table border="1">
<tr>
<td width="160">用戶名</td>
<td width="160">部門</td>
<td width="160">職位</td>
</tr>
</table>
<%
int i =0;
//循環(huán)顯示表中的數(shù)據(jù) pageSize(每頁(yè)所顯示的記錄)
//rs.isAfterLast() 游標(biāo)是否在最后一行之后說(shuō)明后面已經(jīng)沒(méi)記錄
while(i<pageSize && !rs.isAfterLast()){
%>
<table border="1">
<col width="160px"/><col width="160px"/><col width="160px"/><col width="160px"/>
<tr>
<td><%=rs.getString("username")%></td>
<td><%=rs.getString("department")%></td>
<td><%=rs.getString("headship")%></td>
</tr>
<%rs.next();i++;}%>
</table>
<form action="" method="get">
<table border="1">
<tr>
<td>當(dāng)前第<%=showPage%>頁(yè)</td>
<td>共<%=pageCount%>頁(yè)</td>
<td>
<a href="MyJsp.jsp?showPage=1">首頁(yè)</a>
<a href="MyJsp.jsp?showPage=<%=showPage-1%>">上一頁(yè)</a>
<a href="MyJsp.jsp?showPage=<%=showPage+1%>">下一頁(yè)</a>
<a href="MyJsp.jsp?showPage=<%=pageCount%>">尾頁(yè)</a>
</td>
<td> 共<%=rowCount%>條記錄 </td>
<td>轉(zhuǎn)到
<input type="text" name="showPage" size="4"/>
<input type="submit" name="go" value="提交"/>
</td>
</tr>
</table>
</form>
<%
con.close() ;
}
catch(Exception e)
{
out.println(e) ;
}
%>
</body>
</html>