JDBC 实效数据分页查询

待我称王封你为后i 2022-06-09 11:21 231阅读 0赞

**本案例使用到的Oracle数据库和数据表为Oracle自带**

**示例代码：**

package com.hcq.main;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class Main2 {
    	private static class DBProperties {
    		// IP地址
    		static final String IP = "127.0.0.1";
    		// 端口
    		static final String PORT = "1521";
    		// 数据库名
    		static final String DB_NAME = "test";
    		// 用户名
    		static final String USER = "scott";
    		// 密码
    		static final String PSW = "tiger";
    		// 连接
    		static final String URL = "jdbc:oracle:thin:@" + IP + ":" + PORT + ":" + DB_NAME;
    	}
    
    	// 得到连接
    	public static Connection getConn() {
    		Connection conn = null;
    		try {
    			conn = DriverManager.getConnection(DBProperties.URL, DBProperties.USER, DBProperties.PSW);
    			return conn;
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return null;
    	}
    
    	// 分页方法 pageNUm表示查询单页数，pageSize表示每页显示的条数
    	public static void page(int pageNum, int pageSize) {
    		Connection conn = Main2.getConn();
    		PreparedStatement ps = null;
    		String sql = "SELECT * FROM (SELECT empno,ename,sal,deptno,ROWNUM rn FROM emp WHERE ROWNUM<=?) temp WHERE"
    				+ " temp.rn BETWEEN ? AND ?";
    		try {
    			ps = conn.prepareStatement(sql);
    			ps.setInt(1, pageNum * pageSize);
    			ps.setInt(2, (pageNum - 1) * pageSize + 1);
    			ps.setInt(3, pageNum * pageSize);
    			ResultSet rs = ps.executeQuery();
    			while (rs.next()) {
    				int empno = rs.getInt(1);
    				String ename = rs.getString(2);
    				double sal = rs.getDouble(3);
    				int deptno = rs.getInt(4);
    				int rn = rs.getInt(5);
    				System.out
    						.println("编号：" + empno + "\t姓名：" + ename + "\t薪水:" + sal + "\t部门:" + deptno + "\tROWNUM:" + rn);
    			}
    			rs.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			if (ps != null) {
    				try {
    					ps.close();
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    			}
    			if (conn != null) {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    			}
    		}
    
    	}
    
    	public static void main(String[] args) {
    		// 查询第二页数据，每页显示3条数据
    		page(2, 3);
    	}
    
    }

**结果截图：**

![Center][]

[Center]: /images/20220609/ca9af26ef26c4233850f2cb1c326fe55.png