Oracle 简单分页

最新推荐文章于 2021-04-10 09:40:17 发布

原创最新推荐文章于 2021-04-10 09:40:17 发布 · 334 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #操作系统 #数据库

SQL 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一种使用Java JDBC连接Oracle数据库实现简单分页的方法。通过示例代码展示了如何创建数据库连接、执行分页查询及处理结果集。该方法首先获取总记录数，然后根据指定的页数和每页显示的记录数构造分页查询语句。

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>