<dependencies>
    <!-- Spring JDBC 基礎(chǔ) -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!-- KingbaseES 驅(qū)動(dòng)（版本鎖定，跟服務(wù)器一致） -->
    <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 默認(rèn)） -->
    <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) - 活躍:{}, 空閑:{}, 總計(jì):{}, 等待:{}", 
                activeConnections, idleConnections, totalConnections, threadsAwaitingConnection);
            // 告警邏輯
            if (activeConnections > totalConnections * 0.8) {
                logger.warn("連接池使用率過(guò)高！活躍連接:{}, 總連接:{}", activeConnections, totalConnections);
                // 發(fā)送告警通知...
            }
            if (threadsAwaitingConnection > 0) {
                logger.error("有線(xiàn)程在等待連接！等待數(shù)量:{}", threadsAwaitingConnection);
                // 發(fā)送緊急告警...
            }
        }
    }
}

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

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