Lindorm:Use Druid to develop applications

Prerequisites

• Java Development Kit (JDK) V1.8 or later is installed.

• A whitelist is configured for the Lindorm instance. For more information, see Configure whitelists.

• The version of LindormTable is 2.3.1 or later. For more information about how to upgrade the version of LindormTable, see Upgrade the minor engine version of a Lindorm instance.

Preparations

• 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>com.aliyun.lindorm</groupId>
    <artifactId>lindorm-all-client</artifactId>
    <version>2.2.1.3</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>com.aliyun.lindorm</groupId>
    <artifactId>lindorm-all-client</artifactId>
    <version>2.2.1.3</version>
  </dependency>

Procedure

1. 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.aliyun.lindorm.table.client.Driver
   # You can replace the url, username, and password parameters to the actual values in your business. You can obtain the values of these parameters on the Lindorm console.
   url=jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****.lindorm.rds.aliyuncs.com:30060
   username=****
   password=****
   # Replace **** with the database that you want to connect.
   connectionProperties=database=****
   
   # 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 minimum number of idle connections in the connection pool. You can configure this parameter based on your requirements.
   minIdle=10
   # Specify the maximum number of idle connections in the connection pool. You can configure this parameter based on your requirements.
   maxActive=20
   # Specify the maximum time that the client can wait to obtain a connection. Unit: ms. We recommend that you keep this configuration 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.
   # In this case, the ConnectionDisconnectedException exception is reported.
   druid.keepAlive=true
   druid.keepAliveBetweenTimeMillis=30000
   minEvictableIdleTimeMillis=600000
   maxEvictableIdleTimeMillis=900000
   timeBetweenEvictionRunsMillis=5000
   
   # Configure parameters that are required to verify connections. We recommend that you keep these configurations unchanged.
   validationQuery=SELECT 1
   testWhileIdle=true
   testOnBorrow=false
   testOnReturn=false
   
   # Configure parameters related to cache. In this example, cache is disabled. We recommend that you keep these configurations unchanged.
   # Otherwise, the NoSuchStatement exception may be reported.
   poolPreparedStatements=false
   maxOpenPreparedStatements=-1
   druid.maxPoolPreparedStatementPerConnectionSize=-1

   Note

   • Adjust or replace the values of the following parameters in actual use:

     • url: The endpoint used to connect to LindormTable. For more information about how to obtain the endpoint, see Obtain an endpoint.

     • username: The username used to access LindormTable. You can view the username in the cluster management system of LindormTable.

     • password: The password used to access LindormTable. If you forget your password, you can change the password in the cluster management system of LindormTable. For more information about how to change your password, see Change the password of a user.

   • For more information about the parameters that you can configure, see DruidDataSource parameter configurations.

2. 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);

3. 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 = "upsert 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();
       }
   }