Using JDBC to Connect to a Database¶
Obtaining the Driver Package¶
Download the GaussDB driver package GaussDB_opengauss_client_tools.zip.
Prerequisites¶
Java JDK 8 has been installed on the local PC.
You can load the driver using either of the following ways:
Before creating a connection, implicitly load the driver in the code: Class.forName("org.postgresql.Driver")
During the JVM startup, transfer the driver as a parameter to JVM: java -Djdbc.drivers=org.postgresql.Driver jdbctest
Note
jdbctest is the name of a test application.
Calling a Function to Create a Database Connection¶
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);
For details about connection parameters of distributed and primary/standby DB instances, see Table 1 and Table 2, respectively.
Parameter | Description |
|---|---|
url | gsjdbc4.jar database connection descriptor. The format is as follows:
Note If gsjdbc200.jar is used, change jdbc:postgresql to jdbc:gaussdb.
|
info | Database connection attributes (all attributes are case-sensitive). Common attributes are as follows: PGDBNAME: string type. This parameter indicates the database name, which is automatically parsed from the URL. PGHOST: string type. This parameter indicates the host IP address. Use colons (:) to separate IP addresses and port numbers, and use commas (,) to separate multiple IP addresses. (This parameter does not need to be configured in the URL. The system automatically parses the URL to obtain its value.) PGPORT: integer type. This parameter indicates the host port number. Use colons (:) to separate IP addresses and port numbers, and use commas (,) to separate multiple IP addresses. (This parameter does not need to be set in the URL. The system automatically parses the URL to obtain its value.) user: string type. This parameter indicates the database user who creates the connection. password: string type. This parameter indicates the password of a database user. loggerLevel: string type. Currently, four log levels are supported: OFF, INFO, DEBUG, and TRACE. If this parameter is set to OFF, the log function is disabled. If this parameter is set to INFO, DEBUG, and TRACE, the log information of different levels is recorded. loggerFile: string type. This parameter indicates the log output path (including directory and file name). If only the file name is specified and the directory is not specified, logs are stored in the directory where the client program is running. If no path is configured or the configured path does not exist, logs are output through flows by default. logger: string type. This parameter indicates the log output framework used by the JDBC driver for interconnecting with user applications. Currently, only the third-party SLF4J API-based log framework is supported.
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 (&). currentSchema: string type. This parameter indicates the schema to be set in search-path. loadBalanceHosts: Boolean type. If load balancing is disabled (by default), 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.
refreshCNIpListTime: integer type. JDBC periodically obtains the list of available CNs of a DB instance. This parameter indicates the interval for obtaining the list. The default value is 10s. 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 10s. ssl: Boolean type. This parameter indicates SSL connection. When this parameter is set to true, NonValidatingFactory and certificate authentication are supported.
sslmode: string type. This parameter indicates the SSL authentication mode. The value can be require, verify-ca, or verify-full.
sslcert: string type. This parameter indicates the complete path of the certificate file. The type of the client and server certificates is End Entity. sslkey: string type. This parameter indicates the complete path of the key file. sslrootcert: string type. This parameter indicates the name of the SSL root certificate. The root certificate type is CA. sslpassword: string type. This parameter is suitable for ConsoleCallbackHandler. sslpasswordcallback: string type. This parameter indicates the class name of the SSL password provider. The default value is org.postgresql.ssl.jdbc4.LibPQFactory.ConsoleCallbackHandler. sslfactory: string type. This parameter indicates the class name used by SSLSocketFactory to establish an SSL connection. sslprivatekeyfactory: string type. This parameter indicates 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, use 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. It is an optional parameter of sslfactory constructors and is not recommended. sslhostnameverifier: string type. This parameter indicates the class name of the host name verifier. The javax.net.ssl.HostnameVerifier interface can be implemented. The default value is org.postgresql.ssl.PGjdbcHostnameVerifier. loginTimeout: integer type. This parameter indicates the waiting time for establishing the database connection, in seconds. connectTimeout: integer type. This parameter indicates the timeout duration for connecting to a server. 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. socketTimeout: integer type. This parameter indicates the timeout duration for a socket read operation. 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. Canceling messages may get stuck. This parameter controls connectTimeout and socketTimeout used for cancel commends, in seconds. The default value is 10s. tcpKeepAlive: Boolean type. This parameter is used to enable or disable TCP keep-alive probe. The default value is false. logUnclosedConnections: Boolean type. A client may leak a connection object because it does not call the connection object's close() method. The object will be collected as garbage and finalized using the finalize() method. If the caller ignores this operation, this parameter is used to close the connection. assumeMinServerVersion (deprecated): string type. This parameter indicates the version of the server to be connected. ApplicationName: string type. This parameter indicates the name of the application that is being connected. You can query the pgxc_stat_activity table to view information about the client that is being connected. The parameter value is displayed in the application_name column. The default value is PostgreSQL JDBC Driver. connectionExtraInfo: Boolean type. This parameter indicates 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 and process owner to the database. The connection_info parameter indicates the reported information. 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. This parameter indicates what the driver should do 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 rollback and retries are done only when there is an invalid statement. protocolVersion: integer type. This parameter indicates the connection protocol version. Only version 3 is supported. When this parameter is specified, you must change the database encryption mode (by running gs_guc set -N all -I all -Z coordinator -c "password_encryption_type=1") and use MD5 encryption. After the DB instance is rebooted, create a user that uses MD5 encryption to encrypt passwords. You must also change the client connection mode to md5 in pg_hba.conf. Log in to the system as the new user. (You are not advised to specify this parameter because the MD5 encryption algorithm has lower security and poses security risks.) Note MD5 encryption is less secure and is not recommended. prepareThreshold: integer type. This parameter indicates 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 indicates the maximum number of queries that are cached in each connection. The default value is 256. If you use more than 256 different queries in prepareStatement() calls, the least recently used queries will be discarded from the cache. The value 0 indicates 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 you cache more than 5 MB of queries, the least recently used queries will be discarded. The value 0 indicates 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 the cache function is disabled. databaseMetadataCacheFieldsMiB: integer type. The default value is 5. This parameter indicates the maximum size of fields that can be cached in each connection, in MB. The value 0 indicates the cache function is disabled. stringtype: string type. The value can be unspecified or varchar. This parameter indicates the type to use when binding PreparedStatement parameters set via setString(). If stringtype is set to VARCHAR (the default value), such parameters will be sent to the server as varchar parameters. If stringtype is set to unspecified, parameters are sent to the server as untyped values, and the server attempts to infer an appropriate type. batchMode: Boolean type. This parameter indicates 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 indicates the default fetchsize for statements in the created connection. The value 0 (by default) indicates that all results are obtained at a time. reWriteBatchedInserts: Boolean type. During batch import, if this parameter is set to on, N insertion statements can be combined 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 indicates 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 indicates whether data is sent and received in binary format. The default value is false. binaryTransferEnable: string type. This parameter indicates the type for which binary transmission is enabled. Every two types are separated by commas (,). You can select either the OID number 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 indicates the type for which binary transmission is disabled. Every two types are separated by commas (,). You can select either the OID number or name. The value of this parameter overwrites the value of binaryTransferEnable. blobMode: string type. This parameter is used to make the setBinaryStream(int, InputStream, int) method assign values to different types of data. The value on indicates that values are assigned to BLOB data type. The value off indicates that values are assigned to BYTEA data type. The default value is on. setBinaryStream(int, InputStream, long) and setBinaryStream(int, InputStream) are used to assign values to the BYTEA data type. socketFactory: string type. This parameter indicates the name of the class used to create a socket connection with the server. This class must extend javax.net.SocketFactory and define a constructor with no parameter or a single string parameter. socketFactoryArg: string type. It is an optional parameter of the constructor of the socketFactory class and is not recommended. receiveBufferSize: integer type. This parameter is used to specify SO_RCVBUF on the connection stream. sendBufferSize: integer type. This parameter is used to specify SO_SNDBUF on the connection stream. preferQueryMode: string type. The value can be extended, extendedForPrepared, extendedCacheEverything, or simple. This parameter indicates 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. This parameter indicates whether to enable distributed write and query. If this parameter is set to not_perfect_sharding_type (default value), distributed write and query are enabled. If it is set to perfect_sharding_type, distributed write and query are disabled. However, 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. |
user | Database user. |
password | Password of the database user. |
Parameter | Description |
|---|---|
url | postgresql.jar database connection descriptor. The format is as follows:
Note
|
info | Database connection attributes (all attributes are case-sensitive). Common attributes are as follows:
|
user | Database user. |
password | Password of the database user. |
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)
{
// Set the driver class.
String driver = "org.postgresql.Driver";
// Database connection descriptor.
String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres";
Connection conn = null;
try
{
// Load the driver.
Class.forName(driver);
}
catch( Exception e )
{
e.printStackTrace();
return null;
}
try
{
// Establish 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)
{
// Set the driver class.
String driver = "org.postgresql.Driver";
// Database connection descriptor.
String sourceURL = "jdbc:postgresql://10.10.0.13:8000/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);
// Establish a connection.
conn = DriverManager.getConnection(sourceURL, info);
System.out.println("Connection succeed!");
}
catch(Exception e)
{
e.printStackTrace();
return null;
}
return conn;
};