【MySQL】【深度实战】【Java】

管理员
# MySQL深度实战：从架构原理到生产级性能优化 ## 目录 1. [MySQL核心架构与存储引擎](#1-mysql核心架构与存储引擎) 2. [设计模式在MySQL客户端中的应用](#2-设计模式在mysql客户端中的应用) 3. [Java反射机制实现ORM映射](#3-java反射机制实现orm映射) 4. [自定义注解实现SQL操作](#4-自定义注解实现sql操作) 5. [MySQL性能优化与JVM调优](#5-mysql性能优化与jvm调优) 6. [生产级数据库工具类实现](#6-生产级数据库工具类实现) 7. [数据库连接池优化](#7-数据库连接池优化) 8. [分库分表方案实现](#8-分库分表方案实现) 9. [读写分离与主从同步](#9-读写分离与主从同步) 10. [完整使用示例](#10-完整使用示例) --- ## 1. MySQL核心架构与存储引擎 ### 1.1 MySQL架构分层 MySQL采用分层架构设计，从上到下分为连接层、服务层、引擎层和存储层。 ```java /** * MySQL架构分层模拟 * 展示MySQL的核心架构组件 */ public class MySQLArchitecture { /** * 连接层 - 负责客户端连接、身份认证、线程管理 */ public static class ConnectionLayer { // 连接池管理 private ConnectionPool connectionPool; // 线程池管理 private ThreadPool threadPool; /** * 处理客户端连接请求 */ public void handleConnection(ClientConnection client) { // 1. 身份认证 authenticate(client); // 2. 分配线程 Thread thread = threadPool.allocateThread(client); // 3. 建立会话 Session session = createSession(client, thread); // 4. 进入服务层处理 ServiceLayer serviceLayer = new ServiceLayer(session); serviceLayer.processQueries(); } private void authenticate(ClientConnection client) { // 验证用户名、密码、主机权限 } private Session createSession(ClientConnection client, Thread thread) { return new Session(client, thread); } } /** * 服务层 - 核心功能层 */ public static class ServiceLayer { private Session session; // SQL接口 private SQLInterface sqlInterface; // 解析器 private Parser parser; // 优化器 private Optimizer optimizer; // 缓存 private QueryCache queryCache; // 权限管理 private PrivilegeManager privilegeManager; public ServiceLayer(Session session) { this.session = session; } /** * 处理查询请求 */ public void processQueries() { while (session.isConnected()) { String sql = session.receiveQuery(); // 1. 检查查询缓存 QueryResult cachedResult = queryCache.get(sql); if (cachedResult != null) { session.sendResult(cachedResult); continue; } // 2. 解析SQL ParseTree parseTree = parser.parse(sql); // 3. 权限检查 if (!privilegeManager.checkPrivilege(session, parseTree)) { session.sendError("权限不足"); continue; } // 4. 查询优化 ExecutionPlan executionPlan = optimizer.optimize(parseTree); // 5. 执行查询 EngineLayer engineLayer = new EngineLayer(executionPlan); QueryResult result = engineLayer.execute(); // 6. 缓存查询结果 queryCache.put(sql, result); // 7. 返回结果 session.sendResult(result); } } } /** * 引擎层 - 插件式存储引擎 */ public static class EngineLayer { private ExecutionPlan executionPlan; public EngineLayer(ExecutionPlan executionPlan) { this.executionPlan = executionPlan; } /** * 执行查询（选择合适的存储引擎） */ public QueryResult execute() { String engineName = executionPlan.getEngineName(); // 工厂模式：根据引擎名称创建对应的引擎实例 StorageEngine engine = StorageEngineFactory.create(engineName); return engine.execute(executionPlan); } } /** * 存储引擎工厂 - 工厂模式 */ public static class StorageEngineFactory { public static StorageEngine create(String engineName) { switch (engineName.toLowerCase()) { case "innodb": return new InnoDBEngine(); case "myisam": return new MyISAMEngine(); case "memory": return new MemoryEngine(); case "csv": return new CSVEngine(); default: throw new IllegalArgumentException("不支持的存储引擎: " + engineName); } } } /** * 存储引擎接口 */ public interface StorageEngine { QueryResult execute(ExecutionPlan plan); Transaction beginTransaction(); Index createIndex(IndexDefinition definition); } /** * InnoDB引擎实现（支持事务、行锁、外键） */ public static class InnoDBEngine implements StorageEngine { // 缓冲池 private BufferPool bufferPool; // 重做日志 private RedoLog redoLog; // 回滚日志 private UndoLog undoLog; @Override public QueryResult execute(ExecutionPlan plan) { // 1. 事务管理 Transaction transaction = beginTransaction(); try { // 2. MVCC多版本并发控制 QueryResult result = executeWithMVCC(plan, transaction); // 3. 提交事务 transaction.commit(); return result; } catch (Exception e) { transaction.rollback(); throw e; } } private QueryResult executeWithMVCC(ExecutionPlan plan, Transaction transaction) { // 实现MVCC逻辑 return new QueryResult(); } @Override public Transaction beginTransaction() { return new InnoDBTransaction(); } @Override public Index createIndex(IndexDefinition definition) { // InnoDB使用B+树索引 return new BPlusTreeIndex(definition); } } /** * MyISAM引擎实现（不支持事务、表锁、快速读取） */ public static class MyISAMEngine implements StorageEngine { // 数据文件 private DataFile dataFile; // 索引文件 private IndexFile indexFile; @Override public QueryResult execute(ExecutionPlan plan) { // MyISAM不支持事务 return executeDirect(plan); } private QueryResult executeDirect(ExecutionPlan plan) { // 直接执行查询 return new QueryResult(); } @Override public Transaction beginTransaction() { throw new UnsupportedOperationException("MyISAM不支持事务"); } @Override public Index createIndex(IndexDefinition definition) { // MyISAM也使用B+树索引 return new BPlusTreeIndex(definition); } } /** * 内存引擎实现（数据存储在内存，速度极快） */ public static class MemoryEngine implements StorageEngine { private Map> tables = new ConcurrentHashMap<>(); @Override public QueryResult execute(ExecutionPlan plan) { // 从内存表读取数据 String tableName = plan.getTableName(); Map table = tables.get(tableName); QueryResult result = new QueryResult(); result.setData(table.values()); return result; } @Override public Transaction beginTransaction() { // Memory引擎支持有限的事务 return new MemoryTransaction(); } @Override public Index createIndex(IndexDefinition definition) { // Memory引擎使用Hash索引 return new HashIndex(definition); } } /** * CSV引擎实现（数据以CSV格式存储） */ public static class CSVEngine implements StorageEngine { @Override public QueryResult execute(ExecutionPlan plan) { // 读取CSV文件 return new QueryResult(); } @Override public Transaction beginTransaction() { throw new UnsupportedOperationException("CSV引擎不支持事务"); } @Override public Index createIndex(IndexDefinition definition) { throw new UnsupportedOperationException("CSV引擎不支持索引"); } } } ``` ### 1.2 InnoDB存储引擎核心特性 ```java /** * InnoDB存储引擎核心特性实现 */ public class InnoDBEngineFeatures { /** * MVCC多版本并发控制 */ public static class MVCC { // 事务ID生成器 private AtomicLong transactionIdGenerator = new AtomicLong(0); // 活跃事务列表 private Set activeTransactions = new ConcurrentHashMap().keySet(Boolean.TRUE); /** * 生成新的事务ID */ public long beginTransaction() { long txId = transactionIdGenerator.incrementAndGet(); activeTransactions.add(txId); return txId; } /** * 提交事务 */ public void commitTransaction(long txId) { activeTransactions.remove(txId); // 清理过期版本 purgeOldVersions(); } /** * 读取数据快照（Read View） */ public ReadView createReadView(long txId) { return new ReadView(txId, new ArrayList<>(activeTransactions)); } /** * 判断数据是否对当前事务可见 */ public boolean isVisible(DataVersion dataVersion, ReadView readView) { long dataTxId = dataVersion.getTransactionId(); // 数据是在当前事务创建的 if (dataTxId == readView.getTransactionId()) { return true; } // 数据是在当前事务开始之前创建的 if (dataTxId < readView.getMinActiveTransactionId()) { return true; } // 数据是在当前事务开始之后创建的，检查是否已提交 return !readView.getActiveTransactionIds().contains(dataTxId); } private void purgeOldVersions() { // 清理不再需要的历史版本 } } /** * 读视图 */ public static class ReadView { private final long transactionId; private final List activeTransactionIds; private final long minActiveTransactionId; private final long maxActiveTransactionId; public ReadView(long transactionId, List activeTransactionIds) { this.transactionId = transactionId; this.activeTransactionIds = activeTransactionIds; this.minActiveTransactionId = activeTransactionIds.stream() .min(Long::compare).orElse(0L); this.maxActiveTransactionId = activeTransactionIds.stream() .max(Long::compare).orElse(0L); } public long getTransactionId() { return transactionId; } public List getActiveTransactionIds() { return activeTransactionIds; } public long getMinActiveTransactionId() { return minActiveTransactionId; } public long getMaxActiveTransactionId() { return maxActiveTransactionId; } } /** * 数据版本 */ public static class DataVersion { private long transactionId; private byte[] data; private DataVersion nextVersion; // 链表指向下一个版本 public DataVersion(long transactionId, byte[] data) { this.transactionId = transactionId; this.data = data; } public long getTransactionId() { return transactionId; } public byte[] getData() { return data; } public DataVersion getNextVersion() { return nextVersion; } public void setNextVersion(DataVersion nextVersion) { this.nextVersion = nextVersion; } } /** * 缓冲池 */ public static class BufferPool { // 缓冲池大小（页） private final int poolSize; // LRU链表 private final ConcurrentLinkedDeque lruList; // 哈希表（快速查找） private final ConcurrentHashMap pageMap; // 脏页列表 private final ConcurrentLinkedQueue dirtyPages; public BufferPool(int poolSize) { this.poolSize = poolSize; this.lruList = new ConcurrentLinkedDeque<>(); this.pageMap = new ConcurrentHashMap<>(); this.dirtyPages = new ConcurrentLinkedQueue<>(); } /** * 从缓冲池获取页 */ public BufferPage getPage(String tableSpace, long pageNumber) { String key = tableSpace + ":" + pageNumber; BufferPage page = pageMap.get(key); if (page != null) { // 更新LRU lruList.remove(page); lruList.addLast(page); return page; } // 从磁盘加载 page = loadFromDisk(tableSpace, pageNumber); // 如果缓冲池已满，执行淘汰策略 if (pageMap.size() >= poolSize) { evictPage(); } // 添加到缓冲池 pageMap.put(key, page); lruList.addLast(page); return page; } /** * 脏页淘汰策略 */ private void evictPage() { // 优先淘汰非脏页 Iterator iterator = lruList.iterator(); while (iterator.hasNext()) { BufferPage page = iterator.next(); if (!page.isDirty()) { iterator.remove(); pageMap.remove(page.getKey()); return; } } // 如果都是脏页，刷新并淘汰最旧的脏页 BufferPage oldestDirty = lruList.pollFirst(); if (oldestDirty != null) { flushPage(oldestDirty); pageMap.remove(oldestDirty.getKey()); } } /** * 标记页为脏页 */ public void markDirty(BufferPage page) { page.setDirty(true); dirtyPages.add(page); } /** * 刷新脏页到磁盘 */ public void flushDirtyPages() { BufferPage page; while ((page = dirtyPages.poll()) != null) { flushPage(page); } } private BufferPage loadFromDisk(String tableSpace, long pageNumber) { // 模拟从磁盘加载页 return new BufferPage(tableSpace, pageNumber); } private void flushPage(BufferPage page) { // 模拟刷新页到磁盘 page.setDirty(false); } } /** * 缓冲页 */ public static class BufferPage { private String tableSpace; private long pageNumber; private byte[] data; private boolean dirty; private long lastAccessTime; public BufferPage(String tableSpace, long pageNumber) { this.tableSpace = tableSpace; this.pageNumber = pageNumber; this.data = new byte[16 * 1024]; // 16KB页 this.dirty = false; this.lastAccessTime = System.currentTimeMillis(); } public String getKey() { return tableSpace + ":" + pageNumber; } public byte[] getData() { this.lastAccessTime = System.currentTimeMillis(); return data; } public void setData(byte[] data) { this.data = data; } public boolean isDirty() { return dirty; } public void setDirty(boolean dirty) { this.dirty = dirty; } public long getLastAccessTime() { return lastAccessTime; } } /** * B+树索引 */ public static class BPlusTreeIndex { private Node root; private int order; // B+树的阶 public BPlusTreeIndex(int order) { this.order = order; this.root = new LeafNode(); } /** * 插入键值对 */ public void insert(long key, RowLocation location) { root.insert(key, location); // 如果根节点分裂，创建新的根节点 if (root.isOverflow()) { InternalNode newRoot = new InternalNode(); newRoot.children.add(root); newRoot.splitChild(0); root = newRoot; } } /** * 查询键对应的位置 */ public RowLocation search(long key) { return root.search(key); } /** * 范围查询 */ public List rangeSearch(long minKey, long maxKey) { return root.rangeSearch(minKey, maxKey); } /** * B+树节点基类 */ public abstract class Node { protected List keys = new ArrayList<>(); public abstract RowLocation search(long key); public abstract List rangeSearch(long minKey, long maxKey); public abstract void insert(long key, RowLocation location); public abstract boolean isOverflow(); public abstract boolean isUnderflow(); } /** * 内部节点 */ public class InternalNode extends Node { protected List children = new ArrayList<>(); @Override public RowLocation search(long key) { int index = findChildIndex(key); return children.get(index).search(key); } @Override public List rangeSearch(long minKey, long maxKey) { int start = findChildIndex(minKey); List results = new ArrayList<>(); for (int i = start; i < children.size(); i++) { results.addAll(children.get(i).rangeSearch(minKey, maxKey)); } return results; } @Override public void insert(long key, RowLocation location) { int index = findChildIndex(key); children.get(index).insert(key, location); } @Override public boolean isOverflow() { return children.size() > order; } @Override public boolean isUnderflow() { return children.size() < (order + 1) / 2; } private int findChildIndex(long key) { int i = 0; while (i < keys.size() && key >= keys.get(i)) { i++; } return i; } public void splitChild(int index) { Node child = children.get(index); InternalNode newNode = new InternalNode(); int mid = child.keys.size() / 2; // 分裂键 long splitKey = child.keys.get(mid); // 分裂子节点 for (int i = mid + 1; i < child.keys.size(); i++) { newNode.keys.add(child.keys.get(i)); newNode.children.add(((InternalNode) child).children.get(i)); } newNode.children.add(((InternalNode) child).children.get(child.keys.size())); // 调整原节点 child.keys.subList(mid, child.keys.size()).clear(); ((InternalNode) child).children.subList(mid + 1, ((InternalNode) child).children.size()).clear(); // 插入新节点和分割键 keys.add(index, splitKey); children.add(index + 1, newNode); } } /** * 叶子节点 */ public class LeafNode extends Node { protected List locations = new ArrayList<>(); protected LeafNode next; @Override public RowLocation search(long key) { int index = Collections.binarySearch(keys, key); if (index >= 0) { return locations.get(index); } return null; } @Override public List rangeSearch(long minKey, long maxKey) { List results = new ArrayList<>(); for (int i = 0; i < keys.size(); i++) { if (keys.get(i) >= minKey && keys.get(i) <= maxKey) { results.add(locations.get(i)); } else if (keys.get(i) > maxKey) { break; } } // 检查下一个叶子节点 if (next != null) { results.addAll(next.rangeSearch(minKey, maxKey)); } return results; } @Override public void insert(long key, RowLocation location) { int index = Collections.binarySearch(keys, key); if (index < 0) { index = -index - 1; } keys.add(index, key); locations.add(index, location); } @Override public boolean isOverflow() { return keys.size() > order; } @Override public boolean isUnderflow() { return keys.size() < order / 2; } } } /** * 行位置 */ public static class RowLocation { private long pageNumber; private int offset; public RowLocation(long pageNumber, int offset) { this.pageNumber = pageNumber; this.offset = offset; } public long getPageNumber() { return pageNumber; } public int getOffset() { return offset; } } } ``` --- ## 2. 设计模式在MySQL客户端中的应用 ### 2.1 单例模式 - 数据库连接池管理 ```java /** * 数据库连接池 - 单例模式 * 确保全局只有一个连接池实例 */ public class DBConnectionPool { // 饿汉式单例 private static final DBConnectionPool INSTANCE = new DBConnectionPool(); // 连接池配置 private HikariDataSource dataSource; private final HikariConfig poolConfig; /** * 私有构造函数 */ private DBConnectionPool() { this.poolConfig = new HikariConfig(); initializePoolConfig(); } /** * 获取单例实例 */ public static DBConnectionPool getInstance() { return INSTANCE; } /** * 初始化连接池配置 */ private void initializePoolConfig() { // 连接池基本配置 poolConfig.setDriverClassName("com.mysql.cj.jdbc.Driver"); // 连接池大小配置 poolConfig.setMaximumPoolSize(200); // 最大连接数 poolConfig.setMinimumIdle(20); // 最小空闲连接数 poolConfig.setIdleTimeout(600000); // 空闲超时时间（10分钟） poolConfig.setMaxLifetime(1800000); // 连接最大生命周期（30分钟） poolConfig.setConnectionTimeout(30000); // 获取连接超时时间（30秒） // 连接测试配置 poolConfig.setConnectionTestQuery("SELECT 1"); poolConfig.setValidationTimeout(5000); // 泄漏检测配置 poolConfig.setLeakDetectionThreshold(60000); // 连接泄漏检测阈值（1分钟） // 性能优化配置 poolConfig.addDataSourceProperty("cachePrepStmts", "true"); // 缓存PreparedStatement poolConfig.addDataSourceProperty("prepStmtCacheSize", "250"); // 缓存大小 poolConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); // 缓存SQL长度限制 poolConfig.addDataSourceProperty("useServerPrepStmts", "true"); // 使用服务端PreparedStatement poolConfig.addDataSourceProperty("useLocalSessionState", "true"); poolConfig.addDataSourceProperty("rewriteBatchedStatements", "true"); // 批量语句重写 poolConfig.addDataSourceProperty("cacheResultSetMetadata", "true"); poolConfig.addDataSourceProperty("cacheServerConfiguration", "true"); poolConfig.addDataSourceProperty("elideSetAutoCommits", "true"); poolConfig.addDataSourceProperty("maintainTimeStats", "false"); // 时区配置 poolConfig.addDataSourceProperty("serverTimezone", "Asia/Shanghai"); } /** * 初始化连接池 */ public synchronized void init(String url, String username, String password) { if (dataSource != null && !dataSource.isClosed()) { return; } poolConfig.setJdbcUrl(url); poolConfig.setUsername(username); poolConfig.setPassword(password); dataSource = new HikariDataSource(poolConfig); System.out.println("数据库连接池初始化成功"); } /** * 获取数据库连接 */ public Connection getConnection() throws SQLException { if (dataSource == null) { throw new IllegalStateException("连接池未初始化"); } return dataSource.getConnection(); } /** * 关闭连接池 */ public synchronized void close() { if (dataSource != null && !dataSource.isClosed()) { dataSource.close(); System.out.println("数据库连接池已关闭"); } } /** * 获取连接池状态 */ public Map getPoolStats() { Map stats = new HashMap<>(); if (dataSource != null) { HikariPoolMXBean poolProxy = dataSource.getHikariPoolMXBean(); stats.put("activeConnections", poolProxy.getActiveConnections()); stats.put("idleConnections", poolProxy.getIdleConnections()); stats.put("totalConnections", poolProxy.getTotalConnections()); stats.put("threadsAwaitingConnection", poolProxy.getThreadsAwaitingConnection()); } return stats; } } ``` ### 2.2 工厂模式 - SQL构建器工厂 ```java /** * SQL构建器工厂 - 工厂模式 * 根据不同类型创建对应的SQL构建器 */ public class SQLBuilderFactory { /** * SQL类型枚举 */ public enum SQLType { SELECT, INSERT, UPDATE, DELETE, COUNT, SUM, AVG, MAX, MIN } /** * 工厂方法：创建SQL构建器 */ public static SQLBuilder create(SQLType type) { switch (type) { case SELECT: return new SelectBuilder(); case INSERT: return new InsertBuilder(); case UPDATE: return new UpdateBuilder(); case DELETE: return new DeleteBuilder(); case COUNT: return new CountBuilder(); case SUM: return new AggregateBuilder("SUM"); case AVG: return new AggregateBuilder("AVG"); case MAX: return new AggregateBuilder("MAX"); case MIN: return new AggregateBuilder("MIN"); default: throw new IllegalArgumentException("不支持的SQL类型: " + type); } } /** * SQL构建器接口 */ public interface SQLBuilder { SQLBuilder table(String table); String build(); } /** * SELECT构建器 */ public static class SelectBuilder implements SQLBuilder { private StringBuilder sql = new StringBuilder("SELECT "); private boolean distinct = false; private List columns = new ArrayList<>(); private String table; private List whereConditions = new ArrayList<>(); private List groupByFields = new ArrayList<>(); private List orderByFields = new ArrayList<>(); private Integer limit; private Integer offset; public SelectBuilder distinct() { this.distinct = true; return this; } public SelectBuilder columns(String... cols) { Collections.addAll(this.columns, cols); return this; } public SelectBuilder column(String col) { this.columns.add(col); return this; } public SelectBuilder where(String condition) { this.whereConditions.add(condition); return this; } public SelectBuilder where(String field, Object value) { this.whereConditions.add(field + " = " + formatValue(value)); return this; } public SelectBuilder groupBy(String field) { this.groupByFields.add(field); return this; } public SelectBuilder orderBy(String field) { this.orderByFields.add(field + " ASC"); return this; } public SelectBuilder orderByDesc(String field) { this.orderByFields.add(field + " DESC"); return this; } public SelectBuilder limit(int limit) { this.limit = limit; return this; } public SelectBuilder offset(int offset) { this.offset = offset; return this; } @Override public SelectBuilder table(String table) { this.table = table; return this; } @Override public String build() { if (distinct) { sql.append("DISTINCT "); } // 处理列 if (columns.isEmpty()) { sql.append("*"); } else { sql.append(String.join(", ", columns)); } // 处理表 sql.append(" FROM ").append(table); // 处理WHERE条件 if (!whereConditions.isEmpty()) { sql.append(" WHERE ").append(String.join(" AND ", whereConditions)); } // 处理GROUP BY if (!groupByFields.isEmpty()) { sql.append(" GROUP BY ").append(String.join(", ", groupByFields)); } // 处理ORDER BY if (!orderByFields.isEmpty()) { sql.append(" ORDER BY ").append(String.join(", ", orderByFields)); } // 处理LIMIT和OFFSET if (limit != null) { sql.append(" LIMIT ").append(limit); if (offset != null) { sql.append(" OFFSET ").append(offset); } } return sql.toString(); } private String formatValue(Object value) { if (value == null) { return "NULL"; } else if (value instanceof String) { return "'" + value + "'"; } else if (value instanceof Date) { return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value) + "'"; } else { return String.valueOf(value); } } } /** * INSERT构建器 */ public static class InsertBuilder implements SQLBuilder { private String table; private List columns = new ArrayList<>(); private List values = new ArrayList<>(); public InsertBuilder column(String column) { this.columns.add(column); return this; } public InsertBuilder columns(String... cols) { Collections.addAll(this.columns, cols); return this; } public InsertBuilder value(Object value) { this.values.add(value); return this; } public InsertBuilder values(Object... vals) { Collections.addAll(this.values, vals); return this; } @Override public InsertBuilder table(String table) { this.table = table; return this; } @Override public String build() { StringBuilder sql = new StringBuilder("INSERT INTO "); sql.append(table); if (!columns.isEmpty()) { sql.append(" (").append(String.join(", ", columns)).append(")"); } sql.append(" VALUES ("); List formattedValues = new ArrayList<>(); for (Object value : values) { formattedValues.add(formatValue(value)); } sql.append(String.join(", ", formattedValues)); sql.append(")"); return sql.toString(); } private String formatValue(Object value) { if (value == null) { return "NULL"; } else if (value instanceof String) { return "'" + value + "'"; } else if (value instanceof Date) { return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value) + "'"; } else { return String.valueOf(value); } } } /** * UPDATE构建器 */ public static class UpdateBuilder implements SQLBuilder { private String table; private Map setValues = new LinkedHashMap<>(); private List whereConditions = new ArrayList<>(); public UpdateBuilder set(String column, Object value) { this.setValues.put(column, value); return this; } public UpdateBuilder where(String condition) { this.whereConditions.add(condition); return this; } public UpdateBuilder where(String field, Object value) { this.whereConditions.add(field + " = " + formatValue(value)); return this; } @Override public UpdateBuilder table(String table) { this.table = table; return this; } @Override public String build() { StringBuilder sql = new StringBuilder("UPDATE "); sql.append(table); sql.append(" SET "); // 处理SET子句 List setClauses = new ArrayList<>(); for (Map.Entry entry : setValues.entrySet()) { setClauses.add(entry.getKey() + " = " + formatValue(entry.getValue())); } sql.append(String.join(", ", setClauses)); // 处理WHERE条件 if (!whereConditions.isEmpty()) { sql.append(" WHERE ").append(String.join(" AND ", whereConditions)); } return sql.toString(); } private String formatValue(Object value) { if (value == null) { return "NULL"; } else if (value instanceof String) { return "'" + value + "'"; } else if (value instanceof Date) { return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value) + "'"; } else { return String.valueOf(value); } } } /** * DELETE构建器 */ public static class DeleteBuilder implements SQLBuilder { private String table; private List whereConditions = new ArrayList<>(); public DeleteBuilder where(String condition) { this.whereConditions.add(condition); return this; } public DeleteBuilder where(String field, Object value) { this.whereConditions.add(field + " = " + formatValue(value)); return this; } @Override public DeleteBuilder table(String table) { this.table = table; return this; } @Override public String build() { StringBuilder sql = new StringBuilder("DELETE FROM "); sql.append(table); if (!whereConditions.isEmpty()) { sql.append(" WHERE ").append(String.join(" AND ", whereConditions)); } else { throw new IllegalStateException("DELETE语句必须包含WHERE条件"); } return sql.toString(); } private String formatValue(Object value) { if (value == null) { return "NULL"; } else if (value instanceof String) { return "'" + value + "'"; } else if (value instanceof Date) { return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value) + "'"; } else { return String.valueOf(value); } } } /** * 聚合函数构建器 */ public static class AggregateBuilder implements SQLBuilder { private String function; private String column; private String table; private List whereConditions = new ArrayList<>(); public AggregateBuilder(String function) { this.function = function; } @Override public AggregateBuilder table(String table) { this.table = table; return this; } public AggregateBuilder column(String column) { this.column = column; return this; } public AggregateBuilder where(String condition) { this.whereConditions.add(condition); return this; } @Override public String build() { StringBuilder sql = new StringBuilder("SELECT "); sql.append(function).append("("); if (column != null) { sql.append(column); } else { sql.append("*"); } sql.append(") FROM ").append(table); if (!whereConditions.isEmpty()) { sql.append(" WHERE ").append(String.join(" AND ", whereConditions)); } return sql.toString(); } } /** * COUNT构建器 */ public static class CountBuilder implements SQLBuilder { private String column; private String table; private List whereConditions = new ArrayList<>(); private boolean distinct = false; public CountBuilder column(String column) { this.column = column; return this; } public CountBuilder distinct() { this.distinct = true; return this; } @Override public CountBuilder table(String table) { this.table = table; return this; } public CountBuilder where(String condition) { this.whereConditions.add(condition); return this; } @Override public String build() { StringBuilder sql = new StringBuilder("SELECT COUNT("); if (distinct) { sql.append("DISTINCT "); } if (column != null) { sql.append(column); } else { sql.append("*"); } sql.append(") FROM ").append(table); if (!whereConditions.isEmpty()) { sql.append(" WHERE ").append(String.join(" AND ", whereConditions)); } return sql.toString(); } } } ``` ### 2.3 建造者模式 - 复杂查询构建 ```java /** * 复杂查询构建器 - 建造者模式 */ public class ComplexQueryBuilder { /** * 查询上下文 */ public static class QueryContext { private List selectFields = new ArrayList<>(); private List fromTables = new ArrayList<>(); private List joinClauses = new ArrayList<>(); private List whereConditions = new ArrayList<>(); private List groupByFields = new ArrayList<>(); private List havingConditions = new ArrayList<>(); private List orderByFields = new ArrayList<>(); private Integer limit; private Integer offset; private boolean distinct = false; private List subQueries = new ArrayList<>(); } /** * 子查询 */ public static class SubQuery { private String alias; private QueryContext context; public SubQuery(String alias, QueryContext context) { this.alias = alias; this.context = context; } public String build() { return "(" + buildQuery(context) + ") AS " + alias; } } private QueryContext context; public ComplexQueryBuilder() { this.context = new QueryContext(); } /** * SELECT字段 */ public ComplexQueryBuilder select(String... fields) { Collections.addAll(context.selectFields, fields); return this; } /** * SELECT DISTINCT */ public ComplexQueryBuilder selectDistinct(String... fields) { context.distinct = true; Collections.addAll(context.selectFields, fields); return this; } /** * FROM表 */ public ComplexQueryBuilder from(String table) { context.fromTables.add(table); return this; } /** * FROM子查询 */ public ComplexQueryBuilder from(SubQuery subQuery) { context.subQueries.add(subQuery); context.fromTables.add(subQuery.build()); return this; } /** * INNER JOIN */ public ComplexQueryBuilder join(String table, String onCondition) { context.joinClauses.add("INNER JOIN " + table + " ON " + onCondition); return this; } /** * LEFT JOIN */ public ComplexQueryBuilder leftJoin(String table, String onCondition) { context.joinClauses.add("LEFT JOIN " + table + " ON " + onCondition); return this; } /** * RIGHT JOIN */ public ComplexQueryBuilder rightJoin(String table, String onCondition) { context.joinClauses.add("RIGHT JOIN " + table + " ON " + onCondition); return this; } /** * WHERE条件 */ public ComplexQueryBuilder where(String condition) { context.whereConditions.add(condition); return this; } /** * WHERE AND条件 */ public ComplexQueryBuilder andWhere(String field, Object value) { context.whereConditions.add(field + " = " + formatValue(value)); return this; } /** * WHERE OR条件 */ public ComplexQueryBuilder orWhere(String condition) { int lastIndex = context.whereConditions.size() - 1; if (lastIndex >= 0) { String lastCondition = context.whereConditions.get(lastIndex); context.whereConditions.set(lastIndex, "(" + lastCondition + ")"); } context.whereConditions.add("OR " + condition); return this; } /** * WHERE IN条件 */ public ComplexQueryBuilder whereIn(String field, Collection values) { List formattedValues = new ArrayList<>(); for (Object value : values) { formattedValues.add(formatValue(value)); } context.whereConditions.add(field + " IN (" + String.join(", ", formattedValues) + ")"); return this; } /** * WHERE BETWEEN条件 */ public ComplexQueryBuilder whereBetween(String field, Object start, Object end) { context.whereConditions.add(field + " BETWEEN " + formatValue(start) + " AND " + formatValue(end)); return this; } /** * WHERE LIKE条件 */ public ComplexQueryBuilder whereLike(String field, String pattern) { context.whereConditions.add(field + " LIKE '" + pattern + "'"); return this; } /** * WHERE IS NULL条件 */ public ComplexQueryBuilder whereIsNull(String field) { context.whereConditions.add(field + " IS NULL"); return this; } /** * WHERE IS NOT NULL条件 */ public ComplexQueryBuilder whereIsNotNull(String field) { context.whereConditions.add(field + " IS NOT NULL"); return this; } /** * GROUP BY */ public ComplexQueryBuilder groupBy(String... fields) { Collections.addAll(context.groupByFields, fields); return this; } /** * HAVING条件 */ public ComplexQueryBuilder having(String condition) { context.havingConditions.add(condition); return this; } /** * ORDER BY */ public ComplexQueryBuilder orderBy(String field) { context.orderByFields.add(field + " ASC"); return this; } /** * ORDER BY DESC */ public ComplexQueryBuilder orderByDesc(String field) { context.orderByFields.add(field + " DESC"); return this; } /** * LIMIT */ public ComplexQueryBuilder limit(int limit) { context.limit = limit; return this; } /** * OFFSET */ public ComplexQueryBuilder offset(int offset) { context.offset = offset; return this; } /** * 构建查询SQL */ public String build() { return buildQuery(context); } private String buildQuery(QueryContext ctx) { StringBuilder sql = new StringBuilder(); // SELECT sql.append("SELECT "); if (ctx.distinct) { sql.append("DISTINCT "); } if (ctx.selectFields.isEmpty()) { sql.append("*"); } else { sql.append(String.join(", ", ctx.selectFields)); } // FROM sql.append(" FROM ").append(String.join(", ", ctx.fromTables)); // JOIN for (String join : ctx.joinClauses) { sql.append(" ").append(join); } // WHERE if (!ctx.whereConditions.isEmpty()) { sql.append(" WHERE ").append(String.join(" AND ", ctx.whereConditions)); } // GROUP BY if (!ctx.groupByFields.isEmpty()) { sql.append(" GROUP BY ").append(String.join(", ", ctx.groupByFields)); } // HAVING if (!ctx.havingConditions.isEmpty()) { sql.append(" HAVING ").append(String.join(" AND ", ctx.havingConditions)); } // ORDER BY if (!ctx.orderByFields.isEmpty()) { sql.append(" ORDER BY ").append(String.join(", ", ctx.orderByFields)); } // LIMIT和OFFSET if (ctx.limit != null) { sql.append(" LIMIT ").append(ctx.limit); if (ctx.offset != null) { sql.append(" OFFSET ").append(ctx.offset); } } return sql.toString(); } private String formatValue(Object value) { if (value == null) { return "NULL"; } else if (value instanceof String) { return "'" + value + "'"; } else if (value instanceof Date) { return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value) + "'"; } else if (value instanceof Collection) { List formattedValues = new ArrayList<>(); for (Object item : (Collection) value) { formattedValues.add(formatValue(item)); } return "(" + String.join(", ", formattedValues) + ")"; } else { return String.valueOf(value); } } /** * 创建子查询 */ public static SubQuery createSubQuery(String alias, ComplexQueryBuilder builder) { return new SubQuery(alias, builder.context); } } ``` --- ## 3. Java反射机制实现ORM映射 ### 3.1 实体类注解定义 ```java /** * ORM映射注解定义 */ public class ORMAnnotations { /** * 表注解 - 标记实体类对应的数据库表 */ @Target(ElementType.TYPE) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Table { /** * 表名 */ String name(); /** * 数据库 */ String catalog() default ""; /** * 模式 */ String schema() default ""; } /** * 列注解 - 标记字段对应的数据库列 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Column { /** * 列名 */ String name(); /** * 是否为主键 */ boolean primaryKey() default false; /** * 是否自增 */ boolean autoIncrement() default false; /** * 列类型 */ String type() default ""; /** * 列长度 */ int length() default 0; /** * 是否可为空 */ boolean nullable() default true; /** * 默认值 */ String defaultValue() default ""; /** * 列备注 */ String comment() default ""; } /** * ID注解 - 标记主键字段 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Id { /** * 生成策略 */ GenerationType strategy() default GenerationType.AUTO; /** * 生成器名称 */ String generator() default ""; } /** * 生成策略枚举 */ public enum GenerationType { AUTO, // 数据库自动生成 IDENTITY, // 自增 SEQUENCE, // 序列 TABLE, // 表生成 UUID, // UUID SNOWFLAKE // 雪花算法 } /** * 过滤注解 - 标记不映射到数据库的字段 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Transient { } /** * 关联注解 - 一对一关系 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface OneToOne { /** * 关联的实体类 */ Class targetEntity() default void.class; /** * 级联操作 */ CascadeType[] cascade() default {}; /** * 是否立即加载 */ boolean fetch() default false; /** * 映射的列 */ String mappedBy() default ""; } /** * 级联操作类型 */ public enum CascadeType { PERSIST, // 级联保存 MERGE, // 级联更新 REMOVE, // 级联删除 REFRESH, // 级联刷新 DETACH, // 级联分离 ALL // 全部 } /** * 关联注解 - 一对多关系 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface OneToMany { /** * 关联的实体类 */ Class targetEntity() default void.class; /** * 级联操作 */ CascadeType[] cascade() default {}; /** * 映射的列 */ String mappedBy() default ""; } /** * 关联注解 - 多对一关系 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ManyToOne { /** * 关联的实体类 */ Class targetEntity() default void.class; /** * 级联操作 */ CascadeType[] cascade() default {}; } /** * 关联注解 - 多对多关系 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ManyToMany { /** * 关联的实体类 */ Class targetEntity() default void.class; /** * 中间表名 */ String joinTable() default ""; /** * 级联操作 */ CascadeType[] cascade() default {}; /** * 映射的列 */ String mappedBy() default ""; } /** * 索引注解 */ @Target(ElementType.TYPE) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Indexes { Index[] value(); } /** * 索引注解 */ @Target(ElementType.TYPE) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Index { /** * 索引名 */ String name(); /** * 索引列 */ String[] columnList(); /** * 是否唯一索引 */ boolean unique() default false; } } ``` ### 3.2 反射实现实体映射 ```java /** * 实体映射器 - 使用反射机制实现ORM映射 */ public class EntityMapper { private static final Logger logger = LoggerFactory.getLogger(EntityMapper.class); private final Class entityClass; private Table tableAnnotation; private Map fieldMappings = new HashMap<>(); private FieldMapping primaryKeyMapping; /** * 字段映射 */ public static class FieldMapping { private Field field; private Column columnAnnotation; private String columnName; private boolean isPrimaryKey; private boolean isAutoIncrement; public FieldMapping(Field field, Column columnAnnotation) { this.field = field; this.columnAnnotation = columnAnnotation; this.columnName = columnAnnotation.name(); this.isPrimaryKey = columnAnnotation.primaryKey(); this.isAutoIncrement = columnAnnotation.autoIncrement(); } public Field getField() { return field; } public String getColumnName() { return columnName; } public boolean isPrimaryKey() { return isPrimaryKey; } public boolean isAutoIncrement() { return isAutoIncrement; } public Object getValue(Object entity) throws IllegalAccessException { field.setAccessible(true); return field.get(entity); } public void setValue(Object entity, Object value) throws IllegalAccessException { field.setAccessible(true); field.set(entity, value); } } public EntityMapper(Class entityClass) { this.entityClass = entityClass; analyzeEntity(); } /** * 分析实体类 */ private void analyzeEntity() { // 获取表注解 tableAnnotation = entityClass.getAnnotation(Table.class); if (tableAnnotation == null) { throw new ORMException("实体类未标注@Table注解: " + entityClass.getName()); } // 分析字段 Field[] fields = entityClass.getDeclaredFields(); for (Field field : fields) { // 跳过静态字段和瞬时字段 if (Modifier.isStatic(field.getModifiers()) || field.isAnnotationPresent(ORMAnnotations.Transient.class)) { continue; } Column columnAnnotation = field.getAnnotation(Column.class); if (columnAnnotation != null) { FieldMapping mapping = new FieldMapping(field, columnAnnotation); fieldMappings.put(mapping.getColumnName(), mapping); // 记录主键 if (mapping.isPrimaryKey()) { if (primaryKeyMapping != null) { throw new ORMException("实体类存在多个主键: " + entityClass.getName()); } primaryKeyMapping = mapping; } } } if (primaryKeyMapping == null) { throw new ORMException("实体类未定义主键: " + entityClass.getName()); } logger.info("实体类分析完成: {}, 表: {}, 字段数: {}", entityClass.getSimpleName(), tableAnnotation.name(), fieldMappings.size()); } /** * 生成INSERT SQL */ public String buildInsertSQL(T entity) throws ORMException { StringBuilder sql = new StringBuilder("INSERT INTO "); sql.append(tableAnnotation.name()).append(" ("); List columnNames = new ArrayList<>(); List placeholders = new ArrayList<>(); for (FieldMapping mapping : fieldMappings.values()) { // 跳过自增主键 if (mapping.isPrimaryKey() && mapping.isAutoIncrement()) { continue; } columnNames.add(mapping.getColumnName()); placeholders.add("?"); } sql.append(String.join(", ", columnNames)) .append(") VALUES (") .append(String.join(", ", placeholders)) .append(")"); return sql.toString(); } /** * 生成UPDATE SQL */ public String buildUpdateSQL(T entity) throws ORMException { StringBuilder sql = new StringBuilder("UPDATE "); sql.append(tableAnnotation.name()).append(" SET "); List setClauses = new ArrayList<>(); Object primaryKeyValue = null; try { for (FieldMapping mapping : fieldMappings.values()) { if (mapping.isPrimaryKey()) { primaryKeyValue = mapping.getValue(entity); continue; } setClauses.add(mapping.getColumnName() + " = ?"); } if (primaryKeyValue == null) { throw new ORMException("主键值为空，无法更新: " + entityClass.getName()); } sql.append(String.join(", ", setClauses)) .append(" WHERE ") .append(primaryKeyMapping.getColumnName()) .append(" = ?"); return sql.toString(); } catch (IllegalAccessException e) { throw new ORMException("获取字段值失败", e); } } /** * 生成DELETE SQL */ public String buildDeleteSQL(Object id) { StringBuilder sql = new StringBuilder("DELETE FROM "); sql.append(tableAnnotation.name()) .append(" WHERE ") .append(primaryKeyMapping.getColumnName()) .append(" = ?"); return sql.toString(); } /** * 生成SELECT BY ID SQL */ public String buildSelectByIdSQL() { StringBuilder sql = new StringBuilder("SELECT "); List columnNames = new ArrayList<>(fieldMappings.keySet()); sql.append(String.join(", ", columnNames)) .append(" FROM ") .append(tableAnnotation.name()) .append(" WHERE ") .append(primaryKeyMapping.getColumnName()) .append(" = ?"); return sql.toString(); } /** * 生成SELECT ALL SQL */ public String buildSelectAllSQL() { StringBuilder sql = new StringBuilder("SELECT "); List columnNames = new ArrayList<>(fieldMappings.keySet()); sql.append(String.join(", ", columnNames)) .append(" FROM ") .append(tableAnnotation.name()); return sql.toString(); } /** * 生成COUNT SQL */ public String buildCountSQL(String whereClause) { StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM "); sql.append(tableAnnotation.name()); if (whereClause != null && !whereClause.isEmpty()) { sql.append(" WHERE ").append(whereClause); } return sql.toString(); } /** * 设置PreparedStatement参数（INSERT） */ public void setInsertParameters(PreparedStatement pstmt, T entity) throws SQLException, ORMException { int index = 1; try { for (FieldMapping mapping : fieldMappings.values()) { if (mapping.isPrimaryKey() && mapping.isAutoIncrement()) { continue; } Object value = mapping.getValue(entity); pstmt.setObject(index++, value); } } catch (IllegalAccessException e) { throw new ORMException("设置参数失败", e); } } /** * 设置PreparedStatement参数（UPDATE） */ public void setUpdateParameters(PreparedStatement pstmt, T entity) throws SQLException, ORMException { int index = 1; Object primaryKeyValue = null; try { for (FieldMapping mapping : fieldMappings.values()) { if (mapping.isPrimaryKey()) { primaryKeyValue = mapping.getValue(entity); continue; } Object value = mapping.getValue(entity); pstmt.setObject(index++, value); } // 设置主键 pstmt.setObject(index, primaryKeyValue); } catch (IllegalAccessException e) { throw new ORMException("设置参数失败", e); } } /** * 设置PreparedStatement参数（DELETE） */ public void setDeleteParameters(PreparedStatement pstmt, Object id) throws SQLException { pstmt.setObject(1, id); } /** * 从ResultSet映射实体 */ public T mapEntity(ResultSet rs) throws SQLException, ORMException { try { T entity = entityClass.newInstance(); for (FieldMapping mapping : fieldMappings.values()) { Object value = rs.getObject(mapping.getColumnName()); mapping.setValue(entity, value); } return entity; } catch (InstantiationException | IllegalAccessException e) { throw new ORMException("映射实体失败", e); } } /** * 从ResultSet映射实体列表 */ public List mapEntityList(ResultSet rs) throws SQLException, ORMException { List entities = new ArrayList<>(); while (rs.next()) { entities.add(mapEntity(rs)); } return entities; } /** * 获取表名 */ public String getTableName() { return tableAnnotation.name(); } /** * 获取主键列名 */ public String getPrimaryKeyColumnName() { return primaryKeyMapping.getColumnName(); } /** * 获取主键值 */ public Object getPrimaryKeyValue(T entity) throws ORMException { try { return primaryKeyMapping.getValue(entity); } catch (IllegalAccessException e) { throw new ORMException("获取主键值失败", e); } } /** * 生成主键值 */ public Object generatePrimaryKey(GenerationType strategy) throws ORMException { switch (strategy) { case UUID: return UUID.randomUUID().toString(); case SNOWFLAKE: return SnowFlakeIdGenerator.nextId(); default: return null; // 由数据库生成 } } } ``` --- ## 4. 自定义注解实现SQL操作 ### 4.1 DAO层注解定义 ```java /** * DAO层注解定义 */ public class DAOAnnotations { /** * INSERT注解 - 标记插入方法 */ @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Insert { /** * 自定义SQL */ String sql() default ""; /** * 是否返回生成的主键 */ boolean returnGeneratedKey() default false; } /** * UPDATE注解 - 标记更新方法 */ @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Update { /** * 自定义SQL */ String sql() default ""; } /** * DELETE注解 - 标记删除方法 */ @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Delete { /** * 自定义SQL */ String sql() default ""; } /** * SELECT注解 - 标记查询方法 */ @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Select { /** * 自定义SQL */ String sql() default ""; /** * 结果集映射 */ Class resultType() default void.class; } /** * 参数注解 - 标记方法参数对应SQL中的占位符 */ @Target(ElementType.PARAMETER) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Param { /** * 参数名 */ String value(); } /** * 批量操作注解 */ @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Batch { /** * SQL模板 */ String sql(); /** * 批量大小 */ int batchSize() default 100; } /** * 事务注解 */ @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Transaction { /** * 事务隔离级别 */ IsolationLevel isolation() default IsolationLevel.READ_COMMITTED; /** * 是否只读 */ boolean readOnly() default false; /** * 超时时间（秒） */ int timeout() default 30; } /** * 隔离级别枚举 */ public enum IsolationLevel { READ_UNCOMMITTED, // 读未提交 READ_COMMITTED, // 读已提交 REPEATABLE_READ, // 可重复读 SERIALIZABLE // 串行化 } } ``` ### 4.2 DAO代理实现 ```java /** * DAO代理 - 使用动态代理实现DAO接口 */ public class DAOProxy implements InvocationHandler { private static final Logger logger = LoggerFactory.getLogger(DAOProxy.class); private final Class daoInterface; private final DBConnectionPool connectionPool; private final Map, EntityMapper> entityMappers = new ConcurrentHashMap<>(); public DAOProxy(Class daoInterface, DBConnectionPool connectionPool) { this.daoInterface = daoInterface; this.connectionPool = connectionPool; analyzeDAOInterface(); } /** * 创建代理实例 */ @SuppressWarnings("unchecked") public static T createProxy(Class daoInterface, DBConnectionPool connectionPool) { return (T) Proxy.newProxyInstance( daoInterface.getClassLoader(), new Class[]{daoInterface}, new DAOProxy(daoInterface, connectionPool) ); } /** * 分析DAO接口 */ private void analyzeDAOInterface() { // 分析接口方法，获取实体类信息 Method[] methods = daoInterface.getDeclaredMethods(); for (Method method : methods) { analyzeMethod(method); } } /** * 分析方法 */ private void analyzeMethod(Method method) { Class returnType = method.getReturnType(); // 获取泛型返回类型 if (returnType == List.class) { Type genericReturnType = method.getGenericReturnType(); if (genericReturnType instanceof ParameterizedType) { Type[] typeArguments = ((ParameterizedType) genericReturnType).getActualTypeArguments(); if (typeArguments.length > 0 && typeArguments[0] instanceof Class) { Class entityClass = (Class) typeArguments[0]; registerEntityMapper(entityClass); } } } else if (!returnType.isPrimitive() && returnType != void.class) { registerEntityMapper(returnType); } } /** * 注册实体映射器 */ @SuppressWarnings("unchecked") private void registerEntityMapper(Class entityClass) { if (!entityMappers.containsKey(entityClass)) { entityMappers.put(entityClass, new EntityMapper<>(entityClass)); } } @Override public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { // 检查是否是Object方法 if (method.getDeclaringClass() == Object.class) { return method.invoke(this, args); } // 检查事务注解 Transaction transaction = method.getAnnotation(Transaction.class); if (transaction != null) { return executeInTransaction(method, args, transaction); } else { return executeMethod(method, args); } } /** * 在事务中执行方法 */ private Object executeInTransaction(Method method, Object[] args, Transaction transaction) throws Throwable { Connection conn = null; try { conn = connectionPool.getConnection(); conn.setAutoCommit(false); // 设置隔离级别 int isolationLevel = mapIsolationLevel(transaction.isolation()); conn.setTransactionIsolation(isolationLevel); // 设置只读 conn.setReadOnly(transaction.readOnly()); // 执行方法 Object result = executeMethodWithConnection(method, args, conn); // 提交事务 conn.commit(); return result; } catch (Exception e) { if (conn != null) { conn.rollback(); } logger.error("事务执行失败", e); throw e; } finally { if (conn != null) { conn.setAutoCommit(true); conn.close(); } } } /** * 执行方法 */ private Object executeMethod(Method method, Object[] args) throws Throwable { Connection conn = null; try { conn = connectionPool.getConnection(); return executeMethodWithConnection(method, args, conn); } finally { if (conn != null) { conn.close(); } } } /** * 使用连接执行方法 */ private Object executeMethodWithConnection(Method method, Object[] args, Connection conn) throws Throwable { // 检查SQL注解 Insert insert = method.getAnnotation(Insert.class); if (insert != null) { return handleInsert(method, args, conn, insert); } Update update = method.getAnnotation(Update.class); if (update != null) { return handleUpdate(method, args, conn, update); } Delete delete = method.getAnnotation(Delete.class); if (delete != null) { return handleDelete(method, args, conn, delete); } Select select = method.getAnnotation(Select.class); if (select != null) { return handleSelect(method, args, conn, select); } Batch batch = method.getAnnotation(Batch.class); if (batch != null) { return handleBatch(method, args, conn, batch); } throw new UnsupportedOperationException("方法未标注SQL注解: " + method.getName()); } /** * 处理INSERT */ private Object handleInsert(Method method, Object[] args, Connection conn, Insert insert) throws Throwable { String sql = insert.sql(); Class resultType = method.getReturnType(); if (sql.isEmpty()) { // 使用ORM自动生成SQL Class entityClass = method.getParameterTypes()[0]; @SuppressWarnings("unchecked") EntityMapper mapper = (EntityMapper) getEntityMapper(entityClass); sql = mapper.buildInsertSQL(args[0]); PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); mapper.setInsertParameters(pstmt, args[0]); int rows = pstmt.executeUpdate(); if (insert.returnGeneratedKey()) { ResultSet rs = pstmt.getGeneratedKeys(); if (rs.next()) { Object generatedKey = rs.getObject(1); return generatedKey; } } return rows; } else { // 使用自定义SQL PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); setParameters(pstmt, method, args); int rows = pstmt.executeUpdate(); if (insert.returnGeneratedKey()) { ResultSet rs = pstmt.getGeneratedKeys(); if (rs.next()) { return rs.getObject(1); } } return rows; } } /** * 处理UPDATE */ private Object handleUpdate(Method method, Object[] args, Connection conn, Update update) throws Throwable { String sql = update.sql(); if (sql.isEmpty()) { // 使用ORM自动生成SQL Class entityClass = method.getParameterTypes()[0]; @SuppressWarnings("unchecked") EntityMapper mapper = (EntityMapper) getEntityMapper(entityClass); sql = mapper.buildUpdateSQL(args[0]); PreparedStatement pstmt = conn.prepareStatement(sql); mapper.setUpdateParameters(pstmt, args[0]); return pstmt.executeUpdate(); } else { // 使用自定义SQL PreparedStatement pstmt = conn.prepareStatement(sql); setParameters(pstmt, method, args); return pstmt.executeUpdate(); } } /** * 处理DELETE */ private Object handleDelete(Method method, Object[] args, Connection conn, Delete delete) throws Throwable { String sql = delete.sql(); if (sql.isEmpty()) { // 使用ORM自动生成SQL Class entityClass = method.getParameterTypes()[0]; @SuppressWarnings("unchecked") EntityMapper mapper = (EntityMapper) getEntityMapper(entityClass); Object id = mapper.getPrimaryKeyValue(args[0]); sql = mapper.buildDeleteSQL(id); PreparedStatement pstmt = conn.prepareStatement(sql); mapper.setDeleteParameters(pstmt, id); return pstmt.executeUpdate(); } else { // 使用自定义SQL PreparedStatement pstmt = conn.prepareStatement(sql); setParameters(pstmt, method, args); return pstmt.executeUpdate(); } } /** * 处理SELECT */ private Object handleSelect(Method method, Object[] args, Connection conn, Select select) throws Throwable { String sql = select.sql(); Class resultType = select.resultType(); if (sql.isEmpty()) { // 使用ORM自动生成SQL if (resultType == void.class) { resultType = method.getReturnType(); } @SuppressWarnings("unchecked") EntityMapper mapper = (EntityMapper) getEntityMapper(resultType); if (args.length == 1 && method.getReturnType() == resultType) { // 根据ID查询 sql = mapper.buildSelectByIdSQL(); } else if (method.getReturnType() == List.class) { // 查询全部 sql = mapper.buildSelectAllSQL(); } PreparedStatement pstmt = conn.prepareStatement(sql); setParameters(pstmt, method, args); ResultSet rs = pstmt.executeQuery(); if (method.getReturnType() == List.class) { return mapper.mapEntityList(rs); } else if (method.getReturnType() == resultType) { if (rs.next()) { return mapper.mapEntity(rs); } return null; } else if (method.getReturnType() == Long.class || method.getReturnType() == long.class) { if (rs.next()) { return rs.getLong(1); } return 0L; } } else { // 使用自定义SQL PreparedStatement pstmt = conn.prepareStatement(sql); setParameters(pstmt, method, args); ResultSet rs = pstmt.executeQuery(); if (resultType != void.class) { @SuppressWarnings("unchecked") EntityMapper mapper = (EntityMapper) getEntityMapper(resultType); if (method.getReturnType() == List.class) { return mapper.mapEntityList(rs); } else { if (rs.next()) { return mapper.mapEntity(rs); } return null; } } else { // 直接返回ResultSet return rs; } } return null; } /** * 处理批量操作 */ private Object handleBatch(Method method, Object[] args, Connection conn, Batch batch) throws Throwable { String sql = batch.sql(); int batchSize = batch.batchSize(); // 获取集合参数 Collection collection = (Collection) args[0]; PreparedStatement pstmt = conn.prepareStatement(sql); int[] results = new int[collection.size()]; int index = 0; int batchCount = 0; for (Object item : collection) { if (item instanceof Object[]) { Object[] params = (Object[]) item; for (int i = 0; i < params.length; i++) { pstmt.setObject(i + 1, params[i]); } } else { pstmt.setObject(1, item); } pstmt.addBatch(); batchCount++; if (batchCount % batchSize == 0) { int[] batchResults = pstmt.executeBatch(); System.arraycopy(batchResults, 0, results, index, batchResults.length); index += batchResults.length; } } // 执行剩余批次 int[] batchResults = pstmt.executeBatch(); System.arraycopy(batchResults, 0, results, index, batchResults.length); return results.length; } /** * 设置PreparedStatement参数 */ private void setParameters(PreparedStatement pstmt, Method method, Object[] args) throws SQLException { Parameter[] parameters = method.getParameters(); for (int i = 0; i < parameters.length; i++) { Param paramAnnotation = parameters[i].getAnnotation(Param.class); if (paramAnnotation != null) { // 使用命名参数（需要SQL中包含:paramName格式的占位符） // 这里简化处理，直接按顺序设置 pstmt.setObject(i + 1, args[i]); } else { pstmt.setObject(i + 1, args[i]); } } } /** * 映射隔离级别 */ private int mapIsolationLevel(DAOAnnotations.IsolationLevel isolation) { switch (isolation) { case READ_UNCOMMITTED: return Connection.TRANSACTION_READ_UNCOMMITTED; case READ_COMMITTED: return Connection.TRANSACTION_READ_COMMITTED; case REPEATABLE_READ: return Connection.TRANSACTION_REPEATABLE_READ; case SERIALIZABLE: return Connection.TRANSACTION_SERIALIZABLE; default: return Connection.TRANSACTION_READ_COMMITTED; } } /** * 获取实体映射器 */ @SuppressWarnings("unchecked") private EntityMapper getEntityMapper(Class entityClass) { return (EntityMapper) entityMappers.computeIfAbsent(entityClass, EntityMapper::new); } } ``` --- ## 5. MySQL性能优化与JVM调优 ### 5.1 MySQL服务器端优化配置 ```sql -- MySQL性能优化配置（my.cnf） [mysqld] # ==================== 连接配置 ==================== max_connections = 500 # 最大连接数 max_connect_errors = 10000 # 最大连接错误数 wait_timeout = 28800 # 等待超时时间（8小时） interactive_timeout = 28800 # 交互超时时间（8小时） # ==================== InnoDB配置 ==================== # 内存配置 innodb_buffer_pool_size = 4G # 缓冲池大小（建议为物理内存的50-70%） innodb_buffer_pool_instances = 8 # 缓冲池实例数 innodb_log_buffer_size = 64M # 日志缓冲区大小 innodb_additional_mem_pool_size = 32M # 附加内存池大小 # 日志配置 innodb_log_file_size = 256M # 日志文件大小 innodb_log_files_in_group = 2 # 日志文件数量 innodb_flush_log_at_trx_commit = 2 # 事务提交时刷新日志 innodb_flush_method = O_DIRECT # 刷新方法 # I/O配置 innodb_io_capacity = 2000 # I/O容量 innodb_io_capacity_max = 4000 # 最大I/O容量 innodb_read_io_threads = 8 # 读线程数 innodb_write_io_threads = 8 # 写线程数 # 其他配置 innodb_file_per_table = 1 # 每表独立文件 innodb_open_files = 4000 # 打开文件数 innodb_thread_concurrency = 0 # 线程并发数（0为不限制） innodb_lock_wait_timeout = 50 # 锁等待超时时间（秒） innodb_deadlock_detect = ON # 死锁检测 innodb_purge_threads = 4 # 清理线程数 innodb_page_cleaners = 4 # 页清理线程数 # ==================== 查询缓存 ==================== query_cache_size = 0 # 查询缓存大小（MySQL 8.0已移除） query_cache_type = 0 # 查询缓存类型 # ==================== 临时表配置 ==================== tmp_table_size = 256M # 临时表大小 max_heap_table_size = 256M # 内存表大小 # ==================== 排序和分组配置 ==================== sort_buffer_size = 4M # 排序缓冲区大小 read_buffer_size = 2M # 读缓冲区大小 read_rnd_buffer_size = 8M # 随机读缓冲区大小 join_buffer_size = 4M # 连接缓冲区大小 # ==================== 慢查询配置 ==================== slow_query_log = 1 # 开启慢查询日志 slow_query_log_file = /var/log/mysql/slow.log # 慢查询日志文件 long_query_time = 2 # 慢查询阈值（秒） log_queries_not_using_indexes = 1 # 记录未使用索引的查询 # ==================== 二进制日志配置 ==================== log_bin = /var/log/mysql/mysql-bin # 二进制日志文件 binlog_format = ROW # 二进制日志格式 binlog_row_image = FULL # 行镜像模式 expire_logs_days = 7 # 日志过期天数 max_binlog_size = 100M # 最大二进制日志大小 sync_binlog = 1 # 同步二进制日志 # ==================== 字符集配置 ==================== character-set-server = utf8mb4 # 服务器字符集 collation-server = utf8mb4_unicode_ci # 服务器排序规则 # ==================== 其他配置 ==================== skip_name_resolve = 1 # 跳过域名解析 lower_case_table_names = 1 # 表名小写 max_allowed_packet = 64M # 最大数据包大小 table_open_cache = 4000 # 表缓存大小 table_definition_cache = 2000 # 表定义缓存 thread_cache_size = 100 # 线程缓存大小 ``` ### 5.2 Java客户端JVM调优 ```java /** * MySQL客户端JVM优化配置 */ public class MySQLJVMOptimizer { /** * JVM参数配置 * * 启动参数： * -Xms4G -Xmx4G * -XX:MetaspaceSize=256m -XX:MaxMetaspaceSize=512m * -XX:+UseG1GC * -XX:MaxGCPauseMillis=50 * -XX:G1NewSizePercent=30 * -XX:G1MaxNewSizePercent=50 * -XX:ParallelGCThreads=8 * -XX:ConcGCThreads=2 * -XX:+UseLargePages * -XX:+AlwaysPreTouch * -XX:MaxDirectMemorySize=2G * -XX:+HeapDumpOnOutOfMemoryError * -XX:HeapDumpPath=/var/log/mysql/heapdump.hprof * -Djava.awt.headless=true * -Dfile.encoding=UTF-8 */ /** * 优化连接池配置 */ public static void optimizeConnectionPool(HikariConfig poolConfig) { // 连接池大小配置 int cpuCount = Runtime.getRuntime().availableProcessors(); poolConfig.setMaximumPoolSize(cpuCount * 2 + 1); // 最大连接数 poolConfig.setMinimumIdle(cpuCount); // 最小空闲连接数 poolConfig.setIdleTimeout(600000); // 空闲超时时间（10分钟） poolConfig.setMaxLifetime(1800000); // 连接最大生命周期（30分钟） poolConfig.setConnectionTimeout(30000); // 获取连接超时时间（30秒） // 连接测试配置 poolConfig.setConnectionTestQuery("SELECT 1"); poolConfig.setValidationTimeout(5000); // 泄漏检测配置 poolConfig.setLeakDetectionThreshold(60000); // 连接泄漏检测阈值（1分钟） // 性能优化配置 poolConfig.addDataSourceProperty("cachePrepStmts", "true"); // 缓存PreparedStatement poolConfig.addDataSourceProperty("prepStmtCacheSize", "250"); // 缓存大小 poolConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); // 缓存SQL长度限制 poolConfig.addDataSourceProperty("useServerPrepStmts", "true"); // 使用服务端PreparedStatement poolConfig.addDataSourceProperty("useLocalSessionState", "true"); poolConfig.addDataSourceProperty("rewriteBatchedStatements", "true"); // 批量语句重写 poolConfig.addDataSourceProperty("cacheResultSetMetadata", "true"); poolConfig.addDataSourceProperty("cacheServerConfiguration", "true"); poolConfig.addDataSourceProperty("elideSetAutoCommits", "true"); poolConfig.addDataSourceProperty("maintainTimeStats", "false"); // 网络优化配置 poolConfig.addDataSourceProperty("socketTimeout", "30000"); // Socket超时时间 poolConfig.addDataSourceProperty("connectTimeout", "10000"); // 连接超时时间 // 时区配置 poolConfig.addDataSourceProperty("serverTimezone", "Asia/Shanghai"); // 字符集配置 poolConfig.addDataSourceProperty("characterEncoding", "utf8mb4"); } /** * 优化PreparedStatement缓存 */ public static void optimizePreparedStatementCache(Connection conn) throws SQLException { // 设置PreparedStatement缓存大小 try (Statement stmt = conn.createStatement()) { stmt.execute("SET SESSION statement_cache_size = 250"); } } /** * 优化批量操作 */ public static void optimizeBatchOperations(Connection conn, int batchSize) throws SQLException { // 设置批量操作大小 try (Statement stmt = conn.createStatement()) { stmt.execute("SET SESSION max_allowed_packet = " + (64 * 1024 * 1024)); stmt.execute("SET SESSION bulk_insert_buffer_size = " + (256 * 1024 * 1024)); } // 设置rewriteBatchedStatements if (conn instanceof HikariProxyConnection) { // HikariCP连接的批量优化 } } /** * 优化事务配置 */ public static void optimizeTransaction(Connection conn) throws SQLException { // 设置事务隔离级别 conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // 设置自动提交 conn.setAutoCommit(false); } /** * 优化查询性能 */ public static void optimizeQueryPerformance(Connection conn) throws SQLException { // 启用查询缓存（MySQL 8.0已移除） // conn.createStatement().execute("SET SESSION query_cache_type = ON"); // 设置查询超时 conn.setNetworkTimeout(null, 30000); // 设置只读事务（适用于只读查询） if (!conn.getAutoCommit()) { conn.setReadOnly(true); } } /** * 监控连接池状态 */ public static Map monitorPoolStatus(HikariDataSource dataSource) { Map status = new HashMap<>(); HikariPoolMXBean poolProxy = dataSource.getHikariPoolMXBean(); status.put("activeConnections", poolProxy.getActiveConnections()); status.put("idleConnections", poolProxy.getIdleConnections()); status.put("totalConnections", poolProxy.getTotalConnections()); status.put("threadsAwaitingConnection", poolProxy.getThreadsAwaitingConnection()); HikariConfigMXBean configProxy = dataSource.getHikariConfigMXBean(); status.put("maximumPoolSize", configProxy.getMaximumPoolSize()); status.put("minimumIdle", configProxy.getMinimumIdle()); return status; } /** * 性能监控工具 */ public static class PerformanceMonitor { private final Map queryMetricsMap = new ConcurrentHashMap<>(); /** * 记录查询性能 */ public void recordQuery(String sql, long executionTime) { QueryMetrics metrics = queryMetricsMap.computeIfAbsent( normalizeSQL(sql), QueryMetrics::new); metrics.record(executionTime); } /** * 获取查询性能统计 */ public Map getQueryMetrics() { return new HashMap<>(queryMetricsMap); } /** * 标准化SQL（去除参数） */ private String normalizeSQL(String sql) { return sql.replaceAll("'[^']*'", "?") .replaceAll("\\d+", "?"); } /** * 查询指标 */ public static class QueryMetrics { private long totalCount = 0; private long totalTime = 0; private long maxTime = 0; private long minTime = Long.MAX_VALUE; public synchronized void record(long executionTime) { totalCount++; totalTime += executionTime; maxTime = Math.max(maxTime, executionTime); minTime = Math.min(minTime, executionTime); } public long getTotalCount() { return totalCount; } public long getAvgTime() { return totalCount > 0 ? totalTime / totalCount : 0; } public long getMaxTime() { return maxTime; } public long getMinTime() { return minTime == Long.MAX_VALUE ? 0 : minTime; } } } } ``` --- ## 6. 生产级数据库工具类实现 ```java /** * 生产级数据库工具类 * 整合设计模式、反射、注解和性能优化 */ public class DBUtils { private static final Logger logger = LoggerFactory.getLogger(DBUtils.class); // 单例连接池 private static volatile DBConnectionPool connectionPool; // 性能监控器 private static MySQLJVMOptimizer.PerformanceMonitor performanceMonitor; // 序列化器缓存 private static final Map, EntityMapper> entityMapperCache = new ConcurrentHashMap<>(); /** * 初始化数据库连接池 */ public static void init(String url, String username, String password) { if (connectionPool == null) { synchronized (DBUtils.class) { if (connectionPool == null) { connectionPool = DBConnectionPool.getInstance(); connectionPool.init(url, username, password); performanceMonitor = new MySQLJVMOptimizer.PerformanceMonitor(); logger.info("数据库工具类初始化成功"); } } } } /** * 获取数据库连接 */ public static Connection getConnection() throws SQLException { if (connectionPool == null) { throw new IllegalStateException("数据库未初始化"); } return connectionPool.getConnection(); } /** * 关闭连接池 */ public static void close() { if (connectionPool != null) { connectionPool.close(); } } /** * 执行查询 */ public static List queryList(String sql, Class entityClass, Object... params) throws SQLException, ORMException { long startTime = System.currentTimeMillis(); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { // 设置参数 setParameters(pstmt, params); // 执行查询 ResultSet rs = pstmt.executeQuery(); // 映射结果 EntityMapper mapper = getEntityMapper(entityClass); List result = mapper.mapEntityList(rs); // 记录性能 long executionTime = System.currentTimeMillis() - startTime; performanceMonitor.recordQuery(sql, executionTime); return result; } } /** * 查询单个对象 */ public static T queryOne(String sql, Class entityClass, Object... params) throws SQLException, ORMException { List list = queryList(sql, entityClass, params); return list.isEmpty() ? null : list.get(0); } /** * 执行更新 */ public static int update(String sql, Object... params) throws SQLException { long startTime = System.currentTimeMillis(); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { // 设置参数 setParameters(pstmt, params); // 执行更新 int rows = pstmt.executeUpdate(); // 记录性能 long executionTime = System.currentTimeMillis() - startTime; performanceMonitor.recordQuery(sql, executionTime); return rows; } } /** * 执行插入并返回生成的主键 */ public static Long insertAndReturnKey(String sql, Object... params) throws SQLException { long startTime = System.currentTimeMillis(); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { // 设置参数 setParameters(pstmt, params); // 执行插入 int rows = pstmt.executeUpdate(); if (rows > 0) { // 获取生成的主键 ResultSet rs = pstmt.getGeneratedKeys(); if (rs.next()) { return rs.getLong(1); } } // 记录性能 long executionTime = System.currentTimeMillis() - startTime; performanceMonitor.recordQuery(sql, executionTime); return null; } } /** * 执行批量操作 */ public static int[] batchUpdate(String sql, List paramsList) throws SQLException { long startTime = System.currentTimeMillis(); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { // 禁用自动提交 conn.setAutoCommit(false); try { // 添加批量 for (Object[] params : paramsList) { setParameters(pstmt, params); pstmt.addBatch(); } // 执行批量 int[] results = pstmt.executeBatch(); // 提交事务 conn.commit(); // 记录性能 long executionTime = System.currentTimeMillis() - startTime; performanceMonitor.recordQuery(sql, executionTime); return results; } catch (SQLException e) { // 回滚事务 conn.rollback(); throw e; } finally { conn.setAutoCommit(true); } } } /** * 保存实体（INSERT） */ public static Long save(T entity) throws SQLException, ORMException { @SuppressWarnings("unchecked") EntityMapper mapper = (EntityMapper) getEntityMapper(entity.getClass()); String sql = mapper.buildInsertSQL(entity); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { mapper.setInsertParameters(pstmt, entity); int rows = pstmt.executeUpdate(); if (rows > 0) { ResultSet rs = pstmt.getGeneratedKeys(); if (rs.next()) { Long generatedKey = rs.getLong(1); // 设置生成的主键 if (generatedKey != null && generatedKey > 0) { mapper.primaryKeyMapping.setValue(entity, generatedKey); } return generatedKey; } } return null; } catch (IllegalAccessException e) { throw new ORMException("设置主键失败", e); } } /** * 更新实体（UPDATE） */ public static int update(T entity) throws SQLException, ORMException { @SuppressWarnings("unchecked") EntityMapper mapper = (EntityMapper) getEntityMapper(entity.getClass()); String sql = mapper.buildUpdateSQL(entity); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { mapper.setUpdateParameters(pstmt, entity); return pstmt.executeUpdate(); } } /** * 删除实体（DELETE） */ public static int delete(T entity) throws SQLException, ORMException { @SuppressWarnings("unchecked") EntityMapper mapper = (EntityMapper) getEntityMapper(entity.getClass()); Object id = mapper.getPrimaryKeyValue(entity); String sql = mapper.buildDeleteSQL(id); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { mapper.setDeleteParameters(pstmt, id); return pstmt.executeUpdate(); } catch (IllegalAccessException e) { throw new ORMException("获取主键失败", e); } } /** * 根据ID查询 */ public static T findById(Class entityClass, Object id) throws SQLException, ORMException { EntityMapper mapper = getEntityMapper(entityClass); String sql = mapper.buildSelectByIdSQL(); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setObject(1, id); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return mapper.mapEntity(rs); } return null; } } /** * 查询全部 */ public static List findAll(Class entityClass) throws SQLException, ORMException { EntityMapper mapper = getEntityMapper(entityClass); String sql = mapper.buildSelectAllSQL(); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { ResultSet rs = pstmt.executeQuery(); return mapper.mapEntityList(rs); } } /** * 统计数量 */ public static long count(Class entityClass, String whereClause, Object... params) throws SQLException, ORMException { EntityMapper mapper = getEntityMapper(entityClass); String sql = mapper.buildCountSQL(whereClause); try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { if (params != null) { for (int i = 0; i < params.length; i++) { pstmt.setObject(i + 1, params[i]); } } ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return rs.getLong(1); } return 0; } } /** * 执行事务 */ public static T executeInTransaction(TransactionCallback callback) throws Exception { Connection conn = null; try { conn = getConnection(); conn.setAutoCommit(false); T result = callback.doInTransaction(conn); conn.commit(); return result; } catch (Exception e) { if (conn != null) { conn.rollback(); } throw e; } finally { if (conn != null) { conn.setAutoCommit(true); conn.close(); } } } /** * 设置PreparedStatement参数 */ private static void setParameters(PreparedStatement pstmt, Object... params) throws SQLException { if (params != null) { for (int i = 0; i < params.length; i++) { pstmt.setObject(i + 1, params[i]); } } } /** * 获取实体映射器 */ @SuppressWarnings("unchecked") private static EntityMapper getEntityMapper(Class entityClass) { return (EntityMapper) entityMapperCache.computeIfAbsent(entityClass, EntityMapper::new); } /** * 事务回调接口 */ @FunctionalInterface public interface TransactionCallback { T doInTransaction(Connection conn) throws Exception; } /** * 获取性能指标 */ public static Map getPerformanceMetrics() { return performanceMonitor.getQueryMetrics(); } /** * 获取连接池状态 */ public static Map getPoolStats() { return connectionPool.getPoolStats(); } } ``` --- ## 7. 数据库连接池优化 ```java /** * 高级连接池优化策略 */ public class AdvancedConnectionPoolOptimizer { /** * 动态调整连接池大小 */ public static class DynamicPoolAdjuster { private final HikariDataSource dataSource; private final ScheduledExecutorService scheduler; public DynamicPoolAdjuster(HikariDataSource dataSource) { this.dataSource = dataSource; this.scheduler = Executors.newScheduledThreadPool(1, r -> { Thread thread = new Thread(r, "PoolAdjuster"); thread.setDaemon(true); return thread; }); } public void start() { // 每5秒检查一次连接池状态 scheduler.scheduleAtFixedRate(this::adjustPoolSize, 5, 5, TimeUnit.SECONDS); } private void adjustPoolSize() { try { HikariPoolMXBean poolProxy = dataSource.getHikariPoolMXBean(); int activeConnections = poolProxy.getActiveConnections(); int idleConnections = poolProxy.getIdleConnections(); int totalConnections = poolProxy.getTotalConnections(); int awaitingConnections = poolProxy.getThreadsAwaitingConnection(); // 如果等待连接的线程过多，增加连接池大小 if (awaitingConnections > 10) { increasePoolSize(10); } // 如果空闲连接过多，减少连接池大小 else if (idleConnections > totalConnections * 0.7) { decreasePoolSize((int) (idleConnections * 0.3)); } } catch (Exception e) { logger.error("动态调整连接池大小失败", e); } } private void increasePoolSize(int delta) { // HikariCP不支持动态调整连接池大小 // 需要重建连接池 logger.info("建议增加连接池大小: +{}", delta); } private void decreasePoolSize(int delta) { // HikariCP会自动回收空闲连接 logger.info("建议减少连接池大小: -{}", delta); } public void stop() { scheduler.shutdown(); } } /** * 连接预热 */ public static class ConnectionWarmer { private final HikariDataSource dataSource; public ConnectionWarmer(HikariDataSource dataSource) { this.dataSource = dataSource; } public void warmUp(int count) { logger.info("开始预热连接池，数量: {}", count); List connections = new ArrayList<>(); try { // 获取指定数量的连接 for (int i = 0; i < count; i++) { Connection conn = dataSource.getConnection(); connections.add(conn); // 执行简单查询验证连接 try (Statement stmt = conn.createStatement()) { stmt.executeQuery("SELECT 1"); } } logger.info("连接池预热完成"); } catch (SQLException e) { logger.error("连接池预热失败", e); } finally { // 关闭所有连接，返回连接池 for (Connection conn : connections) { try { conn.close(); } catch (SQLException e) { logger.error("关闭连接失败", e); } } } } } /** * 连接泄漏检测 */ public static class ConnectionLeakDetector { private final HikariDataSource dataSource; private final Map connectionStacks = new ConcurrentHashMap<>(); public ConnectionLeakDetector(HikariDataSource dataSource) { this.dataSource = dataSource; } public void start() { // 每分钟检查一次连接泄漏 ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1); scheduler.scheduleAtFixedRate(this::detectLeaks, 1, 1, TimeUnit.MINUTES); } private void detectLeaks() { try { HikariPoolMXBean poolProxy = dataSource.getHikariPoolMXBean(); int activeConnections = poolProxy.getActiveConnections(); if (activeConnections > 0) { logger.warn("检测到可能的连接泄漏，活跃连接数: {}", activeConnections); // 记录连接堆栈信息 } } catch (Exception e) { logger.error("检测连接泄漏失败", e); } } } } ``` --- ## 8. 分库分表方案实现 ```java /** * 分库分表方案实现 */ public class ShardingStrategy { /** * 分片策略枚举 */ public enum ShardingAlgorithm { MOD, // 取模分片 HASH, // 哈希分片 RANGE, // 范围分片 CONSISTENT_HASH // 一致性哈希 } /** * 分库分表配置 */ public static class ShardingConfig { private int dbCount; // 数据库数量 private int tableCountPerDb; // 每个数据库的表数量 private ShardingAlgorithm algorithm; private List dbNames; // 数据库名称列表 public ShardingConfig(int dbCount, int tableCountPerDb, ShardingAlgorithm algorithm) { this.dbCount = dbCount; this.tableCountPerDb = tableCountPerDb; this.algorithm = algorithm; this.dbNames = generateDbNames(dbCount); } private List generateDbNames(int count) { List names = new ArrayList<>(); for (int i = 0; i < count; i++) { names.add("sharding_db_" + i); } return names; } public int getDbCount() { return dbCount; } public int getTableCountPerDb() { return tableCountPerDb; } public ShardingAlgorithm getAlgorithm() { return algorithm; } public List getDbNames() { return dbNames; } } /** * 分片路由器 */ public static class ShardingRouter { private final ShardingConfig config; public ShardingRouter(ShardingConfig config) { this.config = config; } /** * 计算分库 */ public String routeDb(String shardingKey) { switch (config.getAlgorithm()) { case MOD: return routeDbByMod(shardingKey); case HASH: return routeDbByHash(shardingKey); case RANGE: return routeDbByRange(shardingKey); case CONSISTENT_HASH: return routeDbByConsistentHash(shardingKey); default: throw new IllegalArgumentException("不支持的分片算法: " + config.getAlgorithm()); } } /** * 计算分表 */ public String routeTable(String shardingKey) { switch (config.getAlgorithm()) { case MOD: return routeTableByMod(shardingKey); case HASH: return routeTableByHash(shardingKey); case RANGE: return routeTableByRange(shardingKey); case CONSISTENT_HASH: return routeTableByConsistentHash(shardingKey); default: throw new IllegalArgumentException("不支持的分片算法: " + config.getAlgorithm()); } } /** * 取模分库 */ private String routeDbByMod(String shardingKey) { int hash = shardingKey.hashCode(); int dbIndex = Math.abs(hash) % config.getDbCount(); return config.getDbNames().get(dbIndex); } /** * 取模分表 */ private String routeTableByMod(String shardingKey) { int hash = shardingKey.hashCode(); int tableIndex = Math.abs(hash) % config.getTableCountPerDb(); return "table_" + tableIndex; } /** * 哈希分库 */ private String routeDbByHash(String shardingKey) { int hash = Math.abs(shardingKey.hashCode()); int dbIndex = hash % config.getDbCount(); return config.getDbNames().get(dbIndex); } /** * 哈希分表 */ private String routeTableByHash(String shardingKey) { int hash = Math.abs(shardingKey.hashCode()); int tableIndex = hash % config.getTableCountPerDb(); return "table_" + tableIndex; } /** * 范围分库 */ private String routeDbByRange(String shardingKey) { try { long value = Long.parseLong(shardingKey); // 假设每个数据库存储1000万条数据 int dbIndex = (int) (value / 10_000_000) % config.getDbCount(); return config.getDbNames().get(dbIndex); } catch (NumberFormatException e) { throw new IllegalArgumentException("范围分片需要数值类型的分片键"); } } /** * 范围分表 */ private String routeTableByRange(String shardingKey) { try { long value = Long.parseLong(shardingKey); // 假设每个表存储100万条数据 int tableIndex = (int) (value / 1_000_000) % config.getTableCountPerDb(); return "table_" + tableIndex; } catch (NumberFormatException e) { throw new IllegalArgumentException("范围分片需要数值类型的分片键"); } } /** * 一致性哈希分库 */ private String routeDbByConsistentHash(String shardingKey) { ConsistentHash hashRing = new ConsistentHash<>(); for (String dbName : config.getDbNames()) { hashRing.addNode(dbName); } return hashRing.getNode(shardingKey); } /** * 一致性哈希分表 */ private String routeTableByConsistentHash(String shardingKey) { List tableNames = new ArrayList<>(); for (int i = 0; i < config.getTableCountPerDb(); i++) { tableNames.add("table_" + i); } ConsistentHash hashRing = new ConsistentHash<>(); for (String tableName : tableNames) { hashRing.addNode(tableName); } return hashRing.getNode(shardingKey); } } /** * 一致性哈希实现 */ public static class ConsistentHash { private final TreeMap ring = new TreeMap<>(); private final int virtualNodes = 150; public void addNode(T node) { for (int i = 0; i < virtualNodes; i++) { long hash = hash(node.toString() + ":" + i); ring.put(hash, node); } } public void removeNode(T node) { for (int i = 0; i < virtualNodes; i++) { long hash = hash(node.toString() + ":" + i); ring.remove(hash); } } public T getNode(String key) { if (ring.isEmpty()) { throw new IllegalStateException("哈希环为空"); } long hash = hash(key); Map.Entry entry = ring.ceilingEntry(hash); if (entry == null) { entry = ring.firstEntry(); } return entry.getValue(); } private long hash(String key) { // 使用FNV哈希算法 final long FNV_64_INIT = 0xcbf29ce484222325L; final long FNV_64_PRIME = 0x100000001b3L; long hash = FNV_64_INIT; for (byte b : key.getBytes()) { hash ^= b; hash *= FNV_64_PRIME; } return hash; } } } ``` --- ## 9. 读写分离与主从同步 ```java /** * 读写分离实现 */ public class ReadWriteSeparation { /** * 数据源类型 */ public enum DataSourceType { MASTER, // 主库（写） SLAVE // 从库（读） } /** * 读写分离数据源 */ public static class ReadWriteDataSource { private final HikariDataSource masterDataSource; private final List slaveDataSources; private final AtomicInteger slaveIndex = new AtomicInteger(0); public ReadWriteDataSource(HikariDataSource masterDataSource, List slaveDataSources) { this.masterDataSource = masterDataSource; this.slaveDataSources = slaveDataSources; } /** * 获取连接（根据数据源类型） */ public Connection getConnection(DataSourceType type) throws SQLException { switch (type) { case MASTER: return masterDataSource.getConnection(); case SLAVE: return getSlaveConnection(); default: throw new IllegalArgumentException("不支持的数据源类型: " + type); } } /** * 获取从库连接（负载均衡） */ private Connection getSlaveConnection() throws SQLException { if (slaveDataSources.isEmpty()) { return masterDataSource.getConnection(); } // 轮询策略 int index = slaveIndex.getAndIncrement() % slaveDataSources.size(); return slaveDataSources.get(index).getConnection(); } /** * 关闭所有数据源 */ public void closeAll() { masterDataSource.close(); for (HikariDataSource slaveDataSource : slaveDataSources) { slaveDataSource.close(); } } } /** * 读写分离拦截器 */ public static class ReadWriteInterceptor implements InvocationHandler { private final Object target; private final ReadWriteDataSource dataSource; public ReadWriteInterceptor(Object target, ReadWriteDataSource dataSource) { this.target = target; this.dataSource = dataSource; } @Override public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { // 判断方法类型 DataSourceType dataSourceType = determineDataSourceType(method); // 获取对应数据源的连接 try (Connection conn = dataSource.getConnection(dataSourceType)) { // 设置连接到上下文 DataSourceContext.setConnection(conn); // 执行方法 return method.invoke(target, args); } finally { // 清理上下文 DataSourceContext.clearConnection(); } } private DataSourceType determineDataSourceType(Method method) { String methodName = method.getName().toLowerCase(); // 写操作 if (methodName.startsWith("insert") || methodName.startsWith("update") || methodName.startsWith("delete") || methodName.startsWith("save") || methodName.startsWith("create") || methodName.startsWith("modify")) { return DataSourceType.MASTER; } // 读操作 if (methodName.startsWith("select") || methodName.startsWith("query") || methodName.startsWith("get") || methodName.startsWith("find") || methodName.startsWith("list") || methodName.startsWith("search")) { return DataSourceType.SLAVE; } // 默认使用主库 return DataSourceType.MASTER; } } /** * 数据源上下文 */ public static class DataSourceContext { private static final ThreadLocal connectionHolder = new ThreadLocal<>(); public static void setConnection(Connection conn) { connectionHolder.set(conn); } public static Connection getConnection() { return connectionHolder.get(); } public static void clearConnection() { connectionHolder.remove(); } } /** * 主从延迟检测 */ public static class MasterSlaveLagDetector { private final ReadWriteDataSource dataSource; public MasterSlaveLagDetector(ReadWriteDataSource dataSource) { this.dataSource = dataSource; } /** * 检测主从延迟 */ public long detectLag() throws SQLException { try (Connection masterConn = dataSource.getConnection(DataSourceType.MASTER); Connection slaveConn = dataSource.getConnection(DataSourceType.SLAVE)) { // 获取主库当前binlog位置 long masterPosition = getBinlogPosition(masterConn); // 获取从库当前执行位置 long slavePosition = getSlavePosition(slaveConn); // 计算延迟 return masterPosition - slavePosition; } } private long getBinlogPosition(Connection conn) throws SQLException { try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SHOW MASTER STATUS")) { if (rs.next()) { return rs.getLong("Position"); } return 0; } } private long getSlavePosition(Connection conn) throws SQLException { try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SHOW SLAVE STATUS")) { if (rs.next()) { return rs.getLong("Exec_Master_Log_Pos"); } return 0; } } } } ``` --- ## 10. 完整使用示例 ```java /** * MySQL完整使用示例 */ public class MySQLCompleteExample { private static final Logger logger = LoggerFactory.getLogger(MySQLCompleteExample.class); public static void main(String[] args) { // 1. 初始化数据库连接 DBUtils.init("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Shanghai", "root", "password"); // 2. 基础操作示例 basicOperations(); // 3. ORM操作示例 ormOperations(); // 4. DAO注解示例 daoAnnotationExample(); // 5. 批量操作示例 batchOperations(); // 6. 事务操作示例 transactionExample(); // 7. 分库分表示例 shardingExample(); // 8. 读写分离示例 readWriteSeparationExample(); // 9. 关闭连接 DBUtils.close(); } /** * 基础操作示例 */ private static void basicOperations() { System.out.println("=== 基础操作示例 ==="); try { // 创建表 String createTableSQL = """ CREATE TABLE IF NOT EXISTS user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100), age INT, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """; DBUtils.update(createTableSQL); // 插入数据 String insertSQL = "INSERT INTO user (username, email, age) VALUES (?, ?, ?)"; Long id = DBUtils.insertAndReturnKey(insertSQL, "张三", "zhangsan@example.com", 25); System.out.println("插入用户ID: " + id); // 查询数据 String selectSQL = "SELECT * FROM user WHERE id = ?"; User user = DBUtils.queryOne(selectSQL, User.class, id); System.out.println("查询用户: " + user); // 更新数据 String updateSQL = "UPDATE user SET age = ? WHERE id = ?"; int updateRows = DBUtils.update(updateSQL, 26, id); System.out.println("更新行数: " + updateRows); // 删除数据 String deleteSQL = "DELETE FROM user WHERE id = ?"; int deleteRows = DBUtils.update(deleteSQL, id); System.out.println("删除行数: " + deleteRows); } catch (Exception e) { logger.error("基础操作失败", e); } } /** * ORM操作示例 */ private static void ormOperations() { System.out.println("\n=== ORM操作示例 ==="); try { // 创建用户对象 User user = new User(); user.setUsername("李四"); user.setEmail("lisi@example.com"); user.setAge(30); // 保存用户 Long id = DBUtils.save(user); System.out.println("保存用户ID: " + id); // 根据ID查询 User foundUser = DBUtils.findById(User.class, id); System.out.println("查询用户: " + foundUser); // 更新用户 foundUser.setAge(31); DBUtils.update(foundUser); System.out.println("更新用户年龄"); // 查询全部用户 List users = DBUtils.findAll(User.class); System.out.println("全部用户: " + users); // 删除用户 DBUtils.delete(foundUser); System.out.println("删除用户"); } catch (Exception e) { logger.error("ORM操作失败", e); } } /** * DAO注解示例 */ private static void daoAnnotationExample() { System.out.println("\n=== DAO注解示例 ==="); try { // 创建DAO代理 UserDao userDao = DAOProxy.createProxy(UserDao.class, DBConnectionPool.getInstance()); // 插入用户 User user = new User(); user.setUsername("王五"); user.setEmail("wangwu@example.com"); user.setAge(28); Long id = userDao.insert(user); System.out.println("插入用户ID: " + id); // 根据ID查询 User foundUser = userDao.selectById(id); System.out.println("查询用户: " + foundUser); // 更新用户 foundUser.setAge(29); userDao.update(foundUser); System.out.println("更新用户"); // 查询全部用户 List users = userDao.selectAll(); System.out.println("全部用户数量: " + users.size()); // 删除用户 userDao.delete(foundUser); System.out.println("删除用户"); } catch (Exception e) { logger.error("DAO注解操作失败", e); } } /** * 批量操作示例 */ private static void batchOperations() { System.out.println("\n=== 批量操作示例 ==="); try { // 准备批量数据 List paramsList = new ArrayList<>(); for (int i = 1; i <= 100; i++) { paramsList.add(new Object[]{"用户" + i, "user" + i + "@example.com", 20 + i % 10}); } // 批量插入 String insertSQL = "INSERT INTO user (username, email, age) VALUES (?, ?, ?)"; int[] results = DBUtils.batchUpdate(insertSQL, paramsList); System.out.println("批量插入结果: " + Arrays.toString(results)); } catch (Exception e) { logger.error("批量操作失败", e); } } /** * 事务操作示例 */ private static void transactionExample() { System.out.println("\n=== 事务操作示例 ==="); try { // 执行事务 String result = DBUtils.executeInTransaction(conn -> { // 保存用户 User user = new User(); user.setUsername("赵六"); user.setEmail("zhaoliu@example.com"); user.setAge(35); Long id = DBUtils.save(user); // 保存用户订单（模拟） // Order order = new Order(); // order.setUserId(id); // DBUtils.save(order); return "事务执行成功，用户ID: " + id; }); System.out.println(result); } catch (Exception e) { logger.error("事务操作失败", e); } } /** * 分库分表示例 */ private static void shardingExample() { System.out.println("\n=== 分库分表示例 ==="); try { // 配置分片策略 ShardingStrategy.ShardingConfig config = new ShardingStrategy.ShardingConfig( 4, // 4个数据库 8, // 每个数据库8个表 ShardingStrategy.ShardingAlgorithm.MOD ); ShardingStrategy.ShardingRouter router = new ShardingStrategy.ShardingRouter(config); // 计算分库和分表 String userId = "1001"; String dbName = router.routeDb(userId); String tableName = router.routeTable(userId); System.out.println("用户ID: " + userId); System.out.println("路由数据库: " + dbName); System.out.println("路由表: " + tableName); } catch (Exception e) { logger.error("分库分表操作失败", e); } } /** * 读写分离示例 */ private static void readWriteSeparationExample() { System.out.println("\n=== 读写分离示例 ==="); try { // 配置主从数据源 HikariConfig masterConfig = new HikariConfig(); masterConfig.setJdbcUrl("jdbc:mysql://master-host:3306/test"); masterConfig.setUsername("root"); masterConfig.setPassword("password"); HikariDataSource masterDataSource = new HikariDataSource(masterConfig); HikariConfig slaveConfig = new HikariConfig(); slaveConfig.setJdbcUrl("jdbc:mysql://slave-host:3306/test"); slaveConfig.setUsername("root"); slaveConfig.setPassword("password"); HikariDataSource slaveDataSource = new HikariDataSource(slaveConfig); List slaveDataSources = new ArrayList<>(); slaveDataSources.add(slaveDataSource); ReadWriteSeparation.ReadWriteDataSource dataSource = new ReadWriteSeparation.ReadWriteDataSource(masterDataSource, slaveDataSources); // 使用读写分离 Connection masterConn = dataSource.getConnection(ReadWriteSeparation.DataSourceType.MASTER); Connection slaveConn = dataSource.getConnection(ReadWriteSeparation.DataSourceType.SLAVE); System.out.println("获取主库连接: " + (masterConn != null)); System.out.println("获取从库连接: " + (slaveConn != null)); // 关闭连接 masterConn.close(); slaveConn.close(); } catch (Exception e) { logger.error("读写分离操作失败", e); } } /** * 用户实体类 */ @ORMAnnotations.Table(name = "user") @Data public static class User { @ORMAnnotations.Id(strategy = ORMAnnotations.GenerationType.IDENTITY) @ORMAnnotations.Column(name = "id", primaryKey = true, autoIncrement = true) private Long id; @ORMAnnotations.Column(name = "username", length = 50, nullable = false) private String username; @ORMAnnotations.Column(name = "email", length = 100) private String email; @ORMAnnotations.Column(name = "age") private Integer age; @ORMAnnotations.Column(name = "create_time") private Date createTime; } /** * 用户DAO接口 */ public interface UserDao { @DAOAnnotations.Insert(returnGeneratedKey = true) Long insert(User user); @DAOAnnotations.Update int update(User user); @DAOAnnotations.Delete int delete(User user); @DAOAnnotations.Select User selectById(Long id); @DAOAnnotations.Select(sql = "SELECT * FROM user WHERE username = ?") User selectByUsername(String username); @DAOAnnotations.Select List selectAll(); @DAOAnnotations.Select(sql = "SELECT COUNT(*) FROM user") long count(); } } ``` --- ## 11. 总结 本文详细介绍了MySQL的深度实战应用，涵盖了以下核心内容： 1. **架构原理**：MySQL分层架构、InnoDB存储引擎、MVCC、缓冲池、B+树索引 2. **设计模式**：单例模式（连接池）、工厂模式（SQL构建器）、建造者模式（复杂查询） 3. **反射机制**：实体类映射、动态SQL生成、参数设置、结果集映射 4. **注解应用**：@Table、@Column、@Id、@Insert、@Update、@Delete、@Select、@Transaction 5. **性能优化**：连接池优化、JVM调优、PreparedStatement缓存、批量操作优化 6. **高级特性**：分库分表、读写分离、主从同步、事务管理、性能监控 通过这些技术点的整合应用，可以构建一个高性能、高可用的MySQL客户端解决方案，满足生产环境的各种复杂场景需求。
评论 0

发表评论 取消回复

Shift+Enter 换行  ·  Enter 发送
还没有评论，来发表第一条吧