oracle、mysql分页实例

最新推荐文章于 2023-10-30 16:17:38 发布

小矶鹬

最新推荐文章于 2023-10-30 16:17:38 发布

阅读量175

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签：数据库分页

本文链接：https://blog.youkuaiyun.com/qq_22472619/article/details/91047086

数据库专栏收录该内容

16 篇文章

订阅专栏

本文详细介绍Oracle与MySQL数据库中实现分页查询的方法。通过具体代码示例，展示了如何使用ROWNUM与LIMIT关键字进行高效分页操作，适用于不同场景的数据查询需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//----------------------------------------------------------------------------------------------------------------------------------oracle分页

jdbc.properties内容如下：

#ORACLE
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
userName=ZHAOYANG
password=ZHAOYANG

数据库连接工具类：

package study;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.tomcat.dbcp.dbcp2.ConnectionFactory;
//oracle连接工具类
public class ConnectFactory {
   private static Connection connection=null;
   
   static {
	   try {
		  Properties p=new Properties();
		  p.load(ConnectionFactory.class.getClassLoader().getResourceAsStream("jdbc.properties"));
		  String driver=p.getProperty("driver");
		  String url=p.getProperty("url");
		  String userName=p.getProperty("userName");
		  String password=p.getProperty("password");
		  System.out.println(driver);
		  
		  Class.forName(driver);
		  connection=DriverManager.getConnection(url,userName,password);
	   } catch (IOException e) {
			e.printStackTrace();
	   } catch (ClassNotFoundException e) {
		    e.printStackTrace();
	   } catch (SQLException e) {
			e.printStackTrace();
	   }
   }
   
   public static Connection getConnection() {
	   return connection;
   }
   
   public static void main(String[] args) {
	  System.out.println(connection);
   }
}

实现分页代码：

package dbpaging;
//oracle实现分页

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import study.ConnectFactory;

//将总记录数与每页记录数取余数，如果余数为0，则总页数等于总记录数除以每页记录数的商；
//如果余数不为0，则总页数等于总记录数除以每页记录数的商的基础上加1

//如果要查看的页码大于总页数，则要查看的页码等于总页数；
//如果要查看的页码小于1，则要查看的页码等于1
public class DeptDAO {
	/**
	 * @param page
	 *  要查看第几页
	 * @param pageSize
	 *  每页记录数
	 */
    public void findByPageOracle(int page,int pageSize) {
    	Connection con=null;
    	PreparedStatement stmt=null;
    	ResultSet rs=null;
    	int total=-1;//总记录数
    	int pages=-1;//总页数
    	
    	//用于查询总记录数
    	StringBuffer sql_total=new StringBuffer();
    	sql_total.append("SELECT COUNT(*) FROM DEPT");
    	
    	//用于分页sql语句
    	StringBuffer sql=new StringBuffer();
    	sql.append("SELECT * FROM (");
    	sql.append(" SELECT ROWNUM AS RN,DEPTNO,DNAME,LOC FROM DEPT");
    	sql.append(") WHERE RN BETWEEN ? AND ?");
    	
    	
    	try {
    		con=ConnectFactory.getConnection();
			stmt=con.prepareStatement(sql_total.toString());
			
			//获得总记录数
			rs=stmt.executeQuery();
			if(rs.next()) {
				total=rs.getInt(1);
			}
			
			//1、计算总页数:
			int mod=total%pageSize;
			if(mod==0) {
				pages=total/pageSize;
			}else {
				pages=total/pageSize+1;
			}
			
			//2、判断边界值：如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页 
			if(page>pages) {
				page=pages;
			}else if(page<1) {
				page=1;
			}
			
			//3、取记录的起始跟结束位置
			int begin = (page - 1) * pageSize + 1;
			int end = begin + pageSize - 1;
			
			stmt=con.prepareStatement(sql.toString());
			stmt.setInt(1,begin);
			stmt.setInt(2,end);
			rs=stmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getInt("DEPTNO")+","+rs.getString("DNAME")+","+rs.getString("LOC"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null) {
				   rs.close();
				}
				if(stmt!=null) {
					stmt.close();
				}
				if(con!=null) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
    }
    
    public static void main(String[] args) {
		DeptDAO dao=new DeptDAO();
		dao.findByPageOracle(1,10);//查询第一页，每页10条记录
	}
}

//----------------------------------------------------------------------------------------------------------------------------------mysql分页

mysql.properties内容如下：

#mySql
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbdetail
userName=root
password=123456

数据库连接工具类：

package study;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.tomcat.dbcp.dbcp2.ConnectionFactory;
//mysql连接工具类
public class ConnectFactory2 {
	   private static Connection connection=null;
	   
	   static {
		   try {
			  Properties p=new Properties();
			  p.load(ConnectionFactory.class.getClassLoader().getResourceAsStream("mysql.properties"));
			  String driver=p.getProperty("driver");
			  String url=p.getProperty("url");
			  String userName=p.getProperty("userName");
			  String password=p.getProperty("password");
			  System.out.println(driver);
			  
			  Class.forName(driver);
			  connection=DriverManager.getConnection(url,userName,password);
		   } catch (IOException e) {
				e.printStackTrace();
		   } catch (ClassNotFoundException e) {
			    e.printStackTrace();
		   } catch (SQLException e) {
				e.printStackTrace();
		   }
	   }
	   
	   public static Connection getConnection() {
		   return connection;
	   }
	   
	   public static void main(String[] args) {
		  System.out.println(connection);
	   }
}

mysql实现分页：

package dbpaging;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import study.ConnectFactory2;

//mysql实现分页

public class DeptDAO2 {
    public void findByPageMySQL(int page,int pageSize) {
    	Connection con=null;
    	PreparedStatement stmt=null;
    	ResultSet rs=null;
    	int total=-1;//总记录数
    	int pages=-1;//总页数
    	
    	StringBuffer sql_total=new StringBuffer();
    	sql_total.append("SELECT COUNT(*) FROM T_SYS_REGIONALISM");
    	
    	StringBuffer sql=new StringBuffer();
    	sql.append("SELECT * FROM T_SYS_REGIONALISM LIMIT ?,?");
    	
    	try {
    		con=ConnectFactory2.getConnection();
			stmt=con.prepareStatement(sql_total.toString());
			
			//获取总记录数
			rs=stmt.executeQuery();
			if(rs.next()) {
				total=rs.getInt(1);
			}
			
			//1、计算总页数
			int mod=total%pageSize;
			if(mod==0) {
				pages=total/pageSize;
			}else {
				pages=total/pageSize+1;
			}
			
			//2、判断边界值：如果要查看的页数大于最大页，或者小于1，则取最后一页或第一页 
			if(page>pages) {
				page=pages;
			}else if(page<1) {
				page=1;
			}
			
			//3、执行分页sql
			int start=(page-1)*pageSize;
			stmt=con.prepareStatement(sql.toString());
			stmt.setInt(1,start);
			stmt.setInt(2,pageSize);
			rs=stmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString("F_CODE")+","+rs.getString("F_NAME"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    
    public static void main(String[] args) {
		DeptDAO2 dao2=new DeptDAO2();
		dao2.findByPageMySQL(0, 10);//第几页，从0开始，每个多少条
	}
    
}

总结：

#ORACLE
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
userName=ZHAOYANG
password=ZHAOYANG

#mySql
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbdetail
userName=root
password=123456

oracle靠rownum分页：

SELECT * FROM (
SELECT ROWNUM AS RN,DEPTNO,DNAME,LOC FROM DEPT
) WHERE RN BETWEEN ? AND ?

参数1：int begin = (page - 1) * pageSize + 1;//page从1开始
参数2：int end = begin + pageSize - 1;

mysql 靠limit分页：

SELECT * FROM T_SYS_REGIONALISM LIMIT ?,?

参数1：int start=(page-1)*pageSize;//page从0开始

参数2：pageSize