Accessing DDM Using a JDBC Connection Pool

Scenario

When a connection pool is used, the system stores database connections in memory during initialization. Then, when you send a request to access a database, the system directly selects an available connection in the connection pool. If the connection is not required, the system returns the connection to the pool for another request to access. Connection establishment and disconnection are both managed by the connection pool itself. In addition, you can set connection pool parameters to control the number of initial connections in the connection pool, upper and lower limits of connections, maximum number of times that each connection can be used, and maximum idle time for each connection. If you prefer, you can also use your own system to monitor the number and usage of database connections.

Procedure

1. Configure Maven.
   • Java 8

     <dependency>
         <groupId>com.zaxxer</groupId>
         <artifactId>HikariCP</artifactId>
         <version>3.3.1</version>
     </dependency>

   • Java 7

     <dependency>
         <groupId>com.zaxxer</groupId>
         <artifactId>HikariCP-java7</artifactId>
         <version>2.4.13</version>
     </dependency>

2. Create a table.

   Table 1 Parameters for creating a table

   Table Name	Field	Type	Primary Key
   account	account_number	bigint	Yes
   	account_type	varchar (45)	No
   	account_name	varchar (50)	No

3. Connect to a DDM instance.
   1. Set the number of connections by configuring parameters in the JDBC URL and HikariCP parameters.
   2. Insert a data record.
   Example:

   package com.huawei.ddm.examples; 
   import java.sql.Connection; 
   import java.sql.PreparedStatement; 
   import java.sql.ResultSet; 
   import java.sql.SQLException; 
   import javax.sql.DataSource; 
   import com.zaxxer.hikari.HikariConfig; 
   import com.zaxxer.hikari.HikariDataSource; 
   
   public class HikariCPDemo { 
       private static DataSource datasource; 
       private static DataSource getDataSource() { 
           if (datasource == null) { 
               HikariConfig config = new HikariConfig(); 
               
               // Configure parameters in the JDBC URL:
               config.setJdbcUrl("jdbc:mysql:loadbalance://192.168.0.10:5066,192.168.0.11:5066/db_name?loadBalanceAutoCommitStatementThreshold=5&loadBalanceHostRemovalGracePeriod=15000&loadBalanceBlacklistTimeout=60000&loadBalancePingTimeout=5000&retriesAllDown=10&connectTimeout=10000"); 
               /* 
               // Or configure parameters in the JDBC URL as follows:
               config.addDataSourceProperty("loadBalanceAutoCommitStatementThreshold",5); 
               config.addDataSourceProperty("loadBalanceHostRemovalGracePeriod", 15000); 
               config.addDataSourceProperty("loadBalanceBlacklistTimeout", 60000); 
               config.addDataSourceProperty("loadBalancePingTimeout", 5000); 
               config.addDataSourceProperty("retriesAllDown", 10); 
               config.addDataSourceProperty("connectTimeout", 10000); 
               */ 
               config.setUsername("username"); 
               config.setPassword("password"); 
               config.setMaximumPoolSize(10); 
               config.setAutoCommit(true); 
   
               // Configure HikariCP parameters.
               config.addDataSourceProperty("cachePrepStmts", true); 
               config.addDataSourceProperty("prepStmtCacheSize", 250); 
               config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048); 
               config.addDataSourceProperty("minimumIdle", 5); 
               config.addDataSourceProperty("maximumPoolSize", 10); 
               config.addDataSourceProperty("idleTimeout", 30000); 
   
               datasource = new HikariDataSource(config); 
           } 
           return datasource; 
       } 
   
       public static void main(String[] args) { 
           Connection connection = null; 
           PreparedStatement pstmt = null; 
           ResultSet resultSet = null; 
           try { 
               DataSource dataSource = getDataSource(); 
               connection = dataSource.getConnection(); 
               System.out.println("The Connection Object is of Class: " + connection.getClass()); 
               // Start a transaction.
               connection.setAutoCommit(false);
   
               // Insert test data.
               String insertSql = "insert into account(account_number, account_type, account_name) values(?, ?, ?);";
               PreparedStatement insertStmt = connection.prepareStatement(insertSql);
               insertStmt.setLong (1, 1L); 
               insertStmt.setString (2, "manager"); 
               insertStmt.setString (3, "demotest01"); 
               insertStmt.executeUpdate(); 
               connection.commit ();
   
               // Query data.
               pstmt = connection.prepareStatement("SELECT * FROM account"); 
               resultSet = pstmt.executeQuery(); 
               while (resultSet.next()) { 
                   String accountNumber = resultSet.getString("account_number"); 
                   String accountType = resultSet.getString("account_type"); 
                   String accountName = resultSet.getString("account_name"); 
                   System.out.println(accountNumber + "," + accountType + "," + accountName); 
               } 
           } catch (Exception e) { 
               try { 
                   if (null != connection) { 
                       connection.rollback(); 
                   } 
               } catch (SQLException e1) { 
                   e1.printStackTrace(); 
               } 
               e.printStackTrace(); 
           } 
       } 
   }