AnalyticDB:JDBC

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for MySQL cluster with a database and an account (privileged or standard)

• The cluster endpoint and port (default: 3306), available on the Cluster Information page in the AnalyticDB for MySQL console

Add the driver dependency

Download the JAR (mysql-connector-java-x.x.x.jar) and add it to CLASSPATH.

Supported driver versions

AnalyticDB for MySQL supports the following MySQL JDBC driver versions:

• 5.0 series: 5.0.2, 5.0.3, 5.0.4, 5.0.5, 5.0.7, and 5.0.8

• 5.1 series: 5.1.1, 5.1.2, 5.1.3, 5.1.4, 5.1.5, 5.1.6, 5.1.7, 5.1.8, 5.1.11, 5.1.12, 5.1.13, 5.1.14, 5.1.15, 5.1.16, 5.1.17, 5.1.18, 5.1.19, 5.1.20, 5.1.21, 5.1.22, 5.1.23, 5.1.24, 5.1.25, 5.1.26, 5.1.27, 5.1.28, 5.1.29, 5.1.31, 5.1.32, 5.1.33, and 5.1.34

• 8.0 series: all versions

Connection URL format

jdbc:mysql://<endpoint>:<port>/<db_name>?useUnicode=true&characterEncoding=UTF-8

Connect without retries

Use try-with-resources to automatically close the connection, statement, and result set:

String url = "jdbc:mysql://<endpoint>:3306/<db_name>?useUnicode=true&characterEncoding=UTF-8";

Properties props = new Properties();
props.put("user", "<account_name>");      // Privileged or standard account
props.put("password", "<account_password>");

Class.forName("com.mysql.jdbc.Driver");

try (Connection conn = DriverManager.getConnection(url, props);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM information_schema.tables")) {
    while (rs.next()) {
        System.out.println(rs.getObject(1));
    }
} catch (ClassNotFoundException | SQLException e) {
    e.printStackTrace();
}

Replace <endpoint>, <db_name>, <account_name>, and <account_password> with your actual values.

Connect with retries

For production use, implement retry logic to handle transient connection failures:

public static final int MAX_QUERY_RETRY_TIMES = 3;
public static Connection conn = null;
public static Statement statement = null;
public static ResultSet rs = null;

public static void main(String[] args) throws ClassNotFoundException {
    String yourDB = "<db_name>";
    String username = "<account_name>";
    String password = "<account_password>";

    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://<endpoint>:3306/" + yourDB + "?useUnicode=true&characterEncoding=UTF-8";

    Properties connectionProps = new Properties();
    connectionProps.put("user", username);
    connectionProps.put("password", password);

    String query = "SELECT id FROM test4dmp.test LIMIT 10";
    int retryTimes = 0;

    // Retry on failure, up to MAX_QUERY_RETRY_TIMES attempts
    while (retryTimes < MAX_QUERY_RETRY_TIMES) {
        try {
            getConn(url, connectionProps);
            execQuery(query);
            break; // Query succeeded; exit the loop
        } catch (SQLException e) {
            System.out.println("SQL exception: " + e.getMessage() + ". Retrying...");
            try {
                if (conn == null || conn.isClosed()) {
                    retryTimes++;
                }
            } catch (SQLException e1) {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e2) {
                        e2.printStackTrace();
                    }
                }
            }
        }
    }
    closeResource();
}

/** Open a connection. */
public static void getConn(String url, Properties connectionProps) throws SQLException {
    conn = DriverManager.getConnection(url, connectionProps);
}

/** Run the query 10 times and print elapsed time and row count for each iteration. */
public static void execQuery(String sql) throws SQLException {
    Statement statement = null;
    ResultSet rs = null;
    statement = conn.createStatement();
    for (int i = 0; i < 10; i++) {
        long startTs = System.currentTimeMillis();
        rs = statement.executeQuery(sql);
        int cnt = 0;
        while (rs.next()) {
            cnt++;
            System.out.println(rs.getObject(1));
        }
        long endTs = System.currentTimeMillis();
        System.out.println("Elapsed: " + (endTs - startTs) + " ms | Rows: " + cnt);
        try {
            Thread.sleep(160000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
}

/** Release all connection resources. */
public static void closeResource() {
    if (rs != null) {
        try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
    if (statement != null) {
        try { statement.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
    if (conn != null) {
        try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
}

Multi-statement execution

To run multiple SQL statements consecutively, enable the multi-statement feature before executing:

SET ADB_CONFIG ALLOW_MULTI_QUERIES=true;

Also set allowMultiQueries=true in the JDBC connection URL:

jdbc:mysql://<endpoint>:3306/<db_name>?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true

FAQ

Internet access is enabled and the public IP address is reachable, but the JDBC connection fails.

Your public IP address may not be on the cluster's whitelist. Run the following command to get your public IP address, then add it to the IP address whitelist of your cluster:

curl ipinfo.io/ip