Develop applications using the Druid connection pool for Java

Prerequisites

Before you begin, make sure you have:

• The MySQL compatibility feature enabled for your Lindorm instance. See Enable the MySQL compatibility feature

• JDK V1.8 or later installed

• Your client IP address added to the whitelist of your Lindorm instance. See Configure whitelists

Add dependencies

Add the following dependencies to the pom.xml file in your Maven project:

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.2.11</version>
</dependency>
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.3.0</version>
</dependency>

If you use druid-spring-boot-starter, exclude the bundled druid component and declare the druid dependency explicitly:

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.2.11</version>
  <exclusions>
    <exclusion>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
    </exclusion>
  </exclusions>
</dependency>
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.2.11</version>
</dependency>
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.3.0</version>
</dependency>

Configure the connection pool

In the src/main/resources directory of your Maven project, create druid.properties with the following content:

# Driver class — keep this unchanged.
driverClassName=com.mysql.cj.jdbc.Driver

# Replace <endpoint> with your LindormTable endpoint for MySQL and <database> with your database name.
# To get the endpoint, see View endpoints: https://www.alibabacloud.com/help/en/lindorm/user-guide/view-endpoints
# Keep the other URL parameters unchanged to maintain optimal performance.
url=jdbc:mysql://<endpoint>:33060/<database>?sslMode=disabled&allowPublicKeyRetrieval=true&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=50000000&socketTimeout=120000
username=<username>
password=<password>

# Initialize the connection pool on startup — keep this unchanged.
init=true

# Number of connections to create during initialization.
initialSize=10
# Maximum number of connections in the pool. Set this to match your thread pool size.
maxActive=40
# Minimum number of idle connections. For high-throughput scenarios, set this equal to maxActive.
# For workloads with large fluctuations, use a smaller value.
minIdle=40
# Maximum time (ms) the client waits to get a connection — keep this unchanged.
maxWait=30000

# Limits how many times a single connection can be reused, preventing uneven load
# across Lindorm SQL frontend nodes. Has a minor performance impact.
druid.phyMaxUseCount=10000

# Keep-alive settings — keep these unchanged to prevent unexpected disconnections.
druid.keepAlive=true
# If a connection has been idle for longer than this value (ms), its validity is checked.
druid.keepAliveBetweenTimeMillis=120000
# Interval (ms) at which idle connections are checked for eviction or keep-alive.
timeBetweenEvictionRunsMillis=60000
# Connections idle for longer than this value (ms) are candidates for eviction.
minEvictableIdleTimeMillis=300000
maxEvictableIdleTimeMillis=600000
phyTimeoutMillis=1800000

# Connection validation settings — keep these unchanged.
testWhileIdle=true
testOnBorrow=false
testOnReturn=false

Initialize the connection pool

Load the configuration file and create the data source:

// Load configuration from druid.properties.
Properties properties = new Properties();
InputStream inputStream = DruidPoolDemo.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(inputStream);

// Initialize the connection pool.
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

Run CRUD operations

All examples get a connection from the pool and return it automatically using try-with-resources. If you do not use try-with-resources, call conn.close() explicitly — this returns the connection to the pool, it does not close it. If a connection becomes invalid without being returned, Druid cannot detect the invalid state.

String tableName = "sql_table_" + new Random().nextInt(1000);

// Create a table.
try (Connection connection = dataSource.getConnection()) {
    try (Statement statement = connection.createStatement()) {
        String sql = "create table if not exists " + tableName + "(id VARCHAR, name VARCHAR, primary key(id))";
        int ret = statement.executeUpdate(sql);
        System.out.println(ret);
    }
}

// Insert a single row.
try (Connection connection = dataSource.getConnection()) {
    String sql = "insert into " + tableName + "(id,name) values(?,?)";
    try (PreparedStatement ps = connection.prepareStatement(sql)) {
        ps.setString(1, "aa");
        ps.setString(2, "bb");
        int ret = ps.executeUpdate();
        System.out.println(ret);
    }
}

// Batch insert.
String insertSql = "insert into " + tableName + "(id,name) values(?,?)";
int batchSize = 100;
try (Connection connection = dataSource.getConnection()) {
    try (PreparedStatement ps = connection.prepareStatement(insertSql)) {
        for (int i = 0; i < batchSize; i++) {
            ps.setString(1, "aa" + i);
            ps.setString(2, "bb" + i);
            ps.addBatch();
        }
        ps.executeBatch();
    }
}

// Query data.
try (Connection connection = dataSource.getConnection()) {
    String sql = "select * from " + tableName + " where id=?";
    try (PreparedStatement ps = connection.prepareStatement(sql)) {
        ps.setString(1, "aa");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            String id = rs.getString(1);
            String name = rs.getString(2);
            System.out.println("id=" + id);
            System.out.println("name=" + name);
        }
    }
}

// Delete data.
try (Connection connection = dataSource.getConnection()) {
    String sql = "delete from " + tableName + " where id=?";
    try (PreparedStatement ps = connection.prepareStatement(sql)) {
        ps.setString(1, "aa");
        ps.executeUpdate();
    }
}

Production considerations

druidDataSource.getStatData();  // Returns current pool statistics
druidDataSource.dump();         // Logs detailed pool configuration and state

FAQ

Why does my application throw Read timed out?

The default socket timeout in Druid is 10 seconds. If a query takes longer, the connection times out and throws this error. Set socketTimeout in milliseconds in the JDBC URL. The recommended value is 120000 (2 minutes):

jdbc:mysql://<endpoint>:33060/<database>?sslMode=disabled&allowPublicKeyRetrieval=true&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=50000000&socketTimeout=120000

Appendix: Connection pool load balancing

Connection pooling (long-lived TCP connections) is more efficient, but it may interfere with distributed load balancing in the following scenarios and cause uneven connection distribution:

• Uneven distribution when many connections are created at once

  When an application suddenly creates a large number of connections, the load balancer may not refresh backend connection statistics in time. Some LDServer nodes may then receive more connection requests. Combined with connection pooling, this can put more pressure on some LDServer nodes than on others, resulting in uneven distribution and affecting overall system performance.

• Uneven distribution due to load balancer health check failures

  Load balancers use active health checks to determine whether backend nodes are healthy. Occasional health check failures may leave some LDServer nodes with fewer connections. Combined with connection pooling, this can put less pressure on some LDServer nodes than on others, resulting in uneven load and affecting overall system performance.

The Druid connection pool provides the phyTimeoutMillis and phyMaxUseCount parameters to periodically refresh pooled connections (for example, every 30 minutes or every 10,000 uses). Add both parameters by default to address these issues while keeping performance largely unchanged.