This topic describes how to use MySQL Java Database Connectivity (JDBC) to connect to an AnalyticDB for MySQL cluster.

Supported MySQL JDBC 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.
  • MySQL 8.0.

Precautions

To create MySQL JDBC connections in Java, you must add the MySQL JDBC driver package to your project. You must add the path of the mysql-connector-java-x.x.x.jar file to the value of the CLASSPATH variable in your project. Otherwise, you cannot create a MySQL JDBC connection.

Sample code for creating a MySQL JDBC connection without retries

To connect to AnalyticDB for MySQL databases through MySQL JDBC, you can add the following Java code to your business system:

Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
    Class.forName("com.mysql.jdbc.Driver");
    // adb_url: the endpoint of the AnalyticDB for MySQL cluster to which you want to connect. You can obtain the endpoint on the Cluster Information page of the AnalyticDB for MySQL console. The default port number is 3306.
    // db_name: the name of the database in the AnalyticDB for MySQL cluster.
    String url = "jdbc:mysql://adb_url:3306/db_name? useUnicode=true&characterEncoding=UTF-8";
    Properties connectionProps = new Properties();
    // account_name: the account used to connect to the AnalyticDB for MySQL cluster. There are two types of accounts: privileged and standard.
    connectionProps.put("user", "account_name");
    // account_password: the password of the account used to connect to the AnalyticDB for MySQL cluster.
    connectionProps.put("password", "account_password");
    connection = DriverManager.getConnection(url, connectionProps);
    statement = connection.createStatement();
    String query = "select count(*) from information_schema.tables";
    rs = statement.executeQuery(query);
    while (rs.next()) {
        System.out.println(rs.getObject(1));
    }
} catch (ClassNotFoundException e) {
    e.printStackTrace();
} catch (SQLException e) {
    e.printStackTrace();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    if (rs ! = null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (statement ! = null) {
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (connection ! = null) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
			

Sample code for creating a MySQL JDBC connection with retries

When you create a MySQL JDBC connection, you can configure the following parameters to implement a retry mechanism:

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 {
    // db_name: the name of the database in the AnalyticDB for MySQL cluster.
    String yourDB = "db_name";  
    // account_name: the account used to connect to the AnalyticDB for MySQL cluster. There are two types of accounts: privileged and standard.
    String username = "account_name";  
    // account_password: the password of the account used to connect to the AnalyticDB for MySQL cluster.
    String password = "account_password"; 
    Class.forName("com.mysql.jdbc.Driver");
    // adb_url: the endpoint of the AnalyticDB for MySQL cluster to which you want to connect. You can obtain the endpoint on the Cluster Information page of the AnalyticDB for MySQL console. The default port number is 3306.
    String url = "jdbc:mysql://adb_url: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;
    // Run automatic retries through loops.
    while (retryTimes < MAX_QUERY_RETRY_TIMES) {
        try {
            getConn(url, connectionProps);
            execQuery(query);// Run a query.
            break; // If the query is run, exit the loop.
        } catch (SQLException e) {
            System.out.println("Met SQL exception: " + e.getMessage() + ", then go to retry task ...");
            try {
                if (conn == null || conn.isClosed()) {
                    retryTimes++;
                }
            } catch (SQLException e1) {
                if (conn ! = null) {
                    try {
                        conn.close();
                    } catch (SQLException e2) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
    // Clear connection resource.
    closeResource();
}
/**
 * Get connection.
 *
 * @param url
 * @param connectionProps
 * @throws SQLException
 */
public static void getConn(String url, Properties connectionProps) throws SQLException {
    conn = DriverManager.getConnection(url, connectionProps);
}
/**
 * Query task execution logic.
 *
 * @param sql
 * @throws SQLException
 */
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("Elapse Time: " + (endTs - startTs));
        System.out.println("Row count: " + cnt);
        try {
            Thread.sleep(160000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
}
/**
 * Close connection resource.
 */
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();
        }
    }
}