ApsaraDB RDS:Use Druid to connect to an ApsaraDB RDS for MySQL instance

Prerequisites

• Java Development Kit (JDK) 1.8 or later is installed on your application server.

• The IP address of the server is added to an IP address whitelist of the RDS instance. For more information, see Configure an IP address whitelist.

  Note

  If your application is deployed on an Elastic Compute Service (ECS) instance that resides in the same region and virtual private cloud (VPC) as the RDS instance, you do not need to configure an IP address whitelist.

Preparations

The following section uses a Maven project as an example to describe the preparations before you use Druid to connect to an RDS instance.

• Install Druid: Add the following dependency to the dependencies section in the pom.xml file. We recommend that you use Druid 1.2.13 or later.

  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.19</version>
  </dependency>

• If you want to use druid-spring-boot-starter in the project, you must exclude the Druid dependency on which druid-spring-boot-starter depends from the dependencies section in the pom.xml file and then explicitly add the Druid dependency back. Sample code:

  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid-spring-boot-starter</artifactId>
      <version>1.2.19</version>
      <exclusions>
          <exclusion>
              <groupId>com.alibaba</groupId>
              <artifactId>druid</artifactId>
          </exclusion>
      </exclusions>
  </dependency>
  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.19</version>
  </dependency>

Use Druid to connect to an RDS instance

1. Configure the parameters of the connection pool: Create a file named druid.properties in the src/main/resources directory of the project and add the following content to the file to configure the parameters for the Druid connection pool. For more information about the parameters, see Common parameters of the connection pool.

   Note

   For more information about how to obtain the endpoint and port number of an RDS instance, see View and manage instance endpoints and ports.

   # The name of the driver class. You do not need to modify this parameter.
   driverClassName=com.mysql.jdbc.Driver
   
   # Specify the url, username, and password parameters based on your business requirements.
   url=jdbc:mysql://rm-bp**************.mysql.rds.aliyuncs.com:3306/database
   username=****
   password=****
   
   # Specify the number of connections that are established during initialization.
   initialSize=20
   # Specify the minimum number of idle connections.
   minIdle=20
   # Specify the maximum number of active connections in a connection pool.
   maxActive=100
   # Specify the maximum period of time that a physical connection can be reused.
   phyTimeoutMillis=3600000
   # Specify the maximum waiting time to obtain a connection. Unit: milliseconds. If no available connections exists in the connection pool and the maximum number of connections is reached, the request from the application to obtain a connection is blocked until the maximum waiting time elapses. We recommend that you retain the parameter setting.
   maxWait=5000
   # Specify the timeout period for establishing a TCP connection between the database driver and the server on which the RDS instance resides. Unit: milliseconds.
   connectTimeout=20000
   # Specify the socket timeout period for an application that is waiting for a response after the application sends data, such as SQL statements, over a TCP connection. Unit: milliseconds.
   socketTimeout=60000
   
   # Configure the required parameters to verify connections. We recommend that you retain the parameter settings.
   testWhileIdle=true
   testOnBorrow=false
   testOnReturn=false
   
   # Configure the parameters related to caching. The PreparedStatement parameter is set to true, which indicates that caching is enabled. We recommend that you retain the parameter settings.
   poolPreparedStatements=true
   maxPoolPreparedStatementPerConnectionSize=100

2. Use the Druid connection pool to connect to an RDS instance: Import the required dependencies into the source code file in the src/main/java directory, build the DruidPoolDemo class, obtain information about the Java Database Connectivity (JDBC) connections from the Druid connection pool, and then access the RDS instance.

   // Import the required dependencies.
   package com.aliyun.rdsfinops.collector.impl;
   import com.alibaba.druid.pool.DruidDataSourceFactory;
   import javax.sql.DataSource;
   import java.io.InputStream;
   import java.sql.*;
   import java.util.Properties;
   
   public class DruidPoolDemo {
       private static DataSource dataSource = null;
       String tableName = "sql_table_test";
       // Load parameter settings.
       static {
           Properties properties = new Properties();
           // Obtain the configurations of the connection pool.
           InputStream inputStream = DruidPoolDemo.class.getClassLoader().getResourceAsStream("druid.properties");
           try {
               properties.load(inputStream);
               // Initialize the connection pool.
               dataSource = DruidDataSourceFactory.createDataSource(properties);
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
       /* 
         View subsequent database operations in the following sections:
         Create a table: public void createTable()
         Insert data: public void insertData()
         Query data: public void selectData()
         Delete data: public void deleteData()
       */
   }

Common parameters of the connection pool

When you use Druid to connect to an RDS instance, we recommend that you configure the following parameters for the connection pool to ensure that the RDS instance runs in a stable and efficient manner.

Subsequent database operations

If you want to perform operations on databases, you can add user-defined functions to the DruidPoolDemo class. The following sample codes provide examples on how to create a table, insert data into the table, query data, and delete data:

String tableName = "sql_table_test";
public void createTable() throws SQLException {
    // Create a table.
    try (Connection connection = dataSource.getConnection()) {
        try (Statement statement = connection.createStatement()) {
            String sql = "create table if not exists " + tableName + "(id VARCHAR(255), name VARCHAR(255), PRIMARY KEY (id))";
            int ret = statement.executeUpdate(sql);
            System.out.println(ret);
        }
    }
}

String tableName = "sql_table_test";
public void insertData(){
    // Insert data to the table.
    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);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

String tableName = "sql_table_test";
public void selectData() throws SQLException {
    // Query data in the table.
    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);
            }
        }
    }
}

String tableName = "sql_table_test";
public void deleteData(){
    // Delete data from the table.
    try (Connection connection = dataSource.getConnection()) {
        String sql = "delete from " + tableName + " where id=?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, "aa");
            ps.executeUpdate();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

References

• For more information about the Python connection pool, see Use DBUtils to connect to an ApsaraDB RDS for MySQL instance.

• For more information about the Go driver package, see Use Go-MySQL-Driver to connect to an ApsaraDB RDS for MySQL instance.

• For more information about the connection pooling feature of a database proxy in ApsaraDB RDS for MySQL, see Configure the connection pooling feature.