Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PolarDB-X JDBC Driver (polardbx-connector-java)

PolarDB-X JDBC Driver (polardbx-connector-java)

This article introduces the PolarDB-X JDBC driver (polardbx-connector-java) for efficiently connecting to PolarDB-X databases in Java applications.

To use PolarDB-X 2.0 more easily, PolarDB-X provides a standard JDBC driver for Java to:

  • Directly connect to PolarDB-X 2.0 Standard Edition and provide the ability to automatically connect to new primary nodes after HA.
  • Directly connect to PolarDB-X 2.0 Standard Edition and provide smooth switchover during scheduled three-node switchover.
  • Directly connect to PolarDB-X 2.0 Enterprise Edition and achieve load balancing across multiple nodes.

Basic Information

Maven Dependencies

The driver is polardbx-connector-java and depends on mysql-connector-java through the provided mode so that users can choose the MySQL JDBC connector to use.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

<dependency>
    <groupId>com.alibaba.polardbx</groupId>
    <artifactId>polardbx-connector-java</artifactId>
    <version>2.1.2</version>
</dependency>

Usage Notes

Sample JDBC URL:

jdbc:polardbx://11.167.60.147:6991/test
  • Driver package name: com.alibaba.polardbx.Driver
  • Protocol header: polardbx

  • Specified IP port:

    • For the Standard Edition, IP: port, or VIP: port, or IP0: port0, IP1: port1 (nodes must be in the same cluster, separated by English commas (,)). It will be routed to the leader node when establishing the connection.
    • For the Enterprise Edition, IP: port, or VIP: port, or IP0: port0, IP1:port1 (nodes must be in the same cluster, separated by English commas (,)). It will be distributed to a random read/write node in the cluster based on load balancing when the connection is established.

  • Additional parameters:

    • clusterId: the ID of the cluster, which is used for verification to avoid misconnection. You can leave it unspecified and obtain it during the first connection.
    • haCheckConnectTimeoutMillis: The connection timeout for HA detection, 3000 milliseconds by default.
    • haCheckSocketTimeoutMillis: The query timeout for HA detection, 3000 milliseconds by default.
    • haCheckIntervalMillis: The interval for HA detection, 1000 milliseconds by default.
    • checkLeaderTransferringIntervalMillis: The interval for smooth switchover detection, 100 milliseconds by default.
    • leaderTransferringWaitTimeoutMillis: The maximum blocking and waiting time for smooth switchover, 5000 milliseconds by default.
    • smoothSwitchover: Indicates whether to support smooth high-availability switchover of the connection pool. By returning true when isClosed is called during the switchover, the connection pool discards the outdated connection. The default value is true.

  • Additional behavior:

    • The connectTimeout parameter is set to 5000 milliseconds when the leader is obtained for the first time or the HA switchover is performed. In other cases, there is no timeout by default.

  • Other features:

    • Written in the JDK 8 standard and compatible with earlier versions.
    • Reference the standard JDBC connector to accommodate various connection pools.
    • After HA occurs, all requests on existing connections will return errors.
    • When retrieving a connection, the HA switchover status is checked to realize smooth switchover.

JDBC URL is compatible with MySQL JDBC connector and supports common parameters such as user, password, useSSL, characterEncoding, connectTimeout, socketTimeout, allowLoadLocalInfile, allowPublicKeyRetrieval, sslMode, characterEncoding, useCursorFetch, rewriteBatchedStatements, netTimeoutForStreamingResults, useServerPrepStmts, and useUnicode.

Smooth Switchover Capability

PolarDB-X, combined with the polardbx-connector-java driver, provides the smooth switchover capability for the database.

For example, as a DBA conducts O&M when the active host of a database is offline, the database is expected to operate HA switchover. Based on the smooth switchover capability of the PolarDB-X database and the driver, the system can provide an imperceptible experience without interrupting business SQL statements or reporting errors, which greatly improves the experience of using the database.

The following figure shows the working principle of the smooth switchover:

1

The smooth switchover provided by PolarDB-X allows the driver to sense the status of the three-node cluster of PolarDB-X Standard Edition in real time. Before the database HA switchover is completed:

  • The client driver blocks the allocated connection from returning until the switchover is complete.
  • Complete the request as soon as possible after the service has obtained the connection and mark it as non-reusable.

Take note of the following items:

  • The client driver adds two checkpoints (connection acquisition and connection return) for smooth switchover. By sensing the status of the three-node cluster in PolarDB-X Standard Edition in real time, the dynamic connection reconstruction capability after the HA switchover is implemented. This only increases the time to obtain a new connection but does not cause errors on the existing connection. Therefore, the application is imperceptible to scheduled switchovers.
  • When the service obtains a database connection, it is recommended to use database operations implemented by the standard try-with-resources statement. As long as the service execution time in the try block is less than the HA window set by the corresponding PolarDB-X (the consensus_wait_millisecond_before_change_leader time threshold, with a default value of 1 second), no error is returned due to scheduled switchovers. However, for a long-running transaction that holds a connection beyond the time threshold, as it spans the switchover process, an error is reported after the switchover because writing is not allowed.

Currently, for the smooth switchover capability of PolarDB-X, the recommended connection pool is Druid v1.2.24 or later.

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.24</version>
</dependency>

Best practices for Druid connection pool parameters: How to Select an Application Connection Pool

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="com.alibaba.polardbx.Driver" />
        <!-- Specify the following basic properties: URL, username, and password. -->
        <property name="url" value="jdbc:polardbx://ip:port/db?autoReconnect=true&rewriteBatchedStatements=true&socketTimeout=30000&connectTimeout=3000" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
        <!-- Specify the initial size, minimum size, and maximum size of the connection pool. -->
        <property name="maxActive" value="20" />
        <property name="initialSize" value="3" />
        <property name="minIdle" value="3" />
        <!-- maxWait: Specify the timeout period of connection establishment. -->
        <property name="maxWait" value="60000" />
        <!-- timeBetweenEvictionRunsMillis: Specify the interval at which the system detects idle connections that need to be closed. Unit: milliseconds. -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <!-- minEvictableIdleTimeMillis: Specify the minimum period of time for which a connection can remain idle in the connection pool. Unit: milliseconds. -->
        <property name="minEvictableIdleTimeMillis" value="300000" />
        <!-- Specify the SQL statement that is used to check whether a connection is available. -->
        <property name="validationQuery" value="select 'z' from dual" />
        <!-- Specify whether to detect idle connections. -->
        <property name="testWhileIdle" value="true" />
        <!-- Specify whether to check the status of a connection before the system borrows the connection. -->
        <property name="testOnBorrow" value="false" />
        <!-- Specify whether to check the status of a connection before the system returns the connection. -->
        <property name="testOnReturn" value="false" />
        <!-- Specify the validity period of each connection. The system automatically closes the connections whose validity period expires. You can specify this parameter to balance the loads on backend nodes. -->
        <property name="phyTimeoutMillis" value="600000" />
        <!-- Specify the maximum number of SQL query requests that can be sent over each connection. After the number of SQL query requests that are sent over a connection reaches the value of this parameter, the system closes the connection. You can specify this parameter to balance the loads on backend nodes. -->
        <property name="phyMaxUseCount" value="10000" />
</bean>

Examples of Quick Start

1. Directly use the driver to connect to PolarDB-X

Class.forName("com.alibaba.polardbx.Driver");

try (final Connection conn = DriverManager.getConnection(
    "jdbc:polardbx://127.0.0.1:3306/", "root", "*****");
    final Statement stmt = conn.createStatement()) {

    try (final ResultSet rs = stmt.executeQuery("select 1")) {
        for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
            System.out.print(rs.getMetaData().getColumnName(i + 1) + "\t");
        }
        System.out.println();
        while (rs.next()) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
                System.out.print(rs.getObject(i + 1) + "\t");
            }
            System.out.println();
        }
    }
}

2. Use the Druid connection pool

try (final DruidDataSource dataSource = new DruidDataSource()) {
    dataSource.setUrl("jdbc:polardbx://127.0.0.1:3306/");
    dataSource.setUsername("root");
    dataSource.setPassword("*****");
    
    // If the version of the Druid connection pool is 1.2.23 or earlier, you need to call setDriverClassName and setExceptionSorter to adapt to the PolarDB-X driver. Newer versions (1.2.24 and later) can automatically identify the driver.
dataSource.setDriverClassName("com.alibaba.polardbx.Driver");
    dataSource.setExceptionSorter(new MySqlExceptionSorter());

    try (final Connection conn = dataSource.getConnection();
        final Statement stmt = conn.createStatement()) {

        try (final ResultSet rs = stmt.executeQuery("select 1")) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
System.out.print(rs.getMetaData().getColumnName(i + 1) + "\t");
            }
            System.out.println();
            while (rs.next()) {
                for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
                    System.out.print(rs.getObject(i + 1) + "\t");
                }
                System.out.println();
            }
        }
    }
}

Alternatively, after setting up the data source through XML, add the following two items to adapt to the new driver:

dataSource.setDriverClassName("com.alibaba.polardbx.Driver");
dataSource.setExceptionSorter(new MySqlExceptionSorter());

3. Use the HikariCP connection pool

Class.forName("com.alibaba.polardbx.Driver");

final HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:polardbx://127.0.0.1:3306/");
config.setUsername("root");
config.setPassword("*****");
config.setMaximumPoolSize(10);

try (HikariDataSource dataSource = new HikariDataSource(config)) {
    try (final Connection conn = dataSource.getConnection();
        final Statement stmt = conn.createStatement()) {

        try (final ResultSet rs = stmt.executeQuery("select 1")) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
System.out.print(rs.getMetaData().getColumnName(i + 1) + "\t");
            }
            System.out.println();
            while (rs.next()) {
                for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
                    System.out.print(rs.getObject(i + 1) + "\t");
                }
                System.out.println();
            }
        }
    }
}

4. Use the DBCP connection pool

Class.forName("com.alibaba.polardbx.Driver");

final BasicDataSource dataSource = new BasicDataSource();
dataSource.setUrl("jdbc:polardbx://127.0.0.1:3306/");
dataSource.setUsername("root");
dataSource.setPassword("*****");
dataSource.setInitialSize(5);

try (final Connection conn = dataSource.getConnection();
    final Statement stmt = conn.createStatement()) {

    try (final ResultSet rs = stmt.executeQuery("select 1")) {
        for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
            System.out.print(rs.getMetaData().getColumnName(i + 1) + "\t");
        }
        System.out.println();
        while (rs.next()) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
                System.out.print(rs.getObject(i + 1) + "\t");
            }
            System.out.println();
        }
    }
}

Actual Demo

  • Configure the JDK environment in the test and development environments.
  • Download polardbx-connector-java-2.1.2.jar and mysql-connector-j-8.0.33.jar.
  • Write a test file and replace the address, username, and password with those of your cluster. Make sure that the network can be connected. The following code demonstrates how to connect to a PolarDB-X Standard Edition cluster and print the cluster information.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestDriver {
    public static void main(String[] args) throws Exception {
        Class.forName("com.alibaba.polardbx.Driver");

        try (final Connection conn = DriverManager.getConnection(
            "jdbc:polardbx://127.0.0.1:3306/", "root", "****");
            final Statement stmt = conn.createStatement()) {

            try (final ResultSet rs = stmt.executeQuery("select * from information_schema.alisql_cluster_global")) {
                for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
System.out.print(rs.getMetaData().getColumnName(i + 1) + "\t");
                }
                System.out.println();
                while (rs.next()) {
                    for (int i = 0; i < rs.getMetaData().getColumnCount(); ++i) {
                        System.out.print(rs.getObject(i + 1) + "\t");
                    }
                    System.out.println();
                }
            }
        }
    }
}

Compile and run the code.

$javac TestDriver.java

$ll
total 2812
-rw-r--r-- 1 chenyu.zzy users 2481560 Dec  3 17:26 mysql-connector-j-8.0.33.jar
-rw-r--r-- 1 chenyu.zzy users  384133 Dec  3 17:19 polardbx-connector-java-2.1.2.jar
-rw-r--r-- 1 chenyu.zzy users    2442 Dec  3 17:29 TestDriver.class
-rw-r--r-- 1 chenyu.zzy users    1102 Dec  3 17:29 TestDriver.java

# When running for the first time, the high-availability feature will print the obtained cluster information to the log interface.
$java -cp .:mysql-connector-j-8.0.33.jar:polardbx-connector-java-2.1.2.jar TestDriver
Dec 03, 2024 5:29:58 PM com.alibaba.polardbx.HaManager info
INFO: Backend cluster state changed to: [{"tag":"127.0.0.1:6991","host":"127.0.0.1","port":6991,"xport":34991,"paxos_port":14991,"role":"Leader","peers":[{"tag":"11.167.60.147:6992","host":"11.167.60.147","port":6992,"xport":-1,"paxos_port":14992,"role":"Follower","version":"8.0.32-X-Cluster-8.4.20-20241014","cluster_id":6990,"update_time":"2024-12-03 17:29:58 GMT+08:00"},{"tag":"11.167.60.147:6993","host":"11.167.60.147","port":6993,"xport":-1,"paxos_port":14993,"role":"Follower","version":"8.0.32-X-Cluster-8.4.20-20241014","cluster_id":6990,"update_time":"2024-12-03 17:29:58 GMT+08:00"},{"tag":"127.0.0.1:6991","host":"11.167.60.147","port":6991,"xport":34991,"paxos_port":14991,"role":"Leader","version":"8.0.32-X-Cluster-8.4.20-20241014","cluster_id":6990,"update_time":"2024-12-03 17:29:58 GMT+08:00"}],"version":"8.0.32-X-Cluster-8.4.20-20241014","cluster_id":6990,"update_time":"2024-12-03 17:29:58 GMT+08:00"}]
SERVER_ID    IP_PORT    MATCH_INDEX    NEXT_INDEX    ROLE    HAS_VOTED    FORCE_SYNC    ELECTION_WEIGHT    LEARNER_SOURCE    APPLIED_INDEX    PIPELINING    SEND_APPLIED
1    11.167.60.147:14991    264333    0    Leader    Yes    N- 9    0    264332    N- No
2    11.167.60.147:14992    264333    264334    Follower    Yes    N- 9    0    264333    Yes    No
3    11.167.60.147:14993    264333    264334    Follower    Yes    N- 1    0    264333    Yes    No

# Run again. Since the cluster information remains unchanged, the relevant information will not be printed (the cluster information will be cached to speed up the initialization).
$java -cp .:mysql-connector-j-8.0.33.jar:polardbx-connector-java-2.1.2.jar TestDriver
SERVER_ID    IP_PORT    MATCH_INDEX    NEXT_INDEX    ROLE    HAS_VOTED    FORCE_SYNC    ELECTION_WEIGHT    LEARNER_SOURCE    APPLIED_INDEX    PIPELINING    SEND_APPLIED
1    11.167.60.147:14991    264333    0    Leader    Yes    N- 9    0    264332    N- No
2    11.167.60.147:14992    264333    264334    Follower    Yes    N- 9    0    264333    Yes    No
3    11.167.60.147:14993    264333    264334    Follower    Yes    N- 1    0    264333    Yes    No
MySQL Database Developers Best practices Maven PolarDB-X Java Database Connectivity PolarDB for Xscale
0 1 0
Share on

Read previous post:

Core Technology of PolarDB-X Storage Engine | Lizard Branch Transaction Management

Read next post:

DMS + AnalyticDB Helps DingTalk AI Assistant Easily Use Intelligent Data Query

ApsaraDB

504 posts | 154 followers

You may also like

Comments

ApsaraDB

504 posts | 154 followers

Related Products

More Posts by ApsaraDB

See All