手搓一个不用中间件的分表策略

场景：针对一些特别的项目，不用中间件，以月为维度进行分表，代码详细设计方案

1. 定义分片策略

首先，定义一个分片策略类，用于决定数据存储在哪个分表中

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;public class ShardingStrategy {private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy_MM");public String getShardId(LocalDate date) {return date.format(formatter);}
}

2. 数据库连接管理

创建一个数据库连接管理类，用于管理数据库连接

import java.sql.Connection;
import java.sql.DriverManager;public class DatabaseManager {private static Connection connection;static {try {Class.forName("com.mysql.cj.jdbc.Driver");connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "user", "password");} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() {return connection;}
}

3. 动态创建表

创建一个方法，用于根据当前月份动态创建新的表

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;public class TableCreator {private ShardingStrategy shardingStrategy = new ShardingStrategy();public void createTableForMonth(LocalDate date) {String shardId = shardingStrategy.getShardId(date);String tableName = "orders_" + shardId;Connection connection = DatabaseManager.getConnection();String createTableSQL = "CREATE TABLE IF NOT EXISTS " + tableName + " ("+ "id BIGINT PRIMARY KEY,"+ "order_date DATE,"+ "customer_name VARCHAR(255)"+ ")";try (PreparedStatement statement = connection.prepareStatement(createTableSQL)) {statement.execute();System.out.println("Table " + tableName + " created successfully.");} catch (SQLException e) {e.printStackTrace();}}
}

4. 订单操作类

创建一个订单操作类，用于执行具体的数据库操作。这里根据分片策略动态生成表名

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;public class OrderDAO {private ShardingStrategy shardingStrategy = new ShardingStrategy();private TableCreator tableCreator = new TableCreator();public void createOrder(long orderId, LocalDate orderDate, String customerName) {// 确保表存在tableCreator.createTableForMonth(orderDate);String shardId = shardingStrategy.getShardId(orderDate);String tableName = "orders_" + shardId;Connection connection = DatabaseManager.getConnection();try (PreparedStatement statement = connection.prepareStatement("INSERT INTO " + tableName + " (id, order_date, customer_name) VALUES (?, ?, ?)")) {statement.setLong(1, orderId);statement.setDate(2, java.sql.Date.valueOf(orderDate));statement.setString(3, customerName);statement.executeUpdate();} catch (SQLException e) {e.printStackTrace();}}public String getCustomerNameByOrderId(long orderId, LocalDate orderDate) {String shardId = shardingStrategy.getShardId(orderDate);String tableName = "orders_" + shardId;Connection connection = DatabaseManager.getConnection();try (PreparedStatement statement = connection.prepareStatement("SELECT customer_name FROM " + tableName + " WHERE id = ?")) {statement.setLong(1, orderId);ResultSet resultSet = statement.executeQuery();if (resultSet.next()) {return resultSet.getString("customer_name");}} catch (SQLException e) {e.printStackTrace();}return null;}
}

5. 使用示例

最后，展示如何使用上述类来创建订单和查询订单信息

public class Main {public static void main(String[] args) {OrderDAO orderDAO = new OrderDAO();// 创建订单orderDAO.createOrder(1, LocalDate.of(2023, 1, 15), "Alice");orderDAO.createOrder(2, LocalDate.of(2023, 2, 20), "Bob");orderDAO.createOrder(3, LocalDate.of(2023, 3, 10), "Charlie");// 查询订单System.out.println("Order ID 1: " + orderDAO.getCustomerNameByOrderId(1, LocalDate.of(2023, 1, 15)));System.out.println("Order ID 2: " + orderDAO.getCustomerNameByOrderId(2, LocalDate.of(2023, 2, 20)));System.out.println("Order ID 3: " + orderDAO.getCustomerNameByOrderId(3, LocalDate.of(2023, 3, 10)));}
}