jsp实现oracle分页查询,jsp跟oracle结合分页demo

/*==============================================================*/

/* 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;

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());

}

}

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;

}

}

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 stuList = new ArrayList() ;

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;

}

}

import java.util.* ;

public class PageModel {

//结果集

private List list ;

//查询总记录数

private int totalRecords ;

//每页多少条数据

private int pageSize ;

//第几页

private int pageNo ;

/**

* 总页数

* @return

*/

public int getTotalPages(){

return (totalRecords + pageSize -1) / pageSize ;

}

/**

* 取得首页

* @return

*/

public int getTopPageNo(){

return 1 ;

}

/**

* 上一页

* @return

*/

public int getPreviousPageNo(){

if(pageNo <= 1){

return 1 ;

}

return pageNo - 1 ;

}

/**

* 下一页

* @return

*/

public int getNextPageNo(){

if(pageNo >= getBottomPageNo()){

return getBottomPageNo() ;

}

return pageNo + 1 ;

}

/**

* 取得尾页

* @return

*/

public int getBottomPageNo(){

return getTotalPages() ;

}

public List getList() {

return list;

}

public void setList(List list) {

this.list = list;

}

public int getTotalRecords() {

return totalRecords;

}

public void setTotalRecords(int totalRecords) {

this.totalRecords = totalRecords;

}

public int getPageSize() {

return pageSize;

}

public void setPageSize(int pageSize) {

this.pageSize = pageSize;

}

public int getPageNo() {

return pageNo;

}

public void setPageNo(int pageNo) {

this.pageNo = pageNo;

}

}

分页展示

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) ;

%>

function topPage() {

window.self.location = "student.jsp?pageNo=";

}

function previousPage() {

window.self.location = "student.jsp?pageNo=";

}

function nextPage() {

window.self.location = "student.jsp?pageNo=";

}

function bottomPage() {

window.self.location = "student.jsp?pageNo=";

}

学生编号

学生姓名

学生年龄

学生性别

学生班级

List stuList = pageModel.getList() ;

for(Iterator iter = stuList.iterator(); iter.hasNext();){

Student stu = iter.next() ;

%>

}

%>

共  页    

当前第 页