`
zhouxuebao87
  • 浏览: 115813 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle 简单分页

    博客分类：
  • SQL
 
阅读更多

Oracle 简单分页

http://my.oschina.net/xiaomaoandhong/blog/74585

以scott方案下emp表为例：

QQ截图20120826011132


JdbcOdbcConnection.java

 

view source print?
01 package cn.nevo.service;
02   
03 import java.sql.Connection;
04 import java.sql.DriverManager;
05 import java.sql.SQLException;
06   
07 /**
08  * @author Administrator
09  */
10 public class JdbcOdbcConnection {
11       
12     public JdbcOdbcConnection(String driver) {
13         try {
14             //加载驱动程序
15             Class.forName(driver);
16         } catch (ClassNotFoundException e) {
17             e.printStackTrace();
18         }
19     }
20       
21     public Connection getConnection(String url, String user, String pwd) {
22         Connection conn = null;
23         try {
24             conn = DriverManager.getConnection(url, user, pwd);
25         } catch (SQLException e) {
26             e.printStackTrace();
27         }
28         return conn;
29     }
30 }  
<!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com -->


OraclePaging.java

 

 

view source print?
01 package cn.nevo.service;
02   
03 import java.sql.Connection;
04 import java.sql.ResultSet;
05 import java.sql.SQLException;
06 import java.sql.Statement;
07   
08 public class OraclePaging {
09       
10     //JDBC方式实现oracle连接
11     final String driver = "oracle.jdbc.driver.OracleDriver";
12     final String url = "jdbc:oracle:thin:@localhost:1521:orcl";
13     final String user = "scott";
14     final String pwd = "tiger";
15       
16     Connection conn = new JdbcOdbcConnection(driver).getConnection(url, user, pwd);
17     Statement st = null;
18     ResultSet rs = null;
19       
20     //获得结果集并返回
21     public ResultSet oraclePage(String strsql) {
22         try {
23             st = conn.createStatement();
24             rs = st.executeQuery(strsql);
25               
26         } catch (SQLException e) {
27             e.printStackTrace();
28         } 
29         return rs;
30     }
31       
32     //关闭数据库资源
33     public void closeAll() {
34         try {
35             rs.close();
36             st.close();
37             conn.close();
38         } catch (SQLException e) {
39             e.printStackTrace();
40         }
41     }
42 }
<!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com -->

index.jsp

 

 

view source print?
01 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
02 <%@page import="java.sql.ResultSet"%>
03 <%
04 String path = request.getContextPath();
05 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
06 %>
07   
08 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
09 <html>
10   <head>
11     <base href="<%=basePath%>">
12       
13     <title>查询scott方案下emp表实现分页</title>
14     <meta http-equiv="pragma" content="no-cache">
15     <meta http-equiv="cache-control" content="no-cache">
16     <meta http-equiv="expires" content="0">    
17     <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
18     <meta http-equiv="description" content="This is my page">
19     <!--
20     <link rel="stylesheet" type="text/css" href="styles.css">
21     -->
22   </head>
23     
24   <body>
25     <jsp:useBean id="oraclePaging" class="cn.nevo.service.OraclePaging"/>
26     <h2 align="center">实现oracle分页</h2>
27     <table border="1" cellpadding="0" cellspacing="0" align="center" width="500">
28         <tr>
29             <th>姓名</th><th>入职日期</th>
30         </tr>
31         <%
32           
33             //接收点击连接时候的pageNow
34             String s_pageNow = (String)request.getParameter("pageNow");
35             int pageNow = 1;
36             if(!"".equals(s_pageNow)&&s_pageNow != null){
37                 pageNow = Integer.parseInt(s_pageNow);
38             }
39               
40             ResultSet rs = null; 
41               
42             //总记录数查询语句
43             String strsql2 = "select count(*) from emp";
44               
45             int rowcount = 5;  //每页显示的记录数
46             int recordCount = 0;  //总共的记录数
47             int pagecount = 0; //总共显示的页数
48             rs = (oraclePaging.oraclePage(strsql2));
49             if(rs.next()) {
50                 recordCount = rs.getInt(1);
51                 if(recordCount % rowcount == 0) {
52                     pagecount = recordCount / rowcount;
53                 }else {
54                     pagecount = recordCount / rowcount + 1;
55                 }
56             }
57               
58             //分页查询sql语句
59             String strsql = "select * from (select e.*, rownum rn from (select * from emp) e where rownum <= " + rowcount * pageNow + ") where rn >=" + ((pageNow -1) * (rowcount) + 1);
60               
61             rs = oraclePaging.oraclePage(strsql);
62             while(rs.next()) {
63                 out.println("<tr>");
64                 out.println("<td align = 'center'>" + rs.getString(2) + "</td>");
65                 out.println("<td align = 'center'>" + rs.getDate(5) + "</td>");
66                 out.println("</tr>");
67             }
68               
69         %>
70             <tr><td colspan="2">
71         <%   
72             //页数的链接
73             for(int i=1; i<=pagecount; i++) {
74                 out.print("<a href = index.jsp?pageNow=" + i + ">[" + i + "]</a>");
75             }
76               
77             oraclePaging.closeAll();
78         %>
79             </td></tr>
80     </table>
81   </body>
82 </html>
<!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com -->

 

启动Oracle服务器，通过http://localhost:8080/OraclePagingTest/访问示例：
QQ截图20120826012656

 

 

分享到：
| Oracle分页
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics