Updated on 2025-03-13 GMT+08:00

JDBC

  • 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.

  • [Specification] A database must be specified for each JDBC instance. Once the instance is created, it cannot switch to another database.
  • [Specification] The length of a single SQL statement cannot exceed 2 GB. Considering the communications cost, it is recommended that the length of a single SQL statement be less than or equal to 5 KB.
  • [Specification] Currently, only the DEFAULT value of the columns in CREATE/ALTER TABLE can be set through parameters. Other DDL statements do not support parameter settings using the Prepare Execute method.
  • [Rule] Each PreparedStatement of JDBC can contain a maximum of 32767 parameters.
  • [Specification] The connection parameter fetchsize must be used when autocommit is disabled. Otherwise, the fetchsize configuration is invalid.
  • [Rule] Use the default GUC parameters and avoid sending SET requests through JDBC to modify GUC parameters.

    For details, see GUC Parameter Programming Specifications.

  • [Rule] You must use the Prepare Execute method to execute query statements to improve execution efficiency.
  • [Rule] Execute SQL statements one by one in the same transaction. Do not combine multiple SQL statements and send them as one statement.

    For details, see [Rule] Do not combine multiple SQL statements into one statement when accessing the database through JDBC.

  • [Rule] The time zone of the host where the JDBC client is located, the time zone of the host where the database cluster is located, and the time zone during cluster configuration must be the same.
  • [Rule] If a temporary table is created in a connection, delete the temporary table before releasing the connection to the connection pool to avoid service errors.
  • [Recommendation] In precision-sensitive scenarios, the numeric type is recommended.
  • [Recommendation] When connecting to the database through JDBC, 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.

  • [Recommendation] 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.
  • [Recommendation] 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.
  • [Recommendation] 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.
  • [Recommendation] Reset the session environment before releasing connections to the JDBC connection tool. 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.
  • [Recommendation] 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.
  • [Recommendation] Set prepareThreshold to a proper value. If the query statement is fixed, set it to 1.
  • [Recommendation] You are advised to set the connection parameter autoBalance to true to enable the CN load balancing function and set multiple CN connection addresses (separated by commas).
    • Once autobalance is enabled, the JDBC driver attempts to allocate JDBC connections to different CNs.
    • The purpose of setting multiple CN connection addresses is to prevent the JDBC driver from failing to obtain the CN list of the cluster for the first time due to a CN fault.
    • Once the CN list is obtained successfully for the first time, the system does not depend on the CN list specified in the connection parameters. Instead, the system obtains the latest CN list by connecting to a valid CN in the cluster CN list obtained in real time at a specified interval.
  • [Recommendation] You are advised to set batchMode to on to use the batch connection mode to improve the execution performance.
  • [Recommendation] Set a proper JDBC connection timeout interval based on the upper-layer request timeout of the service to prevent the job from always occupying database resources.

    Timeout parameters include loginTimeout, connectTimeout, and socketTimeout.

    • loginTimeout: integer type. This parameter specifies the waiting time for establishing the database connection, in seconds. The default value is 0, indicating that the timeout mechanism is disabled.
    • connectTimeout: integer type. This parameter 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 default value 0 indicates that the timeout mechanism is disabled.
    • socketTimeout: integer type. This parameter indicates 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 is disabled.
    • cancelSignalTimeout: integer type. A cancel message may cause a block. This attribute controls "connect timeout" and "socket timeout" in a cancel command. The default value is 10.
    • tcpKeepAlive: Boolean type. This parameter is used to enable or disable TCP keepalive detection. The default value is false.