Oracle 简单分页-CSDN博客

本文链接：https://blog.csdn.net/iteye_6066/article/details/82436452

Oracle 简单分页

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

以scott方案下emp表为例：

JdbcOdbcConnection.java

 
package cn.nevo.service;  
 
  
 
import java.sql.Connection;  
 
import java.sql.DriverManager;  
 
import java.sql.SQLException;  
 
  
 
     07/** 
 
 * @author Administrator  
 
 */ 
 
public class JdbcOdbcConnection {  
 
      
 
    public JdbcOdbcConnection(String driver) {  
 
        try {  
 
            //加载驱动程序  
 
            Class.forName(driver);  
 
        } catch (ClassNotFoundException e) {  
 
            e.printStackTrace();  
 
        }  
 
    }  
 
      
 
    public Connection getConnection(String url, String user, String pwd) {  
 
        Connection conn = null;  
 
        try {  
 
            conn = DriverManager.getConnection(url, user, pwd);  
 
        } catch (SQLException e) {  
 
            e.printStackTrace();  
 
        }  
 
        return conn;  
 
    }  
 
     30}

OraclePaging.java

 
package cn.nevo.service;  
 
  
 
import java.sql.Connection;  
 
import java.sql.ResultSet;  
 
import java.sql.SQLException;  
 
import java.sql.Statement;  
 
  
 
public class OraclePaging {  
 
      
 
    //JDBC方式实现oracle连接  
 
    final String driver = "oracle.jdbc.driver.OracleDriver";  
 
    final String url = "jdbc:oracle:thin:@localhost:1521:orcl";  
 
    final String user = "scott";  
 
    final String pwd = "tiger";  
 
      
 
    Connection conn = new JdbcOdbcConnection(driver).getConnection(url, user, pwd);  
 
    Statement st = null;  
 
    ResultSet rs = null;  
 
      
 
    //获得结果集并返回  
 
    public ResultSet oraclePage(String strsql) {  
 
        try {  
 
            st = conn.createStatement();  
 
            rs = st.executeQuery(strsql);  
 
              
 
        } catch (SQLException e) {  
 
            e.printStackTrace();  
 
        }   
 
        return rs;  
 
    }  
 
      
 
    //关闭数据库资源  
 
    public void closeAll() {  
 
        try {  
 
            rs.close();  
 
            st.close();  
 
            conn.close();  
 
        } catch (SQLException e) {  
 
            e.printStackTrace();  
 
        }  
 
    }  
 
     42}

index.jsp

 
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>  
 
<%@page import="java.sql.ResultSet"%>  
 
     03<% 
 
     04String path = request.getContextPath(); 
 
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";  
 
     06%> 
 
  
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  
 
     09<html> 
 
  <head>  
 
    <base href="<%=basePath%>">  
 
      
 
    <title>查询scott方案下emp表实现分页</title>  
 
    <meta http-equiv="pragma" content="no-cache">  
 
    <meta http-equiv="cache-control" content="no-cache">  
 
    <meta http-equiv="expires" content="0">      
 
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">  
 
    <meta http-equiv="description" content="This is my page">  
 
    <!--  
 
    <link rel="stylesheet" type="text/css" href="styles.css">  
 
    -->  
 
  </head>  
 
    
 
  <body>  
 
    <jsp:useBean id="oraclePaging" class="cn.nevo.service.OraclePaging"/>  
 
    <h2 align="center">实现oracle分页</h2>  
 
    <table border="1" cellpadding="0" cellspacing="0" align="center" width="500">  
 
        <tr>  
 
            <th>姓名</th><th>入职日期</th>  
 
        </tr>  
 
        <%  
 
          
 
            //接收点击连接时候的pageNow  
 
            String s_pageNow = (String)request.getParameter("pageNow");  
 
            int pageNow = 1;  
 
            if(!"".equals(s_pageNow)&&s_pageNow != null){  
 
                pageNow = Integer.parseInt(s_pageNow);  
 
            }  
 
              
 
            ResultSet rs = null;   
 
              
 
            //总记录数查询语句  
 
            String strsql2 = "select count(*) from emp";  
 
              
 
            int rowcount = 5;  //每页显示的记录数  
 
            int recordCount = 0;  //总共的记录数  
 
            int pagecount = 0; //总共显示的页数  
 
            rs = (oraclePaging.oraclePage(strsql2));  
 
            if(rs.next()) {  
 
                recordCount = rs.getInt(1);  
 
                if(recordCount % rowcount == 0) {  
 
                    pagecount = recordCount / rowcount;  
 
                }else {  
 
                    pagecount = recordCount / rowcount + 1;  
 
                }  
 
            }  
 
              
 
            //分页查询sql语句  
 
            String strsql = "select * from (select e.*, rownum rn from (select * from emp) e where rownum <= " + rowcount * pageNow + ") where rn >=" + ((pageNow -1) * (rowcount) + 1);  
 
              
 
            rs = oraclePaging.oraclePage(strsql);  
 
            while(rs.next()) {  
 
                out.println("<tr>");  
 
                out.println("<td align = 'center'>" + rs.getString(2) + "</td>");  
 
                out.println("<td align = 'center'>" + rs.getDate(5) + "</td>");  
 
                out.println("</tr>");  
 
            }  
 
              
 
        %>  
 
            <tr><td colspan="2">  
 
        <%     
 
            //页数的链接  
 
            for(int i=1; i<=pagecount; i++) {  
 
                out.print("<a href = index.jsp?pageNow=" + i + ">[" + i + "]</a>");  
 
            }  
 
              
 
            oraclePaging.closeAll();  
 
        %>  
 
            </td></tr>  
 
    </table>  
 
  </body>  
 
     82</html>