Configuring JDBC to Connect to a Cluster (Load Balancing Mode)

Function

When JDBC is used to connect to a cluster, if only one CN address is configured, all service requests are sent to this CN. As a result, the CN is overloaded, while other CNs are idle. If the CN is faulty, the connection becomes unavailable, affecting service continuity. To avoid this issue, you are advised to use load balancing and distribute connection requests among multiple CNs. This reduces the pressure on a single CN and improves connection availability. The following three methods are available:

• Connecting to a cluster using ELB (recommended): Elastic Load Balance (ELB) distributes access traffic to multiple ECSs based on forwarding policies. It improves the fault tolerance capability of applications.
• Connecting to a cluster using JDBC load balancing (recommended): Enable load balancing of the JDBC driver and configure the private IP address of at least one CN in the URL. The system will automatically scan the IP addresses of all CNs. JDBC load balancing functions like ELB, randomly connecting to a CN.
• Connecting to a cluster in multi-host mode: Use JDBC to manually configure IP addresses of multiple nodes, which is similar to ELB.

Differences Between the Three Load Balancing Methods

The following table provides the differences between the three methods. Select one based on your service scenario.

Notes and Constraints

The sample code uses the system administrator dbadmin to establish connections. The dbadmin user has the highest permissions of the cluster and can perform all management operations. For routine services, you are advised to create independent service users and grant them the minimum permissions required (see Creating a DWS Database User or Role) to prevent security risks caused by excessive permissions.

• To connect to a cluster in JDBC load balancing mode, ensure that the JDBC driver version is 8.3.1.200 or later.
• The IP addresses, port numbers, and database names in this section are for reference only. Replace them with the actual connection addresses and database names of the cluster. If the configuration is incorrect, the connection will fail.
• When connecting to a cluster using a private IP address, ensure that the client and the cluster are in the same VPC so that they can communicate with each other.

Method 1: Using ELB to Connect to a Cluster

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. Obtain the load balancer IP address. Click the name of a specified cluster to go to the cluster details page and obtain the load balancer IP address. For details about how to bind a load balancer to a cluster, see Binding and Unbinding Load Balancers for a DWS Cluster.
4. Configure the driver. For details, see Downloading the JDBC or ODBC Driver. Add the following dependency to the pom.xml file of the Maven project:

   1 2 3 4 5

   <dependency> <groupId>com.huaweicloud.dws</groupId> <artifactId>huaweicloud-dws-jdbc</artifactId> <version>8.5.1</version> </dependency>

5. Obtain the database connection. If you connect to the cluster using ELB, you only need to configure the IP address and port number of the load balancer in the URL. Replace 192.160.xx.xx:8000 in the following example with the IP address and database port number of the load balancer.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

   private static final String USER_NAME = "dbadmin"; private static final String PASSWORD = "password"; // jdbc:postgresql://ELB_IP:PORT/dbName private static final String URL = "jdbc:postgresql://192.160.xx.xx:8000/gaussdb"; private static Properties properties = new Properties(); static { properties.setProperty("user", USER_NAME); properties.setProperty("password", PASSWORD); } /** * Obtain the database connection. */ public static Connection getConnection() { Connection connection = null; try { connection = DriverManager.getConnection(URL, properties); } catch (SQLException e) { e.printStackTrace(); } return connection; }

6. Verify the connection.
   • Call the getConnection() method. The Connection object is returned and no exception is thrown.
   • Run the following statement using the connection. The database version is returned.

     select version();

Method 2: Using JDBC Load Balancing to Connect to a Cluster (Recommended)

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. Obtain the private IP address. Click the name of a specified cluster to go to the cluster details page and click the Cluster Topology tab to obtain the private IP address of a CN. For details, see Obtaining the Connection Address of a DWS Cluster.
4. Configure the driver. For details, see Downloading the JDBC or ODBC Driver. The JDBC load balancing mode is supported by version 8.3.1.200 or later. Ensure that the JDBC driver version has been upgraded to 8.3.1.200 or later. (Version 8.5.1 is used as an example in this document.) Add the following dependency to the pom.xml file of the Maven project:

   1 2 3 4 5

   <dependency> <groupId>com.huaweicloud.dws</groupId> <artifactId>huaweicloud-dws-jdbc</artifactId> <version>8.5.1</version> </dependency>

5. Obtain the database connection. After you configure the private IP address of at least one CN, the driver automatically scans all CNs in the cluster to obtain their IP addresses. For details about how to configure URL parameters, see Table 2. Replace the IP address and port number in the following example URL with the private IP address and database port number of the CN obtained in 3.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

   private static final String USER_NAME = "dbadmin"; private static final String PASSWORD = "password"; // jdbc:postgresql://host1:port1,host2:port2/dbName private static final String URL = "jdbc:postgresql://192.160.0.10:8000,192.168.0.11:8000,192.168.0.12:8000/gaussdb?loadBalanceHosts=true&cnListRefreshSwitch=on&cnListRefreshDelay=100000&cnListRefreshPeriod=5000"; private static Properties properties = new Properties(); static { properties.setProperty("user", USER_NAME); properties.setProperty("password", PASSWORD); } /** * Obtain the database connection. */ public static Connection getConnection() { Connection connection = null; try { connection = DriverManager.getConnection(URL, properties); } catch (SQLException e) { e.printStackTrace(); } return connection; }

   Table 2 JDBC URL parameters

   Parameter	Description	Default Value	Recommendation
   loadBalanceHosts	Whether JDBC load balancing is enabled. If this function is enabled, each new connection is randomly distributed to a CN that has been configured.	false	Set this parameter to true when the load balancing mode is used for connection.
   cnListRefreshSwitch	Whether to enable automatic detection of available CNs. If this function is enabled, the driver periodically obtains the CN list of the cluster and automatically updates the connection list when CNs in the cluster change.	off	You are advised to set this parameter to on when CNs in the cluster may change (for example, scale in or out).
   cnListRefreshDelay	Delay for automatic detection of available CNs, in milliseconds. After the driver is started, it is refreshed for the first time after the specified delay. This parameter is valid only when cnListRefreshSwitch is set to on.	1800000	You can set this parameter based on the interval between driver startup and the initial service connection.
   cnListRefreshPeriod	Period for automatic detection of available CNs, in milliseconds. The driver refreshes the CN list at this interval. This parameter is valid only when cnListRefreshSwitch is set to on.	1800000	You can decrease the value when CNs in the cluster change frequently to detect changes in a timely manner.

6. Verify the connection.
   • Call the getConnection() method. The Connection object is returned and no exception is thrown.
   • Run the following statement using the connection. The database version is returned.

     select version();

Method 3: Connecting to the Cluster in Multi-host Mode

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. Obtain the EIP. Click the name of a specified cluster to go to the cluster details page and obtain the EIP.
4. Configure the driver. For details, see Downloading the JDBC or ODBC Driver. Add the following dependency to the pom.xml file of the Maven project:

   1 2 3 4 5

   <dependency> <groupId>com.huaweicloud.dws</groupId> <artifactId>huaweicloud-dws-jdbc</artifactId> <version>8.5.1</version> </dependency>

5. Obtain the database connection. Replace the IP address and port number in the following example URL with the IP address and database port number of the CN obtained. In multi-host mode, you need to manually configure the IP addresses of multiple CNs and separate them with commas (,). If the URL parameter loadBalanceHosts is set to true, load balancing is enabled and connections are randomly distributed to CNs.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

   private static final String USER_NAME = "dbadmin"; private static final String PASSWORD = "password"; // jdbc:postgresql://host1:port1,host2:port2/dbName private static final String URL = "jdbc:postgresql://192.160.0.10:8000,192.168.0.11:8000,192.168.0.12:8000/gaussdb?loadBalanceHosts=true"; private static Properties properties = new Properties(); static { properties.setProperty("user", USER_NAME); properties.setProperty("password", PASSWORD); } /** * Obtain the database connection. */ public static Connection getConnection() { Connection connection = null; try { connection = DriverManager.getConnection(URL, properties); } catch (SQLException e) { e.printStackTrace(); } return connection; }

6. Verify the connection.
   • Call the getConnection() method. The Connection object is returned and no exception is thrown.
   • Run the following statement using the connection. The database version is returned.

     select version();

References

• JDBC driver version compatibility: JDBC Version Description
• Binding a load balancer to a DWS cluster: Binding and Unbinding Load Balancers for a DWS Cluster
• Creating an independent service user and granting the minimum permissions required to the user: Creating a DWS Database User or Role