Use Druid to develop applications - Lindorm - Alibaba Cloud Documentation Center

This topic describes how to use Druid to connect to and use LindormTable.

Prerequisites

The MySQL compatibility feature is enabled for the instance. For more information, see Enable the MySQL compatibility feature.
Java Development Kit (JDK) V1.8 or later is installed.
The IP address of your client is added to the whitelist of your Lindorm instance. For more information, see Configure whitelists.

Procedure

Before you use Druid to connect to LindormTable, you must install Druid and Lindorm JDBC Driver.

For example, you can 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-java</artifactId>
    <version>8.0.33</version>
</dependency>

If you want to use druid-spring-boot-starter to start Druid, you must first exclude the druid component on which druid-spring-boot-starter depends and then explicitly add the dependency on the druid component to the configuration file. The following example shows how to configure dependencies when you use druid-spring-boot-starter to start Druid:

<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-java</artifactId>
    <version>8.0.33</version>
</dependency>

Configure parameters for Druid. In the src/main/resources path of your Maven project, create the druid.properties file and add the following configurations to the file:

# Specify the name of the driver class. You can keep this configuration unchanged.
driverClassName=com.mysql.jdbc.Driver
# You can replace the url, username, and password parameters with the actual values in your business. You can obtain the values of these parameters on the Lindorm console.
# Replace the database parameter with the name of the database to which you want to connect.
url=jdbc:mysql://ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com:33060/database?sslMode=disabled&allowPublicKeyRetrieval=true&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=50000000
username=****
password=****

# Initialize the connection pool to create a connection. We recommend that you keep this configuration unchanged.
init=true
# Specify the number of connections that you want to create during initialization. You can configure this parameter based on your requirements.
initialSize=10
# Specify the maximum number of connections in the connection pool. You can configure this parameter based on your requirements. We recommend that you set this parameter to the same value as that of the thread pool in your business.
maxActive=200
# Specify the minimum number of idle connections in the connection pool. You can configure this parameter based on your requirements. We recommend that you set this parameter to the same value as that of the maxActive parameter.
minIdle=200
# Specify the maximum time period that the client can wait to obtain a connection. Unit: ms. We recommend that you keep this value unchanged.
maxWait=30000

# Configure parameters related to connection keep-alive. We recommend that you keep these configurations unchanged. Otherwise, the connection may be unexpectedly disconnected.
druid.keepAlive=true
# Specify the allowed idle period of the connections. If a connection has been in the idle state for a period longer than this value, the validity of the connection is checked.
druid.keepAliveBetweenTimeMillis=120000
# Specify the interval at which connections are determined to be evicted or kept alive.
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=1800000

# Configure parameters that are required to verify connections. We recommend that you keep these configurations unchanged.
testWhileIdle=true
testOnBorrow=false
testOnReturn=false

Parameters

Parameter	Description
url	The URL used by the JDBC for the Druid client to connect to the MySQL database. The URL is in the following format: `jdbc:mysql://<LindormTable endpoint for MySQL>/<Database name>?<Connection parameter>`. For more information about how to obtain the LindormTable endpoint for MySQL, see View endpoints. If you do not specify the database name in the URL, the client connects to the default database.
username	If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see the Change the password of a user section of the "Manage users" topic.
password

Load the parameter configurations of Druid and initialize the connection pool.

// Load parameter configurations.
Properties properties = new Properties();
InputStream inputStream = DruidPoolDemo.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(inputStream);
// Initialize the connection pool.
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

Use Druid to obtain connection information from JDBC and connect to LindormTable.

/* -------------- Example on how to connect to LindormTable by using JDBC  ----------------- */

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

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

// 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();
    }
}

Note

In Lindorm SQL, the INSERT syntax is equivalent to the UPSERT syntax. However, the JDBC for MySQL client is optimized for the INSERT syntax. Therefore, we recommend that you use the INSERT syntax to write data.