Connecting to a Standard HTAP Instance Through JDBC

You can connect to a standard HTAP instance through JDBC.

Precautions

Currently, HTAP instances only support the UTF-8 character set.

Prerequisites

You are familiar with:
- Computer basics
- Java
- JDBC knowledge

You have downloaded the official JDBC driver for MySQL or MariaDB.
You have created a standard HTAP instance.

The following dependency has been added to the pom.xml file.

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

Connecting to a Standard HTAP Instance Through JDBC

Download the SSL certificate and verify it before connecting to your instance.

Log in to the management console.
Click in the upper left corner and select a region and project.
Click in the upper left corner of the page and choose Databases > TaurusDB.
On the Instances page, locate a TaurusDB instance and click its name to access the Basic Information page.
In the navigation pane, choose HTAP Analysis.
Locate an HTAP instance and click its name to access the Basic Information page.
In the DB Instance Information area, click Download next to SSL.

Use keytool to generate a truststore file using the CA certificate.

<keytool_installation_path> ./keytool.exe -importcert -alias <MySQLCACert> -file <ca.pem> -keystore <truststore_file> -storepass <password>

**Table 1** Parameter description
Parameter	Description
<keytool_installation_path>	Bin directory in the JDK or JRE installation path, for example, C:\Program Files (x86)\Java\jdk11.0.7\bin.
<MySQLCACert>	Name of the truststore file. Set it to a name specific to the service for future identification.
<ca.pem>	Name of the downloaded CA certificate, for example, ca.pem.
<truststore_file>	Path for storing the truststore file.
<password>	Password of the truststore file.

Code example (using keytool in the JDK installation path to generate the truststore file):

Owner:  CN=MySQL_Server_8.0.22_Auto_Generated_CA_Certificate
Issuer: CN=MySQL_Server_8.0.22_Auto_Generated_CA_Certificate 
Serial number: 1
Valid from: Thu Feb 16 11:42:43 EST 2017 until: Sun Feb 14 11:42:43 EST 2027 
Certificate fingerprints:
     MD5: 18:87:97:37:EA:CB:0B:5A:24:AB:27:76:45:A4:78:C1
     SHA1: 2B:0D:D9:69:2C:99:BF:1E:2A:25:4E:8D:2D:38:B8:70:66:47:FA:ED 
     SHA256:C3:29:67:1B:E5:37:06:F7:A9:93:DF:C7:B3:27:5E:09:C7:FD:EE:2D:18:86:F4:9C:40:D8:26:CB:DA:95: A0:24
     Signature algorithm name: SHA256withRSA Subject Public Key Algorithm: 2048-bit RSA key 
     Version: 1
     Trust this certificate? [no]: y
     Certificate was added to keystore

Connect to your instance through JDBC.

jdbc:mysql://<instance_ip>:<instance_port>/<database_name>? 
requireSSL=<value1>&useSSL=<value2>&verifyServerCertificate=<value3>&trustCertificateKeyStoreUrl=file: 
<truststore_file>&trustCertificateKeyStorePassword=<password>

**Table 2** Parameter description
Parameter	Description
<instance_ip>	IP address of the HTAP instance. NOTE: If you are accessing the HTAP instance through an ECS, <instance_ip> is the private IP address of the HTAP instance. You can view the private IP address in the Network Information area on the Basic Information page. If you are accessing the HTAP instance through a proxy instance, <instance_ip> is the proxy address. You can view the proxy address on the Database Proxy page.
<instance_port>	Database port of the instance. The default value is 3306. NOTE: You can view the port in the Network Information area on the Basic Information page.
<database_name>	Database name used for connecting to the instance.
<value1>	Value of requireSSL, indicating whether the server supports SSL. It can be either of the following: true: The server supports SSL. false: The server does not support SSL. NOTE: For details about the relationship between requireSSL and other connection parameters, as well as the relationship between requireSSL and sslmode, see Table 3.
<value2>	Value of useSSL, indicating whether the client uses SSL to connect to the server. It can be either of the following: true: The client uses SSL to connect to the server. false: The client does not use SSL to connect to the server. NOTE: For details about the relationship between useSSL and other connection parameters, as well as the relationship between useSSL and sslmode, see Table 3.
<value3>	Value of verifyServerCertificate, indicating whether the client verifies the server certificate. It can be either of the following: true: The client verifies the server certificate. false: The client does not verify the server certificate. NOTE: For details about the relationship between verifyServerCertificate and other connection parameters, as well as the relationship between verifyServerCertificate and sslmode, see Table 3.
<truststore_file>	Path for storing the truststore file configured in 8.
<password>	Password of the truststore file configured in 8.

Specify the corresponding parameter for connection based on the JAR package used by the client. Examples:

mysql-connector-java-5.1.xx.jar (For connection drivers earlier than 8.0.28, use the enabledTLSProtocols parameter. For details, see Connecting Securely Using SSL.)

jdbc:mysql://<instance_ip>:<instance_port>/<database_name>? 
 requireSSL=true&useSSL=true&verifyServerCertificate=true&trustCertificateKeyStoreUrl=file: 
 <truststore_file>&trustCertificateKeyStorePassword=<password>&enabledTLSProtocols=TLSv1.2

mysql-connector-java-8.0. xx.jar (For connection drivers of 8.0.28 and later versions, use the tlsVersions parameter.)

jdbc:mysql://<instance_ip>:<instance_port>/<database_name>? 
 requireSSL=true&useSSL=true&verifyServerCertificate=true&trustCertificateKeyStoreUrl=file: 
 <truststore_file>&trustCertificateKeyStorePassword=<password>&tlsVersions=TLSv1.2

**Table 3** Relationship between connection parameters and sslmode
useSSL	requireSSL	verifyServerCertificate	sslMode
false	N/A	N/A	DISABLED
true	false	false	PREFERRED
true	true	false	REQUIRED
true	N/A	true	VERIFY_CA

Code example (Java code for connecting to an HTAP instance):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class JDBCTest {
    static final String IP = "*.*.*.*"; //IP address of the instance
    static final String USER = "***"; //Username
    static final String PASS = "***"; //Password
    public static void main(String[] args) {
        System.out.println("JDBCTest.main");
        Connection conn = null;
        Statement stmt = null;

        //sslMode:VERIFY_CA
        String url = "jdbc:mysql://" + IP + ":<port>/<database>?useSSL=true&verifyServerCertificate=true&&enabledTLSProtocols=TLSv1.2,TLSv1.3&trustCertificateKeyStoreUrl=file:<truststore_file>&trustCertificateKeyStorePassword=<password>";
        //sslMode:PREFERRED
        //String url = "jdbc:mysql://" + IP + ":<port>/<database>?requireSSL=false&useSSL=true&verifyServerCertificate=false&enabledTLSProtocols=TLSv1.2,TLSv1.3";
        //sslMode:REQUIRED
        //String url = "jdbc:mysql://" + IP + ":<port>/<database>?requireSSL=true&useSSL=true&verifyServerCertificate=false&enabledTLSProtocols=TLSv1.2,TLSv1.3";
        try {

            //Class.forName("com.mysql.jdbc.Driver");
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, USER, PASS);
            stmt = conn.createStatement();
            String sql = "show databases;";
            ResultSet rs = stmt.executeQuery(sql);
            int columns = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= columns; i++) {
                System.out.print(rs.getMetaData().getColumnName(i));
                System.out.print("\t");
            }
            while (rs.next()) {
                System.out.println();
                for (int i = 1; i <= columns; i++) {
                    System.out.print(rs.getObject(i));
                    System.out.print("\t");
                }
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // release resource ....
        }
    }
}

The JDBC URL format in code is as follows:

jdbc:mysql://<instance_ip>:<instance_port>/<database_name>

**Table 4** Parameter description
Parameter	Description
<instance_ip>	IP address of an FE node in the HTAP instance. If a proxy instance is created, use the IP address of the proxy instance.
<instance_port>	HTAP instance port. The default value is 3306.
<database_name>	Database name used for connecting to the instance.

Code example (Java code for connecting to an HTAP instance):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
public class JDBCTest {
    static final String IP = "*.*.*.*"; //IP address of the instance
    static final String USER = "***"; //Username
    static final String PASS = "***"; //Password
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        String url = "jdbc:mysql://" + IP + ":<port>/<database>";
        try {
            
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, USER, PASS);
            stmt = conn.createStatement();
            String sql = "show databases;";
            ResultSet rs = stmt.executeQuery(sql);
            int columns = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= columns; i++) {
                System.out.print(rs.getMetaData().getColumnName(i));
                System.out.print("\t");
            }
            while (rs.next()) {
                System.out.println();
                for (int i = 1; i <= columns; i++) {
                    System.out.print(rs.getObject(i));
                    System.out.print("\t");
                }
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // release resource ....
        }
    }
}

Parent Topic: Connecting to a Standard HTAP Instance

Previous topic: Connecting to a Standard HTAP Instance Through DAS

Next topic: Instance Management