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;
/* 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());
}
}
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;
}
}
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;
}
}
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 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;
}
}
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"%>
<%@ 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>
<%@ 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>
相关推荐
这个也是从网上整理的一片jsp和oracle结合的分页程序。个人觉得还不错,就共享出来了。
带学员课上做的oracle模仿百度分页样式的例子。比较适合初学者参考,jdbc工具类是自己写的,没有使用第三方分页组件。tomcat是apache-tomcat-7.0.41,myeclipse是MyEclipse 9.0,oracle是11g。例子中用的是海量数据...
JSP+JDBC_真分页(基于Oracle数据库分页)笔记JSP+JDBC_真分页(基于Oracle数据库分页)笔记JSP+JDBC_真分页(基于Oracle数据库分页)笔记
jsp 加 oracle 数据库分页,自行创建表结构,表很简单的,比较适合初学者使用,共5个例子程序,其中常用的分页效果都有.
JSP+JDBC_真分页(基于Oracle数据库分页).
这是我前段时间写的一个分页,是用Jsp+Javabean+Servlet对Oracle 和Mysql分页
oracle+jsp实现分页
功能:JSP数据库操作例程 - 数据分页显示 - JDBC 2.0 - Oracle --%> ;charset=8859_1" %> //变量声明 java.sql.Connection sqlCon; //数据库连接对象 java.sql.Statement sqlStmt; //SQL语句对象 ...
分页技术。实验一(1) Developer使用说明(1) 数据库管理(1) PL/SQL(1) Oracle客户端安装与配置(1) Oracle(1) sqlplus和isqlplus基本命令(1) 语言访问数据库(1) 用SQL(1)
jsp分页 oracle 插件demo 下载此demo后 打开myeclipse import此demo 根据实际情况修改jdbc properties配置文件
oracle&jsp简单实现分页,注意到Oracle包
java代码与oracle 函数包实现 jsp分页
我的一个详细的jsp分页程序!(oracle+jsp+apache)
这是一个非常锻炼人的项目,可以供大家下载和参考哦
这是一个JSP+Oracle+tomcat的页面访问数据库的数据增删改查案例,使用了分页技术,C标签等等,功能齐全
这是我自己改的分页代码 呵呵 写了将近一个星期 只有一个控制类和一个jsp页 方便阅读和修改 只要修改test.java里面的数据库连接和表名就可以用了 希望大家工作和学习顺利 里面的class12.jar这个包是因为我的数据库...
北大青鸟的2期同学注意了这是java+jsp+servlet+dao+oracle做得分页 增删改查操作2期考试 只要把这个代码看明白了 考试绝对90分+ 不惧任何考题
质量保证,逻辑代码全部手写,部分使用MyEclipse8.5生成,没有复制其他地方的代码,初级学习分页的好例子,代码中有注释!
本系统主要实现火车查询售票功能,主要的功能如下: 客户端管理功能: (1)动车基本信息管理模块;...4.本设计采用JSP+Servlet+EJB3.0+Oracle10g工具进行开发,最后通过JDBC进行与数据库的相关的链接。
severlet jsp 分页 ajax等技术的笔记