jdbc实现跨库分页查询demo
数据库表结构及示例数据
创建数据库和表
-- 数据库A
CREATE DATABASE a;
USE a;CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL,age INT,create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);-- 数据库B
CREATE DATABASE b;
USE b;CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL,age INT,create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
插入示例数据(各10条)
-- 数据库A的数据
USE a;
INSERT INTO users (username, age) VALUES ('Alice_A', 25);
INSERT INTO users (username, age) VALUES ('Bob_A', 30);
INSERT INTO users (username, age) VALUES ('Charlie_A', 22);
INSERT INTO users (username, age) VALUES ('David_A', 28);
INSERT INTO users (username, age) VALUES ('Eve_A', 35);
INSERT INTO users (username, age) VALUES ('Frank_A', 40);
INSERT INTO users (username, age) VALUES ('Grace_A', 27);
INSERT INTO users (username, age) VALUES ('Henry_A', 33);
INSERT INTO users (username, age) VALUES ('Ivy_A', 29);
INSERT INTO users (username, age) VALUES ('Jack_A', 31);-- 数据库B的数据
USE b;
INSERT INTO users (username, age) VALUES ('Alice_B', 26);
INSERT INTO users (username, age) VALUES ('Bob_B', 32);
INSERT INTO users (username, age) VALUES ('Charlie_B', 23);
INSERT INTO users (username, age) VALUES ('David_B', 29);
INSERT INTO users (username, age) VALUES ('Eve_B', 36);
INSERT INTO users (username, age) VALUES ('Frank_B', 41);
INSERT INTO users (username, age) VALUES ('Grace_B', 28);
INSERT INTO users (username, age) VALUES ('Henry_B', 34);
INSERT INTO users (username, age) VALUES ('Ivy_B', 30);
INSERT INTO users (username, age) VALUES ('Jack_B', 32);
代码
import lombok.Data;import java.sql.*;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;public class CrossDatabasePagination {// 数据库连接信息private static final String DB_URL_A = "jdbc:mysql:///:3306/a";private static final String DB_URL_B = "jdbc:mysql:///:3306/b";private static final String USER = "root";private static final String PASS = "cnmsb";public static void main(String[] args) {int page = 2; // 第2页int pageSize = 5; // 每页5条List<User> combinedResults = getCrossDatabaseUsers(page, pageSize);System.out.println("跨库分页查询结果(第" + page + "页, 每页" + pageSize + "条):");combinedResults.forEach(System.out::println);}public static List<User> getCrossDatabaseUsers(int page, int pageSize) {List<User> allUsers = new ArrayList<>();// 从数据库A查询，标记来源为"a"allUsers.addAll(getUsersFromDatabase(DB_URL_A, page, pageSize, "a"));// 从数据库B查询，标记来源为"b"allUsers.addAll(getUsersFromDatabase(DB_URL_B, page, pageSize, "b"));// 内存中合并、排序和分页return allUsers.stream().sorted(Comparator.comparing(User::getId)).skip((page - 1) * pageSize).limit(pageSize).collect(Collectors.toList());}private static List<User> getUsersFromDatabase(String dbUrl, int page, int pageSize, String sourceDb) {List<User> users = new ArrayList<>();// 计算每个库应该查询的数据量int limit = pageSize;int offset = (page - 1) * pageSize;String sql = "SELECT id, username, age, create_time FROM users ORDER BY id LIMIT ? OFFSET ?";try (Connection conn = DriverManager.getConnection(dbUrl, USER, PASS);PreparedStatement pstmt = conn.prepareStatement(sql)) {pstmt.setInt(1, limit);pstmt.setInt(2, offset);ResultSet rs = pstmt.executeQuery();while (rs.next()) {User user = new User();user.setId(rs.getInt("id"));user.setUsername(rs.getString("username"));user.setAge(rs.getInt("age"));user.setCreateTime(rs.getTimestamp("create_time"));user.setSourceDb(sourceDb); // 设置数据来源users.add(user);}} catch (SQLException e) {System.err.println("查询数据库出错: " + dbUrl);e.printStackTrace();}return users;}@Datastatic class User {private int id;private String username;private int age;private Timestamp createTime;private String sourceDb; // 新增字段，标识数据来源@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", age=" + age +", createTime=" + createTime +", sourceDb='" + sourceDb + '\'' +'}';}}
}
