oracle脚本：drop table t_student cascade constraints;
/* ============================================================== */
/* Table: t_student */
/* ============================================================== */
create table t_student (
s_id char ( 10 ) not null ,
s_name varchar2( 20 ) not null ,
s_age char ( 2 ) not null ,
s_sex char ( 2 ) not null ,
s_class varchar2( 20 ),
constraint PK_T_STUDENT primary key (s_id)
);
insert into t_student values( ' 0001 ' , ' 张三 ' , ' 20 ' , ' 男 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0002 ' , ' 李四 ' , ' 21 ' , ' 女 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0003 ' , ' 王五 ' , ' 20 ' , ' 男 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0004 ' , ' 赵柳 ' , ' 20 ' , ' 女 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0005 ' , ' 杨梅 ' , ' 21 ' , ' 男 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0006 ' , ' 刘海 ' , ' 23 ' , ' 女 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0007 ' , ' 孙江 ' , ' 20 ' , ' 女 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0008 ' , ' 苏灿 ' , ' 22 ' , ' 男 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0009 ' , ' 王霞 ' , ' 23 ' , ' 女 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0010 ' , ' 王猛 ' , ' 22 ' , ' 男 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0011 ' , ' 张相 ' , ' 22 ' , ' 女 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0012 ' , ' 香橙 ' , ' 20 ' , ' 女 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0013 ' , ' 李心 ' , ' 21 ' , ' 女 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0014 ' , ' 张强 ' , ' 20 ' , ' 男 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0015 ' , ' 赵琳 ' , ' 21 ' , ' 女 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0016 ' , ' 刘达 ' , ' 21 ' , ' 男 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0017 ' , ' 苏惠 ' , ' 20 ' , ' 女 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0018 ' , ' 贾瑞 ' , ' 20 ' , ' 女 ' , ' 08级一班 ' ) ;
insert into t_student values( ' 0019 ' , ' 谷瑞坤 ' , ' 22 ' , ' 男 ' , ' 08级二班 ' ) ;
insert into t_student values( ' 0020 ' , ' 祥还 ' , ' 21 ' , ' 男 ' , ' 08级一班 ' ) ;
commit;
采用单利模式创建DbUtil类获得Connection对象：package com.stmcc.test.util;
import java.sql. * ;
public class DbUtil {
private static final String driver = " oracle.jdbc.driver.OracleDriver " ;
private static final String url = " jdbc:oracle:thin:@10.10.10.2:1521:orcl " ;
private static final String username = " test " ;
private static final String password = " test " ;
public static Connection getConnection(){
Connection conn = null ;
try {
Class.forName(driver) ;
conn = DriverManager.getConnection(url, username, password) ;
} catch (Exception e){
e.printStackTrace() ;
}
return conn ;
}
public static void close(Connection conn) {
if (conn != null ) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement pstmt) {
if (pstmt != null ) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs ) {
if (rs != null ) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
System.out.println(DbUtil.getConnection());
}
}
创建学生实体类：package com.stmcc.test;
public class Student {
private String s_id ;
private String s_name ;
private String s_age ;
private String s_sex ;
private String s_class ;
public String getS_id() {
return s_id;
}
public void setS_id(String s_id) {
this .s_id = s_id;
}
public String getS_name() {
return s_name;
}
public void setS_name(String s_name) {
this .s_name = s_name;
}
public String getS_age() {
return s_age;
}
public void setS_age(String s_age) {
this .s_age = s_age;
}
public String getS_sex() {
return s_sex;
}
public void setS_sex(String s_sex) {
this .s_sex = s_sex;
}
public String getS_class() {
return s_class;
}
public void setS_class(String s_class) {
this .s_class = s_class;
}
}
创建学生管理类：package com.stmcc.test.util;
import com.stmcc.test. * ;
import java.sql. * ;
import java.util. * ;
public class StuManager {
private static StuManager instance = new StuManager() ;
private StuManager(){} ;
public static StuManager getInstance(){
return instance ;
}
// oracle实现分页的查询语句
// select s_id, s_name, s_age, s_sex, s_class
// from
// (
// select rownum rn, s_id, s_name, s_age, s_sex, s_class
// from
// (select s_id, s_name, s_age, s_sex, s_class
// from t_student order by s_id
// )where rownum <= 10
// )where rn > 5 ;
public PageModel findStudentList( int pageNo, int pageSize){
PageModel pageModel = null ;
StringBuffer sql = new StringBuffer() ;
sql.append( " select s_id, s_name, s_age, s_sex, s_class " )
.append( " from " )
.append( " ( " )
.append( " select rownum rn, s_id, s_name, s_age, s_sex, s_class " )
.append( " from " )
.append( " ( " )
.append( " select s_id, s_name, s_age, s_sex, s_class " )
.append( " from t_student order by s_id " )
.append( " ) " )
.append( " where rownum <= ? " )
.append( " ) " )
.append( " where rn > ? " );
Connection conn = null ;
PreparedStatement pstmt = null ;
ResultSet rs = null ;
try {
conn = DbUtil.getConnection() ;
pstmt = conn.prepareStatement(sql.toString()) ;
pstmt.setInt( 1 , pageNo * pageSize) ;
pstmt.setInt( 2 , (pageNo - 1 ) * pageSize) ;
rs = pstmt.executeQuery() ;
List < Student > stuList = new ArrayList < Student > () ;
while (rs.next()){
Student stu = new Student() ;
stu.setS_id(rs.getString( " s_id " )) ;
stu.setS_name(rs.getString( " s_name " )) ;
stu.setS_age(rs.getString( " s_age " )) ;
stu.setS_sex(rs.getString( " s_sex " )) ;
stu.setS_class(rs.getString( " s_class " )) ;
stuList.add(stu) ;
}
pageModel = new PageModel() ;
pageModel.setList(stuList) ;
pageModel.setTotalRecords(getTotalRecords(conn)) ;
pageModel.setPageSize(pageSize);
pageModel.setPageNo(pageNo);
} catch (Exception e){
e.printStackTrace() ;
} finally {
DbUtil.close(rs) ;
DbUtil.close(pstmt) ;
DbUtil.close(conn) ;
}
return pageModel ;
}
/**
* 取得总记录数
* @param conn
* @return
*/
private int getTotalRecords(Connection conn)
throws SQLException {
String sql = " select count(*) from t_student " ;
PreparedStatement pstmt = null ;
ResultSet rs = null ;
int count = 0 ;
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
rs.next();
count = rs.getInt( 1 );
} finally {
DbUtil.close(rs);
DbUtil.close(pstmt);
}
return count;
}
}
创建分页模型类实现业务逻辑：package com.stmcc.test.util;
import com.stmcc.test. * ;
import java.sql. * ;
import java.util. * ;
public class StuManager {
private static StuManager instance = new StuManager() ;
private StuManager(){} ;
public static StuManager getInstance(){
return instance ;
}
// oracle实现分页的查询语句
// select s_id, s_name, s_age, s_sex, s_class
// from
// (
// select rownum rn, s_id, s_name, s_age, s_sex, s_class
// from
// (select s_id, s_name, s_age, s_sex, s_class
// from t_student order by s_id
// )where rownum <= 10
// )where rn > 5 ;
public PageModel findStudentList( int pageNo, int pageSize){
PageModel pageModel = null ;
StringBuffer sql = new StringBuffer() ;
sql.append( " select s_id, s_name, s_age, s_sex, s_class " )
.append( " from " )
.append( " ( " )
.append( " select rownum rn, s_id, s_name, s_age, s_sex, s_class " )
.append( " from " )
.append( " ( " )
.append( " select s_id, s_name, s_age, s_sex, s_class " )
.append( " from t_student order by s_id " )
.append( " ) " )
.append( " where rownum <= ? " )
.append( " ) " )
.append( " where rn > ? " );
Connection conn = null ;
PreparedStatement pstmt = null ;
ResultSet rs = null ;
try {
conn = DbUtil.getConnection() ;
pstmt = conn.prepareStatement(sql.toString()) ;
pstmt.setInt( 1 , pageNo * pageSize) ;
pstmt.setInt( 2 , (pageNo - 1 ) * pageSize) ;
rs = pstmt.executeQuery() ;
List < Student > stuList = new ArrayList < Student > () ;
while (rs.next()){
Student stu = new Student() ;
stu.setS_id(rs.getString( " s_id " )) ;
stu.setS_name(rs.getString( " s_name " )) ;
stu.setS_age(rs.getString( " s_age " )) ;
stu.setS_sex(rs.getString( " s_sex " )) ;
stu.setS_class(rs.getString( " s_class " )) ;
stuList.add(stu) ;
}
pageModel = new PageModel() ;
pageModel.setList(stuList) ;
pageModel.setTotalRecords(getTotalRecords(conn)) ;
pageModel.setPageSize(pageSize);
pageModel.setPageNo(pageNo);
} catch (Exception e){
e.printStackTrace() ;
} finally {
DbUtil.close(rs) ;
DbUtil.close(pstmt) ;
DbUtil.close(conn) ;
}
return pageModel ;
}
/**
* 取得总记录数
* @param conn
* @return
*/
private int getTotalRecords(Connection conn)
throws SQLException {
String sql = " select count(*) from t_student " ;
PreparedStatement pstmt = null ;
ResultSet rs = null ;
int count = 0 ;
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
rs.next();
count = rs.getInt( 1 );
} finally {
DbUtil.close(rs);
DbUtil.close(pstmt);
}
return count;
}
}
jsp页面：<%@ page contentType="text/html" pageEncoding="GBK"%>
<% @ page import = " com.stmcc.test.util.* " %>
<% @ page import = " com.stmcc.test.* " %>
<% @ page import = " java.sql.* " %>
<% @ page import = " java.util.* " %>
< html >
< head >< title > 分页展示 </ title ></ head >
<%
int pageNo = 1 ;
int pageSize = 5 ;
String pageNoString = request.getParameter( " pageNo " ) ;
if (pageNoString != null ){
pageNo = Integer.parseInt(pageNoString) ;
}
PageModel pageModel = StuManager.getInstance().findStudentList(pageNo,pageSize) ;
%>
< script type = " text/javaScript " >
function topPage() {
window.self.location = " student.jsp?pageNo=<%=pageModel.getTopPageNo()%> " ;
}
function previousPage() {
window.self.location = " student.jsp?pageNo=<%=pageModel.getPreviousPageNo()%> " ;
}
function nextPage() {
window.self.location = " student.jsp?pageNo=<%=pageModel.getNextPageNo()%> " ;
}
function bottomPage() {
window.self.location = " student.jsp?pageNo=<%=pageModel.getBottomPageNo()%> " ;
}
</ script >
< body >
< center >
< table border = " 1 " >
< tr >
< td > 学生编号 </ td >
< td > 学生姓名 </ td >
< td > 学生年龄 </ td >
< td > 学生性别 </ td >
< td > 学生班级 </ td >
</ tr >
<%
List stuList = pageModel.getList() ;
for (Iterator < Student > iter = stuList.iterator(); iter.hasNext();){
Student stu = iter.next() ;
%>
< tr >
< td ><%= stu.getS_id() %></ td >
< td ><%= stu.getS_name() %></ td >
< td ><%= stu.getS_age() %></ td >
< td ><%= stu.getS_sex() %></ td >
< td ><%= stu.getS_class() %></ td >
</ tr >
<%
}
%>
< tr >< td colspan = " 5 " >
共 & nbsp; <%= pageModel.getTotalPages() %>& nbsp;页 & nbsp; & nbsp; & nbsp; & nbsp;
当前第 & nbsp; <%= pageModel.getPageNo() %> 页 & nbsp; & nbsp; & nbsp; & nbsp; & nbsp;
< input type = " button " value = " 首页 " onClick = " topPage() " >
< input type = " button " value = " 上一页 " onClick = " previousPage() " >
< input type = " button " value = " 下一页 " onClick = " nextPage() " >
< input type = " button " value = " 尾页 " onClick = " bottomPage() " >
</ td ></ tr >
</ table >
</ center >
</ body >
</ html >
