Connecting to a Database

After a database is connected, it can be used to run SQL statements to operate data.

Function Prototype

JDBC provides three database connection methods.

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

Parameters

**Table 1** Database connection parameters
Parameter	Description
url	gaussdbjdbc.jar database connection descriptor. If host is set to a server name or an IPv4 address, formats are as follows: jdbc:gaussdb: (If the database name is left empty, the username is used.) jdbc:gaussdb:database jdbc:gaussdb://host/database jdbc:gaussdb://host:port/database jdbc:gaussdb://host:port/database?param1=value1&param2=value2 jdbc:gaussdb://host1:port1,host2:port2/database?param1=value1&param2=value2 If host is set to an IPv6 address, formats are as follows: jdbc:gaussdb: (If the database name is left empty, the username is used.) jdbc:gaussdb:database jdbc:gaussdb://host/database or jdbc:gaussdb://[host]/database jdbc:gaussdb://[host]:port/database jdbc:gaussdb://[host]:port/database?param1=value1&param2=value2 jdbc:gaussdb://[host1]:port1,[host2]:port2/database?param1=value1&param2=value2 NOTE: database indicates the name of the database to connect. host indicates the name or IP address of the database server. Both IPv4 and IPv6 addresses are supported. For security purposes, the database CN forbids access from other nodes in the cluster without authentication. To access the CN from inside the cluster, deploy the JDBC program on the host where the CN is located and set host to 127.0.0.1. Otherwise, the error message "FATAL: Forbid remote connection with trust method!" may be displayed. It is recommended that the service system be deployed outside the cluster. If it is deployed inside, database performance may be affected. By default, the local host is used to connect to the server. port indicates the port number of the database server. By default, the database on port 5432 of the local host is connected. If host is set to an IPv6 address and the port number is specified in the URL, use square brackets ([]) to enclose the IP address. The format is [IP address]:Port number. param indicates a database connection attribute. The parameter can be configured in the URL. The URL starts with a question mark (?), uses an equal sign (=) to assign a value to the parameter, and uses an ampersand (&) to separate parameters. You can also use the attributes of the info object for configuration. For details, see Examples. value indicates the database connection attribute values. The connectTimeout and socketTimeout parameters must be set for connection. If they are not set, the default value 0 is used, indicating that the connection will not time out. When the network between the DN and client is faulty, the client does not receive the ACK packet from the DN. In this case, the client starts the timeout retransmission mechanism to continuously retransmit packets. A timeout error is reported only when the timeout interval reaches the default value 600s. As a result, the RTO is high. You are advised to ensure the validity of the URL when using the standard JDBC API to establish a connection. An invalid URL may cause an exception, and the exception contains the original URL character string, which may cause sensitive information leakage.
info	Database connection attributes (all attributes are case sensitive). Common attributes are described as follows: PGDBNAME: string type. This parameter specifies the database name. You do not need to set this parameter in the URL because the database name is automatically parsed from the URL. PGHOST: string type. It specifies the host IP address. Both IPv4 and IPv6 addresses are supported. Use colons (:) to separate IP addresses and port numbers, and use commas (,) to separate multiple CNs. (This parameter does not need to be set in the URL. The system automatically parses the URL to obtain its value.) For details, see Examples. PGPORT: integer type. It specifies the host port number. Use colons (:) to separate IP addresses and port numbers, and use commas (,) to separate multiple CNs. (This parameter does not need to be set in the URL. The system automatically parses the URL to obtain its value.) For details, see Examples. user: string type. It specifies the database user who creates the connection. password: string type. It specifies the password of the database user. enable_ce: string type. enable_ce=1 indicates that JDBC supports the basic capability of encrypted equality query. enable_ce=3 indicates that software and hardware integration is supported based on the encrypted equality query capability. key_info: string type. This parameter is used together with enable_ce to set parameters for accessing an external key manager in an encrypted database. refreshClientEncryption: string type. The default value is NULL. If refreshClientEncryption is set to 1 (default value), the encrypted database supports cache update on the client. loggerLevel: string type. The default value is NULL (disabled). The following log levels are supported: OFF, INFO, DEBUG, and TRACE. OFF indicates that the log function is disabled. INFO, DEBUG, and TRACE logs record information of different levels. loggerFile: string type. It specifies the log output path (directory and file name). You need to specify the log directory and file name. If no directory is specified, log files are generated in the directory where the client program is running. If the path is not configured or the configured path does not exist, logs are output through streams by default. This parameter has been discarded and does not take effect any more. It is used to specify the path for exporting monitoring logs only when the connection monitoring function is enabled. To export JDBC logs to a specified path, configure it in the java.util.logging attribute file or system attributes. logger: string type. It indicates the log output framework used by the JDBC driver. The JDBC driver supports the log output framework used for interconnecting with user applications. Currently, only the Slf4j-API-based third-party log output framework is supported. For details, see 6.2.9 Log Management. If it is left empty or is set to JDK LOGGER, JDK LOGGER is used. Otherwise, the slf4j-API-based third-party log framework must be used. allowEncodingChanges: Boolean type. The default value is false. If this parameter is set to true, the character set type can be changed. This parameter is used together with characterEncoding to set the character set. The two parameters are separated by ampersands (&). The value of characterEncoding can be UTF8, GBK, LATIN1, GB18030, GB18030_2022, or ZHS16GBK. Example: allowEncodingChanges=true&characterEncoding=UTF8. NOTE: The ZHS16GBK character set supports the euro sign. To display the euro sign, set the character set to ZHS16GBK. currentSchema: string type. You need to specify a schema in search-path. If the schema name contains special characters except letters, digits, and underscores (_), you are advised to enclose the schema name in quotation marks. Note that the schema name is case sensitive after quotation marks are added. If multiple schemas need to be configured, separate them with commas (,). Schemas containing special characters also need to be enclosed in quotation marks. Example: currentSchema=schema_a,"schema-b","schema/c". loadBalanceHosts: Boolean type. In the default mode (disabled), multiple hosts specified in the URL are connected in sequence. If load balancing is enabled, the shuffle algorithm is used to randomly select a host from the candidate hosts to establish a connection. autoBalance: string type. If this parameter is set to true, balance, or roundrobin, the JDBC load balancing function is enabled to balance multiple connections of an application to each CN available in the database cluster. Example: jdbc:gaussdb://host1:port1,host2:port2/database?autoBalance=true. JDBC periodically obtains the list of available CNs in the entire cluster, for example, the obtained list is host1:port1,host2:port2,host3:port3,host4:port4. The refreshCNIpListTime parameter specifies the interval for obtaining the list, and the default interval is 10 seconds. Hosts obtained from the CN list are data IP addresses. When autoBalance is enabled on host1 and host2, HA is implemented only for the first connection. The driver will select available CNs from host1, host2, host3, and host4 in sequence to update the available CN list and new connections will be established on host1, host2, host3, and host4 using the RoundRobin algorithm. priorityn indicates that the JDBC-based load balancing function is enabled. Multiple connections of an application are balanced to the first n available CNs configured in the URL. When the first n CNs are unavailable, connections are randomly allocated to other available CNs in the database cluster. n is a number not less than 0 and less than the number of CNs configured in the URL. When parseCandidatesByDomain is set to true, the priority configuration of priorityn does not take effect. In this scenario, the priority is specified by priorityDomains. When priorityn is used for load balancing, the value of host cannot be IPv6. Example: jdbc:gaussdb://host1:port1,host2:port2,host3:port3,host4:port4/database?autoBalance=priority2. JDBC periodically obtains the list of available CNs in the entire cluster (defined by refreshCNIpListTime). For example, the obtained list is host1:port1,host2:port2,host3:port3,host4:port4,host5:port5,host6:port6, where host1 and host2 are in AZ1, and host3 and host4 are in AZ2. The driver preferentially selects host1 and host2 for load balancing. If both host1 and host2 are unavailable, the driver randomly selects a CN from host3, host4, host5, and host6 for connection. If this parameter is set to shuffle, JDBC random load balancing is enabled to randomly and evenly distribute multiple connections of the application to available CNs in the database cluster. Example: jdbc:gaussdb://host1:port1,host2:port2,host3:port3/database?autoBalance=shuffle. JDBC periodically obtains the list of available CNs in the entire cluster. For example, the obtained list is host1:port1,host2:port2,host3:port3,host4:port4. The refreshCNIpListTime parameter specifies the interval for obtaining the list, and the default interval is 10 seconds. For the first connection, host1:port1,host2:port2,host3:port3 is used for HA. For subsequent connections, the shuffle algorithm is used to randomly select a CN from the refreshed CN list. If this parameter is set to specified, the JDBC load balancing function is enabled and connections are established only on nodes specified in the user URL. Example: jdbc:postgresql://host1:port1,host2:port2,host3:port3,host4:port4/database?autoBalance=specified&priorityServers=2. JDBC identifies host1 and host2 as the connection nodes in the primary cluster, and host3 and host4 as the connection nodes in the standby cluster. First, connections are evenly established on host1 and host2. If all nodes are faulty, connections are not established on the remaining nodes in the cluster. After the standby node becomes the primary node, connections are evenly established on host3 and host4. If all nodes are faulty, connections are not established on the remaining nodes in the cluster. If this parameter is set to false, the JDBC load balancing and priority-based load balancing functions are disabled. The default value is false. CAUTION: Load balancing is based on the connection level rather than the transaction level. If the connection is persistent and the load on the connection is unbalanced, the load on the CN may be unbalanced. Load balancing can be used only in distributed scenarios and cannot be used in centralized scenarios. If the load balancing function is enabled when the DR cluster is connected, query requests are sent to the primary cluster. As a result, the query performance is affected. When load balancing is enabled, the floating IP address or data IP address can be configured in the URL. If the floating IP address is configured, the system obtains the corresponding data IP address based on the floating IP address and performs load balancing based on the obtained data IP address. Therefore, when configuring the floating IP address or data IP address in the URL, ensure that the network connection of the data IP address is normal. Otherwise, the load balancing function is abnormal. refreshCNIpListTime: integer type. This parameter specifies the interval at which JDBC periodically checks the status of CNs in the database cluster and obtains the IP address list of available CNs. The default value is 10 seconds. hostRecheckSeconds: integer type. After JDBC attempts to connect to a host, the host status is saved: connection success or connection failure. This status is trusted within the duration specified by hostRecheckSeconds. After the duration expires, the status becomes invalid. The default value is 10 seconds. ssl: Boolean type. It specifies a connection in SSL mode. When ssl is set to true, the NonValidatingFactory channel and certificate mode are supported. 1. For the NonValidatingFactory channel, configure the username and password and set SSL to true. 2. In certification mode, configure the client certificate, key, and root certificate, and set SSL to true. sslmode: string type. It specifies the SSL authentication mode. The value can be disable, allow, prefer, require, verify-ca, or verify-full. disable: SSL connection is disabled. allow: If the database server requires SSL connection, SSL connection can be enabled. However, authenticity of the database server will not be verified. prefer: If the database supports SSL connection, SSL connection is preferred. However, authenticity of the database server will not be verified. require: The system only attempts to set up an SSL connection. It neither checks whether the server certificate is issued by a trusted CA, nor checks whether the host name of the server is the same as that in the certificate. verify-ca: attempts to set up an SSL connection and checks whether the server certificate is issued by a trusted CA. verify-full: The system attempts to set up an SSL connection, checks whether the server certificate is issued by a trusted CA, and checks whether the host name of the server is the same as that in the certificate. sslcert: string type. It specifies the complete path of the certificate file. The type of the client and server certificates is End Entity. sslkey: string type. It specifies the complete path of the key file. You need to convert the client certificate to the DER format. For details, see Connecting to a Database (Using SSL). sslrootcert: string type. It specifies the name of the SSL root certificate. The root certificate type is CA. sslpassword: string type. It is provided for ConsoleCallbackHandler. sslpasswordcallback: string type. It specifies the class name of the SSL password provider. Default value: com.huawei.gaussdb.jdbc.ssl.jdbc4.LibPQFactory.ConsoleCallbackHandler. sslfactory: string type. It specifies the class name used by SSLSocketFactory to establish an SSL connection. sslprivatekeyfactory: string type. The provided value is the fully qualified name of the implementation class of the com.huawei.gaussdb.jdbc.ssl.PrivateKeyFactory API that implements the private key decryption method. If it is not provided, try the default JDK private key decryption algorithm. If the decryption fails, use com.huawei.gaussdb.jdbc.ssl.BouncyCastlePrivateKeyFactory. You need to provide the bcpkix-jdk15on.jar package. The recommended version is 1.65 or later. sslfactoryarg: string type. The value is an optional parameter of the constructor function of the sslfactory class. (This parameter is not recommended.) sslhostnameverifier: string type. It specifies the class name of the host name verifier. The API must implement javax.net.ssl.HostnameVerifier. The default value is com.huawei.gaussdb.jdbc.ssl.PGjdbcHostnameVerifier. loginTimeout: integer type. It specifies the waiting time for establishing the database connection, in seconds. When multiple IP addresses are configured in the URL, if the time for obtaining the connection exceeds this value, the connection fails and the subsequent IP addresses are not tried. The default value is 0 (disabled). connectTimeout: integer type. It specifies the timeout interval for connecting to a server. If the time taken to connect to a server exceeds the value specified, the connection is interrupted. The unit of the timeout interval is second. The value 0 indicates that the timeout mechanism does not take effect. When multiple IP addresses are configured in the URL, this parameter indicates the timeout interval for connecting to a single IP address. The default value is 0. socketTimeout: integer type. It specifies the timeout interval for a socket read operation. If the time taken to read data from a server exceeds the value specified, the connection is closed. The unit of the timeout interval is second. The value 0 indicates that the timeout mechanism does not take effect. The default value is 0. If this parameter is not set, the client waits for a long time when the database process is abnormal. You are advised to set this parameter based on the SQL execution time acceptable to services. When the timeout is triggered on the JDBC and the connection is closed, the running services delivered by the JDBC to the database are forcibly terminated. This capability is controlled by the GUC parameter check_disconnect_query. If this parameter is set to on, the capability is supported. If this parameter is set to off, the capability is not supported. socketTimeoutInConnecting: integer type. It specifies the timeout interval for a socket read operation during the connection establishment. If the time taken to read data from the server exceeds this value, it searches for the next node for connection. The unit of the timeout interval is second. The default value is 5s. statementTimeout: integer type. It specifies the timeout interval for executing a statement in a connection. If the execution time of a statement exceeds this value, the statement execution is canceled. The unit of the timeout interval is millisecond. The default value 0 indicates that the timeout mechanism is disabled. cancelSignalTimeout: integer type. A cancel message may cause a block. This attribute controls connect timeout and socket timeout in a cancel command. If the cancel command does not respond within the specified time, the connection is interrupted to reduce the occupation of client resources. The unit of the timeout interval is second. The default value is 10 seconds. tcpKeepAlive: Boolean type. It is used to enable or disable TCP keepalive detection. The default value is false. logUnclosedConnections: Boolean type. The default value is false. The client may leak a connection object because it does not call the connection object's close() method. These objects will be collected as garbage and finalized using the finalize() method. After it is set to true, if the caller ignores this operation, this method closes the connection. assumeMinServerVersion (discarded): string type. This parameter indicates the version of the server to connect. ApplicationName: string type. This parameter specifies the name of the application that is being connected. You can query the pgxc_stat_activity table on the CN to view information about the client that is being connected. The name is displayed in the application_name column. The default value is GaussDB JDBC Driver. connectionExtraInfo: Boolean type. It specifies whether the driver reports the driver deployment path, process owner, and URL connection configuration information to the database. The value can be true or false. The default value is false. If connectionExtraInfo is set to true, the JDBC driver reports the driver deployment path, process owner, and URL connection configuration information to the database and displays the information in the connection_info parameter. In this case, you can query the information from PG_STAT_ACTIVITY or PGXC_STAT_ACTIVITY. autosave: string type. The value can be always, never, or conservative. It specifies the action that the driver should perform upon a query failure. If autosave is set to always, the JDBC driver sets a savepoint before each query and rolls back to the savepoint if the query fails. If autosave is set to never, there is no savepoint. If autosave is set to conservative, a savepoint is set for each query. However, the system rolls back and retries only when there is an invalid statement. The default value is never. protocolVersion: integer type. It specifies the connection protocol version. Only versions 1 and 3 are supported. If it is set to 1, only the V1 server is connected. If it is set to 3, MD5 encryption is used. You must need to set the GUC parameter password_encryption_type to 1 to change the database encryption mode. After the cluster is restarted, create a user who uses MD5 encryption to encrypt passwords. You must also change the client connection mode to md5 in the gs_hba.conf file. Log in to the system as a new user. (You are advised not to set this value because the MD5 encryption algorithm has lower security and poses security risks.) NOTE: The MD5 encryption algorithm has lower security and poses security risks. Therefore, you are advised to use a more secure encryption algorithm. prepareThreshold: integer type. It specifies the number of times that the PreparedStatement object is executed before the prepared statement on the server is used. The default value is 5, indicating that when the same PreparedStatement object is executed for five or more times, the parse message is not sent to the server to parse the statement. Instead, the statement that has been parsed on the server is used. preparedStatementCacheQueries: integer type. This parameter specifies the maximum number of queries generated by the cache statement object of each connection. The default value is 256. If the number of queries generated by the statement object is greater than 256, the least recently used queries will be discarded from the cache. The value 0 indicates that the cache function is disabled. preparedStatementCacheSizeMiB: integer type. This parameter indicates the maximum size of queries generated by the cache statement object of each connection, in MB. The default value is 5. If the size of the cached queries exceeds 5 MB, the least recently used query cache will be discarded. The value 0 indicates that the cache function is disabled. databaseMetadataCacheFields: integer type. The default value is 65536. It specifies the maximum number of columns that can be cached in each connection. The value 0 indicates that the cache function is disabled. databaseMetadataCacheFieldsMiB: integer type. The default value is 5. This parameter indicates the maximum size of columns that can be cached in each connection, in MB. The value 0 indicates that the cache function is disabled. stringtype: string type. The options are unspecified and varchar. It specifies the type of the PreparedStatement parameter used by the setString() method. If stringtype is set to VARCHAR (default value), these parameters are sent to the server as varchar parameters. If stringtype is set to unspecified, these parameters are sent to the server as an untyped value, and the server attempts to infer their appropriate type. batchMode: string type. It specifies whether to connect the database in batch mode. The default value is on, indicating that the batch mode is enabled. If batchMode is set to on, the returned result is [count, 0, 0...0]. The first element in the array is the total number of records affected in batches. If batchMode is set to off, the returned result is [1, 1, 1...1]. Each element in the array corresponds to the number of affected records in a single modification. fetchsize: integer type. It specifies the default fetchsize for statements in the created connection. The default value is 0, indicating that all results are obtained at a time. It has the same function as defaultRowFetchSize. If they are set at the same time, fetchsize prevails. reWriteBatchedInserts: Boolean type. The default value is false. During batch import, set this parameter to true to combine N insert statements into one: insert into TABLE_NAME values(values1, ..., valuesN), ..., (values1, ..., valuesN). To use this parameter, set batchMode to off. unknownLength: integer type. The default value is Integer.MAX_VALUE. This parameter specifies the length of the unknown length type when the data of some GaussDB types (such as TEXT) is returned by functions such as ResultSetMetaData.getColumnDisplaySize and ResultSetMetaData.getPrecision. defaultRowFetchSize: integer type. It specifies the number of rows read by fetch in ResultSet at a time. Limiting the number of rows read each time in a database access request can avoid unnecessary memory consumption, thereby avoiding out of memory exception. The default value is 0, indicating that all rows are obtained at a time in ResultSet. This parameter cannot be set to a negative value. binaryTransfer: Boolean type. It specifies whether data is sent and received in binary format. The default value is false. binaryTransferEnable: string type. It specifies types for which binary transmission is enabled. Every two types are separated by commas (,). You can select either the OID or name, for example, binaryTransferEnable=INT4_ARRAY,INT8_ARRAY. For example, if the name is BLOB and the OID is 88, set binaryTransferEnable to BLOB or 88. binaryTransferDisable: string type. It specifies types for which binary transmission is disabled. Every two types are separated by commas (,). You can select either the OID or name. It overwrites the setting of binaryTransferEnable. blobMode: string type. It is used to set the data type of parameters bound to the setBinaryStream method. If the value is on, the data type is blob. If the value is off, the data type is bytea. The default value is on. You are advised to set this parameter to on for systems migrated from ORA and MySQL and to off for systems migrated from PG. socketFactory: string type. It specifies the name of the class used to create a socket connection with the server. This class must implement the javax.net.SocketFactory API and define a constructor with no parameter or a single string parameter. socketFactoryArg: string type. The value is an optional parameter of the constructor function of the socketFactory class and is not recommended. receiveBufferSize: integer type. It is used to set SO_RCVBUF on the connection stream. sendBufferSize: integer type. It is used to set SO_SNDBUF on the connection stream. preferQueryMode: string type. The value can be "extended", "extendedForPrepared", "extendedCacheEverything", or "simple". This parameter specifies the query mode. The default value is extended. In simple mode, only the Q message in text mode can be sent. The parse and bind messages are not supported. In extended mode, parse, bind, and execute messages are used. In extendedForPrepared mode, only the prepared statement object uses extended query, and the statement object uses only simple query. The extendedCacheEverything mode caches the query generated by each statement object. ApplicationType: string type. The value can be "not_perfect_sharding_type" or "perfect_sharding_type". It specifies whether to enable distributed write and query. The default value is not_perfect_sharding_type. Distributed write and query are enabled if this parameter is set to not_perfect_sharding_type. If it is set to perfect_sharding_type, distributed write and query are disabled by default. Distributed write and query can be performed only when */ multinode /* is added to the SQL statement. This parameter is valid only when the database is in the GTM-free scenario. priorityServers: integer type. This value is used to specify the first n nodes configured in the URL as the primary cluster to be connected preferentially. The default value is NULL. The value is a number greater than 0 and less than the number of CNs configured in the URL. It is used in streaming DR scenarios. Example: jdbc:gaussdb://host1:port1,host2:port2,host3:port3,host4:port4,/database?priorityServers=2. That is, host1 and host2 are primary cluster nodes, and host3 and host4 are DR cluster nodes. usingEip: Boolean type. This value specifies whether to use an elastic IP address for load balancing. The default value is true, indicating that an elastic IP address is used for load balancing. The value false indicates that a data IP address is used for load balancing. If usingEip is set to true or left blank, the host in the URL must use an EIP. If usingEip is set to false, the host in the URL must use a data IP address. Otherwise, the priorityn load balancing policy will become invalid. traceInterfaceClass: string type. The default value is NULL, which is used to obtain the implementation class of traceId. The value is the fully qualified name of the implementation class of the com.huawei.gaussdb.jdbc.log.Tracer API that implements the method for obtaining traceId. use_boolean: Boolean type. It is used to set the OID type bound to the setBoolean method in extended mode. The default value is false, indicating that the int2 type is bound. The value true indicates that the Boolean type is bound. allowReadOnly: Boolean type. It specifies whether to enable the read-only mode for a connection. The default value is true, indicating that the read-only mode is enabled. If it is set to false, the read-only mode is disabled. In this case, calling connection.setReadOnly(true) does not take effect, and data can still be modified. TLSCiphersSupperted: string type. It is used to set the supported TLS encryption suite. The default value is TLS_DHE_RSA_WITH_AES_128_GCM_SHA256,TLS_DHE_RSA_WITH_AES_256_GCM_SHA384,TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384. stripTrailingZeros: Boolean type. The default value is false. If the value is true, trailing 0s of the numeric type are removed. It is valid only for ResultSet.getObject(int columnIndex). enableTimeZone: Boolean type. The default value is true. It specifies whether to enable the time zone setting on the server. The value true indicates that the JVM time zone is obtained to specify the database time zone. The value false indicates that the database time zone is used. enableStandbyRead: Boolean type. It specifies whether to enable the read on standby mode. The default value is false. The value true indicates that the mode is enabled, and the value false indicates that the mode is disabled. compatibilityTags: string type. The default value is NULL. It is used to restore some features of the driver to an earlier version to ensure forward compatibility. You can configure one or more tags. Each tag indicates that the corresponding forward compatibility feature is enabled. Different tags are separated by commas (,). Generally, this parameter is not recommended unless there is an obvious forward compatibility issue. Currently, the following tag is supported: typeMapInitNull: After this tag is used, the behavior of earlier driver versions is compatible. The initial value of typeMap in java.sql.Connection is NULL. If this tag is not used, the initial value is an empty map. parseCandidatesByDomain: Boolean type. Specifies whether to obtain standby nodes based on domain names. The default value is false. The value is true (enabled) or false (disabled). After this function is enabled, you need to configure the host information in the URL in the format of domain name plus port number. The driver obtains the IP address based on the domain name, generates a standby node set, and works with the autoBalance parameter for load balancing. primaryDomains: integer type. Specifies the first n domain names configured in the URL as the primary cluster to be preferentially connected. The default value is 0 (disabled). It takes effect only when parseCandidatesByDomain is set to true. A value greater than 0 and less than the number of domain names configured in the URL indicates that the function is enabled. The domain names in the URL are divided into two groups. The first group is the primary cluster, and the second group is the standby cluster. After a primary/standby switchover, the two groups switch their positions. The first group is the primary cluster, and the second group is the standby cluster. This mode applies to streaming DR and Dorado dual-cluster scenarios. Example: jdbc:gaussdb://domain1:port1,domain2:port2,domain3:port3,domain4:port4/database?parseCandidatesByDomain=true&primaryDomains=2, in which domain1 and domain2 correspond to the primary cluster and the IP addresses corresponding to domain1 and domain2 are preferentially connected. If a primary/standby switchover occurs, domain3 and domain4 are marked as the primary cluster, and connections are preferentially established to domain3 and domain4. priorityDomains: integer type. It specifies the first n domain names configured in the URL as the domain names to be preferentially connected. The default value is 0 (disabled). The function takes effect only when parseCandidatesByDomain is set to true. A value greater than 0 and less than the number of domain names configured in the URL indicates that the function is enabled. If primaryDomains is configured, the value must be less than the value of primaryDomains. Example: jdbc:gaussdb://domain1:port1,domain2:port2,domain3:port3,domain4:port4/database?parseCandidatesByDomain=true&primaryDomains=2&priorityDomains=1, in which domain1 and domain2 correspond to the primary cluster but the IP address corresponding to domain1 is preferentially connected. domain2 is the standby domain name. The system attempts to connect to domain2 only when domain1 cannot be connected. If a primary/standby switchover occurs, domain3 and domain4 are marked as the primary cluster. However, the IP address corresponding to domain3 is preferentially connected. If domain3 cannot be connected, the IP address corresponding to domain4 is connected. refreshDomainResolveTime: integer type. The default value is 10, in seconds. The minimum value is 1 and the maximum value is 2147483647. It specifies the interval for updating the domain name resolution result and takes effect only when parseCandidatesByDomain is set to true. After it takes effect, the domain name resolution update time involved in the URL is set to a specified value and the domain name resolution result is periodically updated in the asynchronous thread. Driver obtains domain name resolution results only from the DNS service in the environment where the application is located. To ensure that domain name binding changes can be detected by Driver in a timely manner, the DNS service in the environment where the application is located must ensure that the domain name binding changes take effect in a timely manner. oracleCompatible: string type. The default value is false. This is used to set the ORA-compatible features of driver APIs. The options are as follows: true or on: All ORA-compatible features of drivers are enabled. false or off: All ORA-compatible features of drivers are disabled. "tag1,tag2,tag3": Some ORA-compatible features of drivers are enabled. You can configure one or more tags separated by commas (,). Each tag corresponds to an ORA-compatible feature. Currently, the following tags are supported: getProcedureColumns: The behavior of the DatabaseMetaData#getProcedureColumns API is compatible with behavior ORA. getCallableStatementResults: The database is compatible with ORA when the getLong, getInt, getShort, and getByte APIs of CallableStatement are called. When the registered output parameter type is java.sql.Types#NUMERIC, you can call the getLong, getInt, getShort, and getByte APIs of CallableStatement to receive the value of out. The SQLException message is displayed only when the value of out exceeds the value range of the Java numeric data type. batchInsertAffectedRows: After reWriteBatchedInserts is enabled, the result returned by the Statement#executeBatch API is compatible with behavior ORA. printSqlInLog: Boolean type. It specifies whether to output SQL statements in exception information or logs. The value is true (enabled) or false (disabled). The default value is true. useGsClobBlobClass: Boolean type. The default value is false, which means that speculative execution for Reduce tasks is disabled. If this parameter is set to true, an object of the PGClob type is returned when the java.sql.ResultSet#getObject API is used to obtain the CLOB column. An object of the PGBlob type is returned when the blob field is obtained. When the metadata API java.sql.ResultSetMetaData#getColumnClassName is used to obtain the type name of a CLOB column, java.sql.Clob is returned. When the metadata API java.sql.ResultSetMetaData#getColumnClassName is used to obtain the type name of a BLOB column, java.sql.Blob is returned. If this parameter is set to false, an object of the PGClob type is returned when the java.sql.ResultSet#getObject API is used to obtain the CLOB column. An object of the byte[] type is returned when the blob field is obtained. When the metadata API java.sql.ResultSetMetaData#getColumnClassName is used to obtain the type name of a CLOB column, java.sql.CLOB is returned. When the metadata API java.sql.ResultSetMetaData#getColumnClassName is used to obtain the type name of a BLOB column, java.sql.BLOB is returned. sessionType: string type. It specifies the type of the current session. An execution plan is generated based on the actual value. The value can be singleNode, globalReadOnly, or normal. The default value is normal. normal: default value, indicating a normal session. The plan is executed based on the original logic of the database. singleNode: indicates a single-node session. The read and write operations of the session can be pushed down. In single-node session mode, a CN sends a one-to-one session to a specified target DN. This parameter must be used together with execTargetNode. Set execTargetNode to the name of the target DN. JDBC preferentially connects to the local CN corresponding to the target DN. globalReadOnly: indicates a cross-node read-only session. In this mode, distributed plans are generated normally, but distributed write operations are prohibited. execTargetNode: string type. It must be used together with sessionType. This parameter is valid only when sessionType is set to singleNode. It indicates a DN in single-node session. The value range is the names of all DNs in the current cluster. If sessionType is set to singleNode and execTargetNode is not specified (the default value is NULL), the effect is the same as that when sessionType is set to normal. executeUpdateQueryable: Boolean type. It specifies whether to enable the executeUpdate method to execute DQL statements. The value is true (enabled) or false (disabled). The default value is false. After this method is enabled, –1 is returned when the executeUpdate method executes DQL statements, and a result set can be obtained. You are advised not to enable this parameter. dbMonitor: Boolean type. It specifies whether to enable the connection monitoring function for JDBC. The default value is false. The value is true (enabled) or false (disabled). The connection monitoring function can monitor the following JDBC metrics: number of connections enabled by applications, number of connections disabled by applications, number of abnormal disconnections, database access volume, client CPU usage, memory usage, uplink and downlink transmission rates, and network delay, jitter, and packet loss rate between applications and databases. If dbMonitor is set to true, loggerLevel is set to debug, and loggerFile is set to filePath, the client connection monitoring information is recorded in the filePath log file. For details, see Example 4: Using the Database Connection Monitoring Function. enableStreamingQuery: Boolean type. It specifies whether to enable the streaming read function. The default value is false. The value is true (enabled) or false (disabled). If this parameter is set to true and statement.setFetchSize(Integer.MIN_VALUE) or statement.enableStreamingResults() is used, the streaming read function is enabled. Streaming read: All data is read at a time and sent to the socket buffer of a client until the buffer is full. If there is free space, the data continues to be sent to the buffer. At the same time, a JVM reads data from the buffer row by row. The advantage of streaming read is that the result is processed fast and no JVM memory overflow occurs. The disadvantage is that only backward traversal is supported. Before the data processing is complete or the statement is closed, other operations cannot be performed on the current connection. For details, see example 2 in Examples. yearIsDateType: Boolean type. The default value is true (enabled). true: An object of the date type is returned when the java.sql.ResultSet#getObject API is used to obtain columns of the year type. A date character string in the yyyy-mm-dd format is returned when the java.sql.ResultSet#getString API is used to obtain data. A year value is returned when the java.sql.ResultSet#getInt/getLong/getShort API is used to obtain data. false: An object of the integer type is returned when the java.sql.ResultSet#getObject API is used to obtain columns of the year data type. A year character string is returned when the java.sql.ResultSet#getString API is used to obtain data. A year value is returned when the java.sql.ResultSet#getInt/getLong/getShort API is used to obtain data. setFloat: Boolean type, indicating whether the OID transferred to the kernel is the OID of float4 when setFloat is called or setObject is set to float. The value true (enabled) indicates that the OID transferred to the kernel is float8. The value false (disabled) indicates that the OID transferred to the kernel is float4. The default value is false. disasterToleranceCluster: Boolean type. The default value is false. When the load balancing function is enabled and only DR cluster nodes are configured in the URL for the JDBC to connect to the database, it determines whether to connect to the primary cluster or the DR cluster. The value true indicates that load balancing is performed on DR cluster nodes. The value false indicates that load balancing is performed between primary cluster nodes.
user	Database user.
password	Password of the database user.

After the uppercaseAttributeName parameter is enabled, if the database contains metadata with a mixture of uppercase and lowercase letters, only the metadata in lowercase letters can be queried and output in uppercase letters. Before using the metadata, ensure that the metadata is stored in lowercase letters to prevent data errors.

Examples

Example 1: Connect to a database.

    
     
       
       
         // gaussdbjdbc.jar is used as an example.
// The following code encapsulates database connection operations into an API. The database can then be connected using an authorized username and a password.
public static Connection getConnect(String username, String passwd)
    {
        // Driver class.
        String driver = "com.huawei.gaussdb.jdbc.Driver";
        // Database connection descriptor.
        String sourceURL = "jdbc:gaussdb://$ip:$port/database";
        Connection conn = null;
        
        try
        {
            // Load the driver.
            Class.forName(driver);
        }
        catch( Exception e )
        {
            e.printStackTrace();
            return null;
        }
        
        try
        {
             // Create a connection.
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("Connection succeed!");
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        }
       
        return conn;
    }

        

      

    
   

Example 2: Use the Properties object as a parameter to create a connection.

    
     
       
       
         // The following code uses the Properties object as a parameter to establish a connection:
public static Connection getConnectUseProp(String username, String passwd)
    {
        // Driver class.
        String driver = "com.huawei.gaussdb.jdbc.Driver";
        // Database connection descriptor.
        String sourceURL = "jdbc:gaussdb://$ip:$port/database?autoBalance=true";
        Connection conn = null;
        Properties info = new Properties();
        
        try
        {
            // Load the driver.
            Class.forName(driver);
        }
        catch( Exception e )
        {
            e.printStackTrace();
            return null;
        }
        
        try
        {
             info.setProperty("user", username);
             info.setProperty("password", passwd);
             // Create a connection.
             conn = DriverManager.getConnection(sourceURL, info);
             System.out.println("Connection succeed!");
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        }
        
        return conn;
    }

        

      

    
   

For details about common parameters, see Common JDBC Parameters.

Example 3: Use the streaming read function.

    
     
       
       
         // There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation).
// Establish a connection.
public static Connection getConnection(String username, String passwd) {
    String driver = "com.huawei.gaussdb.jdbc.Driver";
    String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableStreamingQuery=true";
    Connection conn = null;
    try {
        // Load the driver.
        Class.forName(driver);
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
    try {
        // Create a connection.
        conn = DriverManager.getConnection(sourceURL, username, passwd);
        System.out.println("Connection succeed!");
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
    return conn;
}

// Execute common SQL statements to create table t_user.
public static void CreateTable(Connection conn) {
    Statement stmt = null;
    try {
        stmt = conn.createStatement();

        // Execute common SQL statements.
        stmt.executeUpdate("DROP TABLE IF EXISTS t_user");
        stmt.executeUpdate("CREATE TABLE t_user(id int, name VARCHAR(20));");
        stmt.close();
    } catch (SQLException e) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        e.printStackTrace();
    }
}

// Execute a prepared statement to insert data in batches.
public static void BatchInsertData(Connection conn) {
    PreparedStatement pst = null;

    try {
        // Generate a prepared statement.
        pst = conn.prepareStatement("INSERT INTO t_user VALUES (?,?)");
        for (int i = 0; i < 20; i++) {
            // Add parameters.
            pst.setInt(1, i + 1);
            pst.setString(2, "name " + (i + 1));
            pst.addBatch();
        }
        // Perform batch processing.
        pst.executeBatch();
        pst.close();
    } catch (SQLException e) {
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        e.printStackTrace();
    }
}

// Enable streaming read and query the content in the t_user table.
public static void StreamingQuery(Connection conn) {
    PreparedStatement pst = null;
    ResultSet resultSet = null;

    try {
        // Query all values in the t_user table.
        pst = conn.prepareStatement("SELECT * FROM t_user");
        pst.setFetchSize(Integer.MIN_VALUE);// Functions the same as ((PgStatement)statement).enableStreamingResults();.
        resultSet = pst.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getInt(1));
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

public static void main(String[] args) throws Exception {
    String userName = System.getenv("EXAMPLE_USERNAME_ENV");
    String password = System.getenv("EXAMPLE_PASSWORD_ENV");
    Connection conn = getConnection(userName , password);

    CreateTable(conn);

    BatchInsertData(conn);

    StreamingQuery(conn);

    // Close the database connection.
    try {
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

        

      

    
   

When the streaming read function is used, you need to perform the resultSet.close() or statement.close() operation after the result set is used. Otherwise, the current connection is unavailable.

Parent topic: Development Based on JDBC

Previous topic: Loading a Driver

Next topic: Connecting to a Database (Using SSL)

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot