Updated on 2024-10-21 GMT+08:00

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

Context

If you use JDBC to connect to only one CN in the cluster, this CN may be overloaded and other CN resources wasted. It also incurs single-node failure risks.

To avoid these problems, you can use JDBC to connect to multiple CNs. The following three methods are available:

  • Connection using ELB: An ELB distributes access traffic to multiple ECSs for traffic control based on forwarding policies. It improves the fault tolerance capability of application programs.
  • To connect to a cluster using JDBC load balancing, include at least one internal IP address of the CN in the URL. The system will scan all CN IP addresses automatically. JDBC load balancing functions like ELB, randomly connecting to a CN.
  • Connection in multi-host mode: Use JDBC to configure multiple nodes, which is similar to ELB.

Method 1: Using ELB to Connect to a Cluster

  1. Obtain the Elastic Load Balance address. On the console, go to the details page of a cluster and obtain the ELB IP address. For details, see Associating and Disassociating ELB.
  2. Configure the driver. For details, see Downloading the JDBC or ODBC Driver.

    1
    2
    3
    4
    5
    <dependency> 
        <groupId>com.huaweicloud.dws</groupId>  
        <artifactId>huaweicloud-dws-jdbc</artifactId>   
        <version>8.1.1.1</version> 
    </dependency>
    

  3. Obtain the database connection.

     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://100.95.153.169: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;
    }
    

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

  1. Obtain the private IP address. Open the specified cluster topology page on the console and obtain the internal IP address of the CN. For details, see Viewing the GaussDB(DWS) Cluster Topology.
  2. Configure the driver. For details, see Downloading the JDBC or ODBC Driver.

    1
    2
    3
    4
    5
    <dependency> 
        <groupId>com.huaweicloud.dws</groupId>  
        <artifactId>huaweicloud-dws-jdbc</artifactId>   
        <version>8.3.1.200</version> 
    </dependency>
    

  3. Obtain the database connection. For how to set URL parameters, see Using JDBC to Connect to a Cluster.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    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://100.95.146.194:8000,100.95.148.220:8000,100.93.0.221: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;
    }
    

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

  1. Obtain the EIP. Go to the details page of a cluster on the console and obtain the EIP.
  2. Configure the driver. For details, see Downloading the JDBC or ODBC Driver.

    1
    2
    3
    4
    5
    <dependency> 
        <groupId>com.huaweicloud.dws</groupId>  
        <artifactId>huaweicloud-dws-jdbc</artifactId>   
        <version>8.1.1.1</version> 
    </dependency>
    

  3. Obtain the database connection.

     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://100.95.146.194:8000,100.95.148.220:8000,100.93.0.221: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;
    }