oracle+jsp实现分页
jsp+oracle实现简单分页
/*1*/
/*==============================================================*/?
/* 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;
/*2*/
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());?
??? }?
}?
/*3*/
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;
Java代码?
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;
Java代码?
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;?
??? }?
}?
?jsp页面：<%@ page contentType="text/html" pageEncoding="GBK"%>
Java代码?
<%@ 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">?
????? 共 <%=pageModel.getTotalPages() %> 页 ?
????? 当前第 <%=pageModel.getPageNo() %>页 ?
? <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>?
