Lindorm: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>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>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.
phyMaxUseCount=30000

# 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=1800000
maxEvictableIdleTimeMillis=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