Oracle 简单分页

最新推荐文章于 2021-04-07 00:04:26 发布

weixin_34245749

最新推荐文章于 2021-04-07 00:04:26 发布

阅读量58

点赞数

文章标签： java python 数据库

原文链接：https://my.oschina.net/xiaomaoandhong/blog/74585

版权

2019独角兽企业重金招聘Python工程师标准>>>

以scott方案下emp表为例：

JdbcOdbcConnection.java

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();
		}
	}
}

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="java.sql.ResultSet"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<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>
</html>

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