通过JDBC进行分页查询

r囧r小猫 2022-02-15 11:09 456阅读 0赞

### 通过JDBC进行 分页查询 ###

**问题：**  
设计一个方法，进行分页查询

> public static void list(int start, int count)

start 表示开始页数，count表示一页显示的总数  
list(0,5) 表示第一页，一共显示5条数据  
list(10,5) 表示第三页，一共显示5条数据

进行分页查询用到的SQL语句参考 ：

例如：显示前5条数据

> select \* from hero limit 0,5

**代码参考如下：**

package jdbc;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    public class FenYeChaXun { 
    
    	
    	public static void list(int start, int count) { 
    		
    		//start 表示开始页数，count表示一页显示的总数
    		//list(0,5) 表示第一页，一共显示5条数据
    		//list(10,5) 表示第三页，一共显示5条数据
    		//0-4 5-9 10-15
    		try { 
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e) { 
    			e.printStackTrace();
    		}
    		
    		try (
    				Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/java?characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
    				Statement s = c.createStatement();
    				
    				){ 
    			
    			System.out.println("您查询的是第" + start + "页，共" + count + "条数据为：");
    			
    			String sql = "select * from hero limit " + ((start-1) * count) + "," + count;
    			ResultSet rs = s.executeQuery(sql);
    			while (rs.next()) { 
    				
    				String id = rs.getString(1);
    				String name = rs.getString(2);
    				int hp = rs.getInt(3);
    				double damage = rs.getDouble(4);
    				System.out.println("id为：" + id + ",姓名为：" + name + ",生命值为：" + hp + ",攻击力为：" + damage);
    			}
    			
    		} catch(SQLException e) { 
    			e.printStackTrace();
    		}
    		
    	}
    	
    	public static void main(String[] args) { 
    		
    		Scanner sc = new Scanner(System.in);
    		System.out.println("请输出查询的页数：");
    		int start = sc.nextInt();
    		System.out.println("请输入数据个数：");
    		int count = sc.nextInt();
    		list(start,count);
    		
    		
    	}
    }

**修改版：**

使用execute方式，而不是executeQuery方式完成练习

package jdbc;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    public class FenYeChaXun { 
    
    	
    	public static void list(int start, int count) { 
    		
    		//start 表示开始页数，count表示一页显示的总数
    		//list(0,5) 表示第一页，一共显示5条数据
    		//list(10,5) 表示第三页，一共显示5条数据
    		//0-4 5-9 10-15
    		try { 
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e) { 
    			e.printStackTrace();
    		}
    		
    		
    		try (
    				Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
    				Statement s = c.createStatement();
    				
    				){ 
    			
    			
    			System.out.println("您查询的是第" + start + "页，共" + count + "条数据为：");
    			String sql = "select * from hero limit " + ((start-1) * count) + "," + count;
    			
    			s.execute(sql);
    			ResultSet rs = s.getResultSet();
    			
    			while (rs.next()) { 
    				
    				String id = rs.getString(1);
    				String name = rs.getString(2);
    				int hp = rs.getInt(3);
    				double damage = rs.getDouble(4);
    				
    				System.out.println("id为：" + id + ",姓名为：" + name + ",生命值为：" + hp + ",攻击力为：" + damage);
    			}
    			
    		} catch(SQLException e) { 
    			e.printStackTrace();
    		}
    		
    	}
    	
    	public static void main(String[] args) { 
    		
    		Scanner sc = new Scanner(System.in);
    		System.out.println("请输出查询的页数：");
    		int start = sc.nextInt();
    		System.out.println("请输入数据个数：");
    		int count = sc.nextInt();
    		list(start,count);
    		
    		
    	}
    }