<dependencies>
    <!-- Spring JDBC 基礎(chǔ) -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!-- KingbaseES 驅(qū)動（版本鎖定，跟服務器一致） -->
    <dependency>
        <groupId>com.kingbase8</groupId>
        <artifactId>kingbase8</artifactId>
        <version>8.6.0</version>
    </dependency>
    <!-- 連接池：三選一（推薦 Hikari 或 Druid） -->
    <!-- Druid（生產(chǎn)監(jiān)控友好） -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.2.18</version>
    </dependency>
    <!-- 或 Apache DBCP2（老系統(tǒng)遷移） -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
    </dependency>
    <!-- 或 HikariCP（高性能 / Spring Boot 默認） -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
</dependencies>

kingbase:
  driver-class-name: com.kingbase8.Driver
  url: jdbc:kingbase8://127.0.0.1:54321/TEST
  username: SYSTEM
  password: SYSTEM

@Configuration
public class DbcpDataSourceConfig {
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.dbcp")
    public DataSource dbcpDataSource() {
        return new BasicDataSource();
    }
}

spring:
  datasource:
    dbcp:
      driver-class-name: com.kingbase8.Driver
      url: jdbc:kingbase8://127.0.0.1:54321/TEST
      username: SYSTEM
      password: MANAGER
      initial-size: 5
      max-total: 50
      min-idle: 5
      max-idle: 20
      validation-query: SELECT 'x'
      test-on-borrow: true
      test-while-idle: true
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 1800000

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public List<User> findAllUsers() {
        return jdbcTemplate.query(
            "SELECT id, name, email FROM users",
            (rs, i) -> {
                User u = new User();
                u.setId(rs.getLong("id"));
                u.setName(rs.getString("name"));
                u.setEmail(rs.getString("email"));
                return u;
            }
        );
    }
}

@Bean
public DataSource c3p0DataSource() throws PropertyVetoException {
    ComboPooledDataSource ds = new ComboPooledDataSource();
    ds.setDriverClass("com.kingbase8.Driver");
    ds.setJdbcUrl("jdbc:kingbase8://127.0.0.1:54321/TEST");
    ds.setUser("SYSTEM");
    ds.setPassword("SYSTEM");
    ds.setInitialPoolSize(5);
    ds.setMinPoolSize(5);
    ds.setMaxPoolSize(50);
    ds.setAcquireIncrement(3);
    ds.setIdleConnectionTestPeriod(10);
    ds.setPreferredTestQuery("SELECT 1");
    ds.setMaxIdleTime(1800);
    return ds;
}

spring:
  datasource:
    druid:
      driver-class-name: com.kingbase8.Driver
      url: jdbc:kingbase8://127.0.0.1:54321/TEST
      username: SYSTEM
      password: MANAGER
      initial-size: 5
      max-active: 50
      min-idle: 5
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 1800000
      validation-query: SELECT 'x'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      filters: stat,wall,log4j2
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        reset-enable: false
        login-username: admin
        login-password: admin123

@Configuration
public class DruidConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.druid")
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
    @Bean
    public ServletRegistrationBean<StatViewServlet> druidStatViewServlet() {
        ServletRegistrationBean<StatViewServlet> bean =
            new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        bean.addInitParameter("loginUsername", "admin");
        bean.addInitParameter("loginPassword", "admin123");
        bean.addInitParameter("resetEnable", "false");
        return bean;
    }
}

spring:
  datasource:
    hikari:
      driver-class-name: com.kingbase8.Driver
      jdbc-url: jdbc:kingbase8://127.0.0.1:54321/TEST
      username: SYSTEM
      password: SYSTEM
      minimum-idle: 5
      maximum-pool-size: 50
      connection-timeout: 10000
      idle-timeout: 600000
      max-lifetime: 1800000
      validation-timeout: 5000
      connection-test-query: SELECT 1
      pool-name: KingbaseHikariCP
      auto-commit: true
      leak-detection-threshold: 60000

@Configuration
public class HikariTunedConfig {
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariDataSource hikariDataSource() {
        HikariDataSource ds = new HikariDataSource();
        ds.addDataSourceProperty("cachePrepStmts", "true");
        ds.addDataSourceProperty("prepStmtCacheSize", "250");
        ds.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        ds.addDataSourceProperty("useServerPrepStmts", "true");
        return ds;
    }
}

@Component
public class ConnectionPoolMonitor {
    private static final Logger logger = LoggerFactory.getLogger(ConnectionPoolMonitor.class);
    @Autowired
    private DataSource dataSource;
    @Scheduled(fixedRate = 60000) // 每分鐘檢查一次
    public void monitorConnectionPool() {
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikariDS = (HikariDataSource) dataSource;
            HikariPoolMXBean poolMXBean = hikariDS.getHikariPoolMXBean();
            int activeConnections = poolMXBean.getActiveConnections();
            int idleConnections = poolMXBean.getIdleConnections();
            int totalConnections = poolMXBean.getTotalConnections();
            int threadsAwaitingConnection = poolMXBean.getThreadsAwaitingConnection();
            logger.info("連接池狀態(tài) - 活躍:{}, 空閑:{}, 總計:{}, 等待:{}", 
                activeConnections, idleConnections, totalConnections, threadsAwaitingConnection);
            // 告警邏輯
            if (activeConnections > totalConnections * 0.8) {
                logger.warn("連接池使用率過高！活躍連接:{}, 總連接:{}", activeConnections, totalConnections);
                // 發(fā)送告警通知...
            }
            if (threadsAwaitingConnection > 0) {
                logger.error("有線程在等待連接！等待數(shù)量:{}", threadsAwaitingConnection);
                // 發(fā)送緊急告警...
            }
        }
    }
}

盲目"優(yōu)化"的配置
spring:
  datasource:
    hikari:
      maximum-pool-size: 200  # 太大了，浪費資源
      connection-timeout: 1000  # 太短了，正常查詢都超時
      idle-timeout: 60000  # 太短了，連接頻繁創(chuàng)建銷毀
      max-lifetime: 300000  # 太短了，增加了不必要的開銷

@Component
public class ConnectionPoolTuning {
    public void performanceTuning() {
        // 1. 先測試默認配置的性能基線
        // 2. 分析業(yè)務特點：并發(fā)量、查詢復雜度、事務長度
        // 3. 逐個參數(shù)調(diào)整，每次只改一個參數(shù)
        // 4. 壓測驗證效果
        // 5. 記錄最優(yōu)配置
        System.out.println("調(diào)優(yōu)原則：");
        System.out.println("1. 連接數(shù) = CPU核心數(shù) * 2 + 磁盤數(shù)（起始值）");
        System.out.println("2. 超時時間要大于最長查詢時間");
        System.out.println("3. 空閑超時要平衡資源利用和連接創(chuàng)建開銷");
        System.out.println("4. 生命周期要小于數(shù)據(jù)庫連接超時");
    }
}