Mysql实现水平分库的示例代码

import Java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MySQLSharding {
    private static final String URL_PREFIX = "jdbc:mysql://";
    private static final String URL_SUFFIX = "?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWord = "password";
    public Connection getConnection(int userId) throws SQLException {
        String url = URL_PREFIX + getDatabaseName(userId) + URL_SUFFIX;
        return DriverManager.getConnection(url, USERNAME, PASSWORD);
    }
    private String getDatabaseName(int userId) {
        int databaseIndex = userId % 2;
        return "database_" + databaseIndex;
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class QueryService {
    private MySQLSharding mySQLSharding;
    public QueryService(MySQLSharding mySQLSharding) {
        this.mySQLSharding = mySQLSharding;
    }
    public List<User> queryUsersByRegion(String region) throws SQLException {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 2; i++) {
            try (Connection connection = mySQLSharding.getConnection(i)) {
                String sql = "SELECT * FROM user WHERE region = ?";
                try (PreparedStatement statement = connection.prepareStatement(sql)) {
                    statement.setString(1, region);
                    try (ResultSet resultSet = statement.executeQuery()) {
                        while (resultSet.next()) {
                            User user = new User();
                            user.setId(resultSet.getInt("id"));
                            user.setName(resultSet.getString("name"));
                            user.setRegion(resultSet.getString("region"));
                            users.add(user);
                        }
                    }
                }
            }
        }
        return users;
    }
}

   public void insertUser(User user) throws SQLException {
        try (Connection connection = mySQLSharding.getConnection(user.getId())) {
            String sql = "INSERT INTO user (id, name, region) VALUES (?, ?, ?)";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setInt(1, user.getId());
                statement.setString(2, user.getName());
                statement.setString(3, user.getRegion());
                statement.executeUpdate();
            }
        }
    }