通过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);
}
}