JDBC Configuration

Connection Parameters

• When a third-party tool connects to GaussDB through JDBC, JDBC sends a connection request to GaussDB. By default, the following configuration parameters are added. For details, see the implementation of the ConnectionFactoryImpl class in the JDBC code.

  params = {
              { "user", user },
              { "database", database },
              { "client_encoding", "UTF8" },
              { "DateStyle", "ISO" },
              { "extra_float_digits", "3" },
              { "TimeZone",  createPostgresTimeZone() },
            };

  These parameters may cause the JDBC and gsql clients to display inconsistent data, for example, date data display mode, floating point precision representation, and timezone.

  If the result is not as expected, you are advised to explicitly set these parameters in the Java connection setting.

  When the database is connected through JDBC, extra_float_digits is set to 3. When the database is connected using gsql, extra_float_digits is set to 0. As a result, the precision of the same data displayed in JDBC clients may be different from that displayed in gsql clients.

• In precision-sensitive scenarios, the numeric type is recommended.
• When JDBC connects to the database, ensure that the following three time zones are the same:
  • Time zone of the host where the JDBC client is located
  • Time zone of the host where the GaussDB cluster is located
  • Time zone during GaussDB cluster configuration
    

    For details about how to set the time zone, contact the administrator.

fetchsize

To use fetchsize in applications, disable autocommit. Enabling the autocommit switch makes the fetchsize configuration invalid.

autocommit

You are advised to enable autocommit in the code for connecting to GaussDB by the JDBC. If autocommit needs to be disabled to improve performance or for other purposes, applications need to ensure that transactions are committed. For example, explicitly commit translations after specifying service SQL statements. Particularly, ensure that all transactions are committed before the client exits.

Connection Releasing

• You are advised to use connection pools to limit the number of connections from applications. You are advised not to connect to a database each time an SQL statement is executed.
• After an application completes its jobs, disconnect it from GaussDB to release occupied resources. You are advised to set the session timeout interval in the jobs.
• Reset the session environment before releasing connections to the JDBC connection pool. Otherwise, historical session information may cause object conflicts.
  • If GUC parameters are set in the connection, run SET SESSION AUTHORIZATION DEFAULT;RESET ALL; to clear the connection status before you return the connection to the connection pool.
  • If a temporary table is used, delete the temporary table before you return the connection to the connection pool.

CopyManager

In the scenario where the ETL tool is not used and real-time data import is required, it is recommended that you use the CopyManager API driven by the GaussDB JDBC to import data in batches during application development.