# JDBC Usage Guide

---------------------------------------------------------------------------

### Obtain the JDBC driver.
Reference: https://support.huaweicloud.com/intl/en-us/mgtg-dws/dws_01_0032.html

Supported Open Source Driver Versions

GaussDB(DWS) supports open-source JDBC driver PostgreSQL JDBC 9.3-1103 or later.

Method 1

1. Log in to the GaussDB(DWS) management console.
2. In the navigation pane, choose "Connections".
3. In the "Driver" area, select a driver.
4. Select "DWS JDBC Driver" and click "Download" to download the JDBC driver matching the current cluster version.
5. The driver package name is "dws_8.1.x_jdbc_driver.zip". After the package is decompressed, there will be two JAR packages
  gsjdbc4.jar and gsjdbc200.jar.
    
Note:
gsjdbc4.jar: The gsjdbc4.jar driver package is compatible with PostgreSQL. Its class names and class structures are the same as those of the PostgreSQL driver.
Applications that run in PostgreSQL can be directly migrated to the current system.

gsjdbc200.jar: If a JVM process needs to access PostgreSQL and GaussDB(DWS) at the same time,
this driver package must be used. In this package, the main class name is com.huawei.gauss200.jdbc.Driver
        (that is, org.postgresql is replaced with com.huawei.gauss200.jdbc).
        The URL prefix of the database connection is jdbc:gaussdb. Other parameters are the same as those of gsjdbc4.jar.

Method 2

Downloading the SDK Package by Configuring the Maven Repository
https://mvnrepository.com/artifact/com.huaweicloud.dws/huaweicloud-dws-jdbc

Copy the Maven repository information and add it to the pom.xml file. For example, add the following code configuration to the pom.xml file:
If the version of the GaussDB(DWS) driver package downloaded from the Maven repository is 8.x.x, it is the same as gsjdbc4. If the version is 8.x.x-200, it is the same as gsjdbc200.jar.

gsjdbc4.jar
    
    <dependency>
        <groupId>com.huaweicloud.dws </groupId>
        <artifactId>huaweicloud-dws-jdbc</artifactId>
        <version>8.1.0</version>
    </dependency>

gsjdbc200.jar

    <dependency>
        <groupId>com.huaweicloud.dws</groupId>
        <artifactId>huaweicloud-dws-jdbc</artifactId>
        <version>8.1.1.1-200</version>
    </dependency>

---------------------------------------------------------------------------

### Using a JDBC Driver to Connect to a Database
Reference: https://support.huaweicloud.com/intl/en-us/mgtg-dws/dws_01_0077.html

Prerequisites
    
JDK 1.6 or later has been installed, and environment variables have been configured.
You have downloaded the JDBC driver. For details, see "Downloading JDBC".
GaussDB(DWS) supports open-source JDBC driver PostgreSQL JDBC 9.3-1103 or later.

#### **1. Loading the Driver**
      
The following methods are supported:

Implicitly load Class.forName("org.postgresql.Driver").
    During the JVM startup, transfer the driver as a parameter to the JVM: java -Djdbc.drivers=org.postgresql.Driver jdbctest
    
Note:
    
If gsjdbc200.jar is used, the main class name is "com.huawei.gauss200.jdbc.Driver".

#### **2. Setting Up a Connection**
    
Call the DriverManager.getConnection() method of JDBC to connect to GaussDB(DWS) databases.
The JDBC API does not provide the connection retry capability. You need to implement the retry processing in the service code.

DriverManager.getConnection() method:

    DriverManager.getConnection(String url);
    DriverManager.getConnection(String url, Properties info);
    DriverManager.getConnection(String url, String user, String password);

url: database connection descriptor, which can be viewed on the management console. For details, see "Obtaining the Cluster Connection Address".
Obtaining the Cluster Connection Address: https://support.huaweicloud.com/intl/en-us/mgtg-dws/dws_01_0033.html

    jdbc:postgresql:database
    jdbc:postgresql://host/database
    jdbc:postgresql://host:port/database
    jdbc:postgresql://host:port[,host:port][...]/database 
   
Note:

If gsjdbc200.jar is used, change jdbc:postgresql to jdbc:gaussdb.

database: name of the database to which you want to connect

host: name or IP address of the database server
IP address for connecting to the cluster on the GaussDB(DWS) management console. Determine the IP address based on the network environment.
If the server connected to GaussDB(DWS) is on the same network as the GaussDB (DWS) cluster, select the internal IP address. If the server is not on the same network as the GaussDB(DWS) cluster, select the public IP address.

port: port number of the database server By default, the database running on port 8000 of the local server is connected.
Multi-IP port configuration is supported. JDBC automatically implements load balancing. The multi-IP port configuration is implemented by Random access + Failover.
In this process, the system automatically ignores unreachable IP addresses. IP addresses are separated using commas (,).

Example: 
   
    jdbc:postgresql://10.10.0.13:8000,10.10.0.14:8000/database

#### Database Connection Properties

If JDBC is used to connect to the cluster, only JDBC connection parameters can be specified in the cluster connection address. Variables cannot be added.

Common attributes are described as follows:

Name | Type | Desc
---|---|---
user | string | indicating the database user who creates the connection
password | string  | indicating the password of a database user
ssl | Boolean | It indicates whether to use the SSL connection.
prepareThreshold | integer | It indicates the number of PreparedStatement executions required before requests are converted to prepared statements in servers.The default value is 5.
batchMode | boolean | It indicates whether to connect the database in batch mode.
fetchsize | integer | It indicates the default fetch size for statements in the created connection.
ApplicationName | string | It indicates an application name. The default value is PostgreSQL JDBC Driver.
allowReadOnly | boolean | It indicates whether to enable the read-only mode for connection. The default value is false. If the value is not changed to true,the execution of connection.setReadOnly does not take effect.
blobMode | string | It is used to set the setBinaryStream method to assign values to different data types. If this parameter is set to on, values are assigned to the BLOB data type.If this parameter is set to off, values are assigned to the bytea data type. Default value: on
connectionExtraInfo | boolean | This parameter indicates whether the JDBC driver reports the driver deployment path and process owner to the database.

Note:

The value can be true or false. Default value: true. If connectionExtraInfo is set to true, the JDBC driver records the deployment path and process owner of the current driver in the connection_info of the database.
The information can be queried in PG_STAT_ACTIVITY and PGXC_STAT_ACTIVITY.

---------------------------------------------------------------------------
FAQ

Reference: https://support.huaweicloud.com/intl/en-us/trouble-dws/dws_09_0100.html

"Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections" Is Reported When the Client Connects to the Database

    Possible causes: The network between the client and server is disconnected, the port is incorrect, or the CN to be connected is abnormal.
    
    Solution:
    
    On the client, ping the IP address of the server to check whether the network connection is normal. If the network connection is abnormal, rectify the network fault.
    Check whether the correct port is specified in the URL to connect to the CN. If the port is incorrect, set a correct one.
    Log in to the server where the CN is located and run the cm_ctl -vC command to check whether the status of the CN to be connected is normal.

"FATAL: Invalid username/password,login denied" Is Reported When the Client Connects to the Database

    Possible cause: The username or password is incorrect.
    
    Solution: Correct the password for connecting to the database.

"No suitable driver found for XXXX" Is Reported When the Client Connects to the Database
    
    Possible cause: The URL format is incorrect.
    
    Solution: Correct the URL format.
    
    For gsjdbc4.jar, the format is jdbc:postgresql://host:port/database.
    For gsjdbc200.jar, the format is jdbc:gaussdb://host:port/database.

