Mastering Spring Boot with KingbaseES: Which Connection Pool Wins?

1. Introduction

In enterprise project development we often focus on both progress and performance: pages must respond quickly and interfaces must be stable, yet the hidden but critical part of database connection management is often ignored. Without a pre‑established pool, high‑traffic periods can cause the application to stall like a train with only one platform.

KingBaseES, a domestic database, is increasingly used in high‑security domains such as healthcare and transportation. After migrating from Oracle or PostgreSQL, teams must choose an appropriate connection pool. DBCP is reliable, C3P0 is mature, Druid offers rich monitoring, and HikariCP is ultra‑lightweight.

This article combines real Spring Boot scenarios to share configuration ideas, characteristics and optimization tips for the four major pools, helping you avoid common pitfalls.

2. What Is the Role of a Database Connection Pool?

In short, a connection pool is like keeping a bucket of water at home instead of fetching it from a well each time you are thirsty. Creating and releasing a database connection incurs handshake, authentication and resource allocation overhead. If each HTTP request opens and closes a connection, the database suffers heavy load.

The pool provides:

Pre‑creating a set of usable connections during low‑traffic periods.

Dynamically increasing or decreasing the number of connections during peaks.

Borrowing a connection from the pool for each request and returning it afterward.

Monitoring the pool and recreating failed connections.

This stabilizes system performance and provides statistics for later optimization.

3. Spring Boot + KingbaseES Environment Preparation

Three‑step model:

Add dependencies so the project knows how to connect to the database.

Configure the address, port, database name, credentials and driver class.

Verify connectivity at startup or with a minimal SQL health check.

3.1 Add Dependencies (pom.xml)

<dependencies>
    <!-- Spring JDBC base -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <!-- KingbaseES driver -->
    <dependency>
        <groupId>com.kingbase8</groupId>
        <artifactId>kingbase8</artifactId>
        <version>8.6.0</version>
    </dependency>

    <!-- Choose one pool (recommended Hikari or Druid) -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.2.18</version>
    </dependency>
    <!-- or Apache DBCP2 -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
    </dependency>
    <!-- or HikariCP (Spring Boot default) -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
</dependencies>

Tip: If you use Spring Boot Starter, you usually do not need to add HikariCP explicitly because it is embedded.

3.2 Basic Connection Info (application.yml)

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

Suggestion:

Never hard‑code passwords in production; use environment variables or externalized config (e.g., ENC, K8s Secret).

If you have multiple data sources, manage them with prefixes instead of mixing them under spring.datasource.

4. Four Main Connection Pools in Practice

DBCP: Old‑reliable, not flashy.

C3P0: Feature‑rich, fine‑grained, suitable for legacy complex scenarios.

Druid: Built‑in monitoring and SQL firewall, ops‑friendly.

HikariCP: Fast, lightweight, modern, high‑concurrency friendly.

4.1 DBCP (migration‑friendly / traditional projects)

Key parameters: initial-size, max-total, validation-query.

Spring Boot configuration:

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

application.yml:

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

Example usage:

@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;
            });
    }
}

4.2 C3P0

Core parameters:

acquireIncrement: How many connections to add when needed.

idleConnectionTestPeriod: Health‑check interval (seconds).

preferredTestQuery: Lightweight validation query.

Spring Boot bean:

@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;
}

4.3 Druid (production‑recommended + visual)

Advantages: SQL monitoring, slow‑SQL detection, Wall anti‑injection, one‑stop web console.

application.yml:

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

Spring Boot bean:

@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;
    }
}

Note: Too‑frequent inspections (e.g., <5 s) waste resources; keep validation queries lightweight.

4.4 HikariCP (Spring Boot default / high‑performance choice)

Features: fast startup, low latency, few parameters, good fault tolerance.

application.yml:

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

Spring Boot bean:

@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;
    }
}

5. Issues Encountered

Missing monitoring, late problem discovery

The system gradually slowed down, users complained, but the team only noticed after management intervened.

Root cause: No proper monitoring or alerting for pool status, slow SQL, connection wait time.

Solution: Build a complete monitoring system.

@Component
public class ConnectionPoolMonitor {
    private static final Logger logger = LoggerFactory.getLogger(ConnectionPoolMonitor.class);
    @Autowired
    private DataSource dataSource;

    @Scheduled(fixedRate = 60000) // every minute
    public void monitorConnectionPool() {
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikariDS = (HikariDataSource) dataSource;
            HikariPoolMXBean poolMXBean = hikariDS.getHikariPoolMXBean();

            int active = poolMXBean.getActiveConnections();
            int idle = poolMXBean.getIdleConnections();
            int total = poolMXBean.getTotalConnections();
            int waiting = poolMXBean.getThreadsAwaitingConnection();

            logger.info("Pool status - active:{}, idle:{}, total:{}, waiting:{}", active, idle, total, waiting);

            if (active > total * 0.8) {
                logger.warn("High pool usage! active:{}, total:{}", active, total);
                // send alert
            }
            if (waiting > 0) {
                logger.error("Threads awaiting connection: {}", waiting);
                // send urgent alert
            }
        }
    }
}

Lessons learned:

Monitoring is essential, not optional.

Set threshold alerts for key metrics.

Druid’s UI is useful but cannot replace programmatic monitoring and alerts.

Blind tuning degrades performance

Following “HikariCP is the fastest” advice, the team set all parameters to “optimal” values, which actually reduced performance.

Root cause: Generic best‑practice settings may not suit a specific workload. Over‑sizing the pool wastes resources; too short timeouts abort long queries.

spring:
  datasource:
    hikari:
      maximum-pool-size: 200   # too large, wasteful
      connection-timeout: 1000 # too short, normal queries timeout
      idle-timeout: 60000      # too short, frequent create/destroy
      max-lifetime: 300000     # too short, adds overhead

Correct approach: Adjust parameters gradually based on actual business characteristics, validate with load testing, and record the optimal configuration.

@Component
public class ConnectionPoolTuning {
    public void performanceTuning() {
        // 1. Test baseline with default config
        // 2. Analyze concurrency, query complexity, transaction length
        // 3. Change one parameter at a time
        // 4. Verify with stress test
        // 5. Record the best settings
        System.out.println("Tuning principles:");
        System.out.println("1. Connections = CPU cores * 2 + disk count (initial)");
        System.out.println("2. Timeout > longest query time");
        System.out.println("3. Idle timeout balances resource use and creation cost");
        System.out.println("4. Lifetime < DB connection timeout");
    }
}

Takeaways:

Do not blindly follow “best practice”; adapt to your scenario.

Tuning is incremental; avoid changing many parameters at once.

Each adjustment must be data‑driven.

Document the tuning process for future reference.

6. Conclusion

Think of database access like keeping water cups ready in an office: don’t fetch water from the well for every sip; instead, have a pool of prepared cups.

In Spring Boot + KingbaseES, HikariCP is the high‑performance “water dispenser” and the preferred pool; Druid adds a visual monitor like a smart kettle, suitable for teams needing dashboards; DBCP and C3P0 are legacy “kettles” that still work but are not recommended for new projects.

True optimization isn’t about adding more cups but about studying peak traffic and average consumption to size the pool precisely, while also monitoring slow queries and long‑held connections.