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	postgresql.jar database connection descriptor. The format is as follows: jdbc:postgresql:database jdbc:postgresql://host/database 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: database indicates the name of the database to connect. host indicates the name or IP address of the database server. For security purposes, the primary database node forbids access from other nodes in the database without authentication. To access the primary database node from inside the database, deploy the JDBC program on the host where the primary database node 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 database. Otherwise, the 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 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.
info	Database connection attributes (all attributes are case sensitive). Common attributes are described as follows: PGDBNAME: string type. It 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. For details, see Examples. PGPORT: integer type. It specifies the host port number. 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. 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, DEBUG, and TRACE. The value OFF indicates that the log function is disabled. DEBUG and TRACE logs record information of different levels. loggerFile: string type. It specifies the name of a log file. You can specify a directory for storing logs. If no directory is specified, logs are stored in the directory where the client program is running. 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. 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, LATIN1, or GB18030. The default value is false. NOTE: When the database whose character set is GB18030_2022 is connected, setting characterEncoding to GB18030_2022 does not take effect and UTF8 is used by default. You need to set characterEncoding to GB18030 so that the GB18030_2022 characters on the server can be properly parsed. 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". 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 attempts to set up an SSL connection. If there is a CA file, the system performs verification as if the parameter was set to verify-ca. 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. Convert the client certificate to the DER format. openssl pkcs8 -topk8 -outform DER -in client.key -out client.key.pk8 -nocrypt 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. The default value is org.postgresql.ssl.jdbc4.LibPQFactory.ConsoleCallbackHandler. sslfactory: string type. It specifies the class name used by SSLSocketFactory to establish an SSL connection. 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 org.postgresql.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 the value of this parameter, the connection fails and the subsequent IP addresses are not tried. The default value is 0. 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 default value 0 indicates that the timeout mechanism does not take effect. 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 does not take effect. driverInfoMode: string type. This parameter controls the output mode of the driver description information. The value can be postgresql or gaussdb. The default value is postgresql, indicating that the driver description related to PostgreSQL is displayed. If this parameter is set to gaussdb, the driver description related to GaussDB is displayed. cancelSignalTimeout: integer type. Cancel messages may cause a block. It controls connectTimeout and socketTimeout in a cancel command. 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 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. The default value is false. assumeMinServerVersion: string type. The client sends a request to set a floating point. This parameter specifies the version of the server to connect, for example, assumeMinServerVersion=9.0. This parameter can reduce the number of packets to send during connection setup. ApplicationName: string type. It specifies the name of the JDBC driver that is being connected. You can query the pg_stat_activity table on the primary database node to view information about the client that is being connected. The JDBC driver name is displayed in the application_name column. The default value is PostgreSQL JDBC Driver. connectionExtraInfo: Boolean type. This parameter specifies whether the 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. 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 database is restarted, create a user who uses MD5 encryption to encrypt passwords. In addition, modify the pg_hba.conf file to change the client connection mode to MD5 and log in as a new user (not recommended). 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 time when the parse statement is sent. The default value is 5. It takes a long time to parse an SQL statement for the first time, but a short time to parse SQL statements later because of cache. If a session runs an SQL statement multiple consecutive times and the number of execution times exceeds the value of prepareThreshold, JDBC does not send the parse command to the SQL statement. preparedStatementCacheQueries: integer type. It specifies the number of queries cached in each connection. The default value is 256. If more than 256 different queries are used in the prepareStatement() call, the least recently used query cache will be discarded. The value 0 indicates that the cache function is disabled. preparedStatementCacheSizeMiB: integer type. This parameter specifies the maximum cache size 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. This parameter specifies the maximum cache size of each connection. The value 0 indicates that the cache function is disabled. databaseMetadataCacheFieldsMiB: integer type. The default value is 5. This parameter specifies the maximum cache size of each connection, in MB. The value 0 indicates that the cache function is disabled. stringtype: string type. The options are false, "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 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. The default value is false. 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. uppercaseAttributeName: Boolean type. The default value is false (disabled). The value can also be true (enabled). If this parameter is enabled, the query result of the API for obtaining metadata is converted to uppercase letters. The application scenario is as follows: All metadata stored in the database is in lowercase, but the metadata in uppercase must be used as the input and output parameters. For details about the involved APIs, see java.sql.DatabaseMetaData and java.sql.ResultSetMetaData. 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. This parameter 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=Integer4_ARRAY,Integer8_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. 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 sets the setBinaryStream method to assign values to different types of data. The value on indicates that values are assigned to BLOB data. The value off indicates that values are assigned to bytea data. The default value is on. 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". It specifies the query mode. In simple mode, the query is executed without parsing or binding. In extended mode, the query is executed and bound. The extendedForPrepared mode is used for prepared statement extension. In extendedCacheEverything mode, each statement is cached. targetServerType: string type. This parameter is used to identify the primary DN and standby DN by querying whether a DN allows the write operation in the URL connection string. The default value is any. The value can be "any", "master", "slave", "preferSlave", or "clusterMainNode". master: attempts to connect to a primary node in the URL connection string. If the primary node cannot be found, an exception is thrown. slave: attempts to connect to a standby node in the URL connection string. If the primary node cannot be found, an exception is thrown. preferSlave: attempts to connect to a standby DN (if available) in the URL connection string. Otherwise, it connects to the primary DN. any: attempts to connect to any DN in the URL connection string. clusterMainNode: attempts to connect to the primary node or main standby node (primary DR node) in the URL string. If the primary node or main standby node cannot be found, an exception is thrown. priorityServers: integer type. This value is used to specify the first n nodes configured in the URL as the primary database instance to be connected preferentially. The default value is NULL. The value is a number greater than 0 and less than the number of DNs 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 database instance nodes, and host3 and host4 are DR database instance nodes. forceTargetServerSlave: Boolean type. It specifies whether to enable the function of forcibly connecting to the standby node and forbid the existing connections to be used on the standby node that is promoted to primary during the primary/standby switchover of the database instance. The default value is false, indicating that the function of forcibly connecting to the standby node is disabled. true: The function of forcibly connecting to the standby node is enabled. 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. 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. 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. 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. 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. loadBalanceHosts: Boolean type. In the default mode (disabled), multiple hosts specified in the URL are connected in default sequence. If load balancing is enabled, the shuffle algorithm is used to randomly select a host from the candidate hosts to establish a connection. In a centralized environment, ensure that no write operation is in services when using this parameter. oracleCompatible: string type. The default value is false. This is used to set the A-compatible features of driver APIs. The options are as follows: true or on: All A-compatible features of drivers are enabled. false or off: All A-compatible features of drivers are disabled. "tag1,tag2,tag3": Some A-compatible features of drivers are enabled. You can configure one or more tags separated by commas (,). Each tag corresponds to an A-compatible feature. Currently, the following tags are supported: getProcedureColumns: The behavior of the DatabaseMetaData#getProcedureColumns API is compatible with behavior A. batchInsertAffectedRows: After reWriteBatchedInserts is enabled, the result returned by the Statement#executeBatch API is compatible with behavior A. 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.
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.

    
     
       
       
         // 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 = "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;
    }

        

      

    
   

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 = "org.postgresql.Driver";
        // Database connection descriptor.
        String sourceURL = "jdbc:postgresql://$ip:$port/postgres";
        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;
    }

        

      

    
   