Connecting to a Database

After a database is connected, you can use JDBC to run SQL statements to operate data.

Function Prototype

JDBC provides the following 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	gsjdbc4.jar database connection descriptor. The format is as follows: jdbc:postgresql: (The default database name is the same as the username.) jdbc:postgresql:database jdbc:postgresql://host/database (If the port number is not specified, the default port number is used.) jdbc:postgresql://host:port/database jdbc:postgresql://host:port/database?param1=value1&param2=value2 jdbc:postgresql://host1:port1,host2:port2/database?param1=value1&param2=value2 NOTE: If gsjdbc200.jar is used, replace jdbc:postgresql with jdbc:gaussdb. database indicates the name of the database to connect. host indicates the name or IP address of the database server. 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 5431 of the local host is connected. 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 the example below. value indicates the database connection attribute values. In a distributed environment, you are advised to configure the autoBalance parameter for the connection strings for load balancing and configure at least two CNs to prevent connection setup failures due to node faults.
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. This parameter specifies the host IP address. 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 the example below. PGPORT: integer type. This parameter 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 the example below. user: string type. This parameter specifies the database user who creates the connection. password: string type. This parameter specifies the password of the database user. enable_ce: string type. If enable_ce is set to 1, JDBC supports encrypted equality queries. refreshClientEncryption: string type. If refreshClientEncryption is set to 1 (default value), the encrypted database supports cache update on the client. loggerLevel: string type. The following log levels are supported: OFF, INFO, DEBUG, and TRACE. Set this parameter to OFF to disable the log function. INFO, DEBUG and TRACE logs record information of different levels. loggerFile: string type. This parameter specifies the log output path (directory and file name). If only the file name is specified and the directory is not specified, logs are generated in the client running program directory. If no path is configured or the configured path does not exist, logs are output through flows by default. This parameter has been discarded and does not take effect. To use this parameter, you can 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 applications. Currently, only the third-party Slf4j-API-based log framework is supported. For details, see 6.2.9 Log Management. If this parameter is not set 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. If this parameter is set to true, the character set type can be changed. This parameter is used together with characterEncoding=CHARSET to set the character set. The two parameters are separated by ampersands (&). The value of characterEncoding can be UTF8, GBK, or LATIN1. currentSchema: string type. This parameter specifies the schema to be set in search-path. 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:postgresql://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 value is 10s. Hosts obtained from the CN list are data IP addresses. Generally, floating IP addresses are used in the cloud environment. Before using the load balancing capability, ensure that the access permissions of the data IP addresses have been added. When autoBalance is enabled on host1 and host2, HA is implemented only for the first connection. The JDBC driver will select available CNs from host1, host2, host3, and host4 in sequence to update the available CN listm 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. Example: jdbc:postgresql://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 JDBC driver preferentially selects host1 and host2 for load balancing. If both host1 and host2 are unavailable, the JDBC 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:postgresql://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 value is 10s. 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 false, the JDBC load balancing and priority-based load balancing functions are disabled. The default value is false. CAUTION: 1. 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. 2. Load balancing can be used only in distributed scenarios and cannot be used in centralized scenarios. 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. This parameter 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. This parameter specifies the SSL authentication mode. The value can be require, verify-ca, or verify-full. 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. This parameter specifies the complete path of the certificate file. The type of the client and server certificates is End Entity. sslkey: string type. This parameter specifies the complete path of the key file. You need to convert the client certificate to the DER format. For details, see Connecting to the Database (Using SSL). sslrootcert: string type. This parameter specifies the name of the SSL root certificate. The root certificate type is CA. sslpassword: string type. This parameter is provided for ConsoleCallbackHandler. sslpasswordcallback: string type. This parameter specifies the class name of the SSL password provider. The default value is org.postgresql.ssl.jdbc4.LibPQFactory.ConsoleCallbackHandler. sslfactory: string type. This parameter specifies the class name used by SSLSocketFactory to establish an SSL connection. sslprivatekeyfactory: string type. This parameter specifies the fully qualified name of the implementation class of the org.postgresql.ssl.PrivateKeyFactory interface that implements the private key decryption method. If this parameter is not specified, try the default JDK private key decryption algorithm. If the decryption fails, use org.postgresql.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 (This parameter is not recommended). sslhostnameverifier: string type. This parameter specifies the class name of the host name verifier. The interface must implement javax.net.ssl.HostnameVerifier. The default value is org.postgresql.ssl.PGjdbcHostnameVerifier. loginTimeout: integer type. This parameter 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 the value of this parameter, the connection fails and the subsequent IP addresses are not tried. connectTimeout: integer type. This parameter specifies the timeout duration for connecting to a server, in seconds. If the time taken to connect to a server exceeds the value specified, the connection is interrupted. If the value is 0, the timeout mechanism is disabled. When multiple IP addresses are configured in the URL, this parameter indicates the timeout interval for connecting to a single IP address. socketTimeout: integer type. This parameter specifies the timeout duration for a socket read operation, in seconds. If the time taken to read data from a server exceeds the value specified, the connection is closed. If the value is 0, the timeout mechanism is disabled. cancelSignalTimeout: integer type. Cancel messages may cause a block. This parameter controls connectTimeout and socketTimeout in a cancel message, in seconds. The default value is 10 seconds. tcpKeepAlive: Boolean type. This parameter is used to enable or disable TCP keepalive detection. The default value is false. logUnclosedConnections: Boolean type. 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. 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 PostgreSQL JDBC Driver. connectionExtraInfo: Boolean type. This parameter specifies whether the JDBC driver reports the driver deployment path and process owner 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. The default value is never. This parameter 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. protocolVersion: integer type. This parameter specifies the connection protocol version. Only versions 1 and 3 are supported. Note: If this parameter is set to 1, only the V1 server is connected. MD5 encryption is used when this parameter is set to 3. You need to set password_encryption_type to 1 to change the database encryption mode. After the cluster is restarted, create a user that uses MD5 encryption to encrypt passwords. You must also change the client connection mode to md5 in the pg_hba.conf file. Log in to the system as the new user. (You are not advised to set this parameter 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. This parameter 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 specifies the maximum number of queries generated by the cache statement object of each connection. The unit is 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. This parameter 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 value can be unspecified or varchar. This parameter specifies the type of the PreparedStatement parameter used by the setString() method. If stringtype is set to varchar, 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. This parameter specifies whether to connect the database in batch mode. The default value is on, indicating that the batch mode is enabled. fetchsize: integer type. This parameter 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 is equivalent to defaultRowFetchSize. reWriteBatchedInserts: Boolean type. During batch import, set this parameter to true to combine N insertion 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 postgresql types (such as TEXT) is returned by functions such as ResultSetMetaData.getColumnDisplaySize and ResultSetMetaData.getPrecision. defaultRowFetchSize: integer type. This parameter 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. This parameter specifies whether data is sent and received in binary format. The default value is false. binaryTransferEnable: string type. This parameter specifies the type 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 OID name is BLOB and the OID number is 88, you can configure the OID as follows: binaryTransferEnable=BLOB or binaryTransferEnable=88 binaryTransferDisEnable: string type. This parameter specifies the type for which binary transmission is disabled. Every two types are separated by commas (,). You can select either the OID or name. The value of this parameter overwrites the value of binaryTransferEnable. blobMode: string type. This parameter 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 Oracle and MySQL and to off for systems migrated from PostgreSQL. socketFactory: string type. This parameter specifies the name of the class used to create a socket connection with the server. This class must implement the javax.net.SocketFactory interface 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. This parameter is used to set SO_RCVBUF on the connection stream. sendBufferSize: integer type. This parameter 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 indicates 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:postgresql://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. The value specifies whether to use the 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. iamUser: string type. The fully-encrypted database encrypts data on the client. During encryption, you can access the Key Management Service (KMS) provided by Huawei Cloud to obtain keys. When accessing the KMS, you need to provide the IAM identity authentication information and KMS project information. iamUser and iamPassword are used to set identity authentication information. kmsDomain, kmsProjectId, and kmsProjectName are used to set KMS project information. To obtain the preceding five parameters, log in to the Huawei Cloud official website and choose Console > My Credential. iamPassword: string type, used to set the password of the IAM user. kmsDoamin: string type, used to set the Huawei Cloud account to which the KMS belongs. kmsProjectName: string type, used to set the deployment zone of a KMS project. KMS projects deployed in different zones are isolated from each other. kmsProjectId: string type, used to set the ID of a KMS project. 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 org.postgresql.log.Tracer API that implements the method for obtaining traceId. use_boolean: Boolean type. This parameter 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. This parameter specifies whether the read-only mode is allowed. The default value is true, indicating that the read-only mode is allowed. If this parameter is set to false, the read-only mode is disabled. TLSCiphersSupperted: string type. This parameter 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 this parameter is set to true, trailing 0s of the numeric type are removed. This parameter is valid only for ResultSet.getObject(int columnIndex). enableTimeZone: Boolean type. The default value is true. This parameter 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. socketTimeoutInConnecting: integer type. The default value is 5s. It specifies the timeout value for a socket read operation during connection establishment. If the time taken to read data from a server exceeds the value specified during connection establishment, the connection is closed. If the value is 0, the timeout mechanism is disabled.
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

    
     
       
       
         // The following uses gsjdbc4.jar as an example.
// The following code encapsulates database connection operations into an interface. 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 = "org.postgresql.Driver";
        // Database connection descriptor.
        String sourceURL = "jdbc:postgresql://$ip:$port/postgres";
        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;
    }
// 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 = "org.postgresql.Driver";
        // Database connection descriptor.
        String sourceURL = "jdbc:postgresql://$ip:$port/postgres?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;
    }

        

      

    
   