Help Center/ GaussDB(DWS)/ User Guide/ Connecting to a GaussDB(DWS) Cluster/ Using a Third-Party Database Adapter for GaussDB(DWS) Cluster Connection/ Using the JDBC and ODBC Drivers to Connect to a Cluster/ Third-party Connection Pool of the JDBC Configuration Database
Updated on 2025-01-06 GMT+08:00
View PDF
Share

Third-party Connection Pool of the JDBC Configuration Database

Context

GaussDB(DWS) does not have its own JDBC connection pool, and the inherited PostgreSQL connection pool is offline. Use third-party connection pools like Druid, HikariCP, or DBCP 2.

  • The connection pool inherited by JDBC from PostgreSQL has been brought offline and is not recommended.
  • Determine the version of the JDBC and driver to be downloaded and how to set the connection pool parameters based on the site requirements.

Configuring the DBCP 2 Connection Pool

  1. Download the JDBC driver package. For details, see Downloading the JDBC or ODBC Driver.

  2. Add the JDBC driver package and the commons-dbcp2, commons-logging, and commons-pool2 driver packages to the project and configure parameters related to the database connection pool.

    • Enabling removeAbandoned allows the connection pool to reclaim and reuse a discarded connection. This occurs when the conditions (getNumIdle() < 2) and (getNumActive() > getMaxTotal() - 3) are met.
      • For example, if maxTotal is set to 20, there are 18 active connections and one connection is restricted. In this case, removeAbandoned is triggered.
      • An active connection is deleted only when it is not used for a period of time specified by removeAbandonedTimeout. The default value is 300 seconds.
      • Traversing a result set does not count as usage. Creating a statement, prepared statement, callable statement, or executing a query resets the lastUsed property of its parent connection.
    • In high-load systems, setting maxIdle to a small value may cause new connections to close immediately. This is because active threads close connections faster than those that open connections. As a result, the number of idle connections is greater than the value of maxIdle. In a high-load system, the most appropriate value of maxIdle is various, but the default value is a good start point.
    Table 1 Parameters of the DBCP 2 connection pool

    Parameter

    Default Value

    Description

    driverClassName

    Enter the value of org.postgresql.Driver.

    Name of the database driver.

    url

    -

    URL for connecting to the database.

    username

    -

    Username.

    password

    -

    Password.

    connectionProperties

    -

    The connection parameters are sent to the JDBC driver when a new connection is set up. The string must be in the format of [Parameter name=Parameter value;].

    NOTE:

    The username and password attributes need to be specified. Therefore, the two parameters do not need to be included here.

    defaultAutoCommit

    -

    Automatic submission. By default, the connection created through the current connection pool is in the automatic submission state. If this parameter is not set, the setAutoCommit method is not invoked.

    defaultReadOnly

    -

    Read-only setting. By default, the connection created through the current connection pool is read-only. If the connection is not set, the setReadOnly method is not invoked.

    defaultTransactionIsolation

    -

    Transaction isolation level.

    The default transaction isolation policy is used for connections created through this pool. The value can be one of the following:

    • NONE
    • READ_COMMITTED
    • READ_UNCOMMITTED
    • REPEATABLE_READ
    • SERIALIZABLE

    defaultCatalog

    -

    The default catalog is used for connections created through this pool.

    cacheState

    true

    Cache status of the connection pool.

    If this parameter is set to true, the current read-only status and auto-commit settings are cached during the first read or write operation after the resource pool connects. This eliminates the need for additional database queries on subsequent getter calls.

    If the underlying connection is accessed directly, changes to the read-only state or auto-commit settings will not update the cache. Set this parameter to false to disable caching in such cases.

    defaultQueryTimeout

    null

    Query timeout interval.

    • Enter an integer, which is used to specify the query timeout interval when a statement is created.
    • If the value is null, the default driver settings are used.

    enableAutoCommitOnReturn

    true

    When a connection is returned to the pool, the connection is automatically submitted.

    Setting it to true will return the connection to the pool with autoCommit set to true by default.

    rollbackOnReturn

    true

    Roll back all operations when the connection is returned to the pool.

    Setting it to true will automatically execute "rollback()" when the connection is returned to the pool, provided that auto submission is enabled.

    initialSize

    0

    Number of initial connections. Number of connections created during initialization when the current connection pool is started. The initial version is 1.2.

    maxTotal

    8

    Maximum number of active connections in the pool. A negative value means there is no limit.

    maxIdle

    8

    Maximum number of idle connections in the pool. Excess idle connections are released when returned to the pool. A negative value means there is no limit.

    minIdle

    0

    Minimum number of idle connections. Minimum number of idle connections to retain in the pool. If the number of idle connections falls below this value, new idle connections are created. A value of 0 means no idle connections are created.

    NOTE:

    The value takes effect only when timeBetweenEvictionRunsMillis is set to a positive number.

    maxWaitMillis

    -

    Maximum waiting time for obtaining a connection from the connection pool.

    • If this parameter is set to –1 and no connection is available, the connection pool waits indefinitely until a connection is obtained.
    • If the parameter is set to N, the connection pool waits for N milliseconds. If the waiting time is insufficient, an exception is thrown.

    validationQuery

    SELECT 1

    Query confirmation SQL statement, which validates the connection before it is returned to the caller by the connection pool.

    • If specified, the query must be a SELECT statement that returns at least one row of data.
    • If no value is specified, the connection is verified by invoking the "isValid()" method.

    validationQueryTimeout

    -

    Query timeout interval for valid SQL statements, in seconds.

    If the parameter is set to a positive number, the value is transferred to the "setQueryTimeOut()" method of the JDBC driver. The setting takes effect for the SQL statement for confirming the validity of the query.

    testOnCreate

    false

    Whether to verify the validity of a connection immediately after creation. If verification fails, the creation attempt fails.

    testOnBorrow

    true

    Whether to verify the validity of a connection when it is leased from the pool. If verification fails, the connection is released and another is leased.

    testOnReturn

    false

    Whether to verify the validity of a connection before returning it to the pool.

    testWhileIdle

    false

    Whether to verify the validity of idle connections using an evictor, if available. Invalid connections are released.

    timeBetweenEvictionRunsMillis

    -1

    Hibernate time (in milliseconds) for the idle object eviction thread. A non-positive value disables the thread.

    numTestsPerEvictionRun

    3

    Number of objects checked during the running of each idle object eviction thread.

    minEvictableIdleTimeMillis

    1000 * 60 * 30

    Minimum number of milliseconds in which objects that meet the eviction conditions are idle in the pool. Minimum duration for releasing an idle connection, in milliseconds.

    softMinEvictableIdleTimeMillis

    -1

    Minimum number of milliseconds in which objects that meet the eviction conditions are idle in the pool.

    Idle connections are released after at least N milliseconds, provided that at least the number of connections specified by minIdle is retained in the pool.

    If miniEvictableIdleTimeMillis is set to a positive number, the idle connection evictor checks miniEvictableIdleTimeMillis first, and then softMinEvictableIdleTimeMillis and the minIdle condition.

    maxConnLifetimeMillis

    -1

    Maximum lifetime of a connection (in milliseconds). Connections exceeding this time fail on the next activation, passivation, or verification. A value of 0 or negative means unlimited lifetime.

    logExpiredConnections

    true

    Whether to write logs when an expired connection is closed by the pool. If a connection's lifespan exceeds maxConnLifetimeMillis, it will be reclaimed by the connection pool and a log will be generated by default. If this parameter is set to false, no log will be written.

    connectionInitSqls

    -

    This parameter executes a set of SQL statements to initialize a physical connection when it is first created. These statements run only once per connection.

    lifo

    true

    Last in first out.

    • Last in first out. If this parameter is set to true, the connection pool returns the last used connection first (if there are available idle connections in the pool).
    • If this parameter is set to false, the pool operates as a FIFO queue and obtains connections from the idle connection instance pool in the sequence in which they are returned.

    poolPreparedStatements

    false

    This determines whether the preprocessing statement pool in the connection pool will be applied.

    maxOpenPreparedStatements

    -

    Maximum number of statements that can be allocated in the statement pool at the same time. A negative value means no limit.

    This setting also applies to the pre-processed statement pool. When a statement pool is created for each connection, the pre-processed statements generated by the following method are included.

    public PreparedStatement prepareStatement(String sql)
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
    NOTE:

    Ensure that connections leave resources for other statements by setting maxOpenPreparedStatements to a value less than the maximum number of cursors.

    accessToUnderlyingConnectionAllowed

    false

    This controls whether the PoolGuard can access underlying connections.

    removeAbandonedOnMaintenance

    removeAbandonedOnBorrow

    false

    Whether to delete abandoned connections that have been abandoned for a period longer than the time specified by removeAbandonedTimout.

    If the value is true, connections unused for longer than removeAbandonedTimeout are considered abandoned and removed.

    Creating or executing statements resets the lastUsed property of the parent connection.

    Setting this parameter to true helps recover connections in applications with few write operations.

    removeAbandonedTimeout

    300

    Timeout interval for removing a discarded connection, in seconds.

    logAbandoned

    false

    Whether to enable stack tracing for discarded statements or connected code in an application. When enabled, stack traces for discarded statements and connection-related logs will be overwritten each time a connection is opened or a statement is created.

    abandonedUsageTracking

    false

    When this parameter is set to true, the connection pool records stack traces each time a method is called on a pooled connection, retaining the latest stack trace to aid in debugging abandoned connections.

    NOTE:

    Setting this parameter to true will increase the overhead. Exercise caution when performing this operation.

    fastFailValidation

    false

    This parameter refers to the quick failure of validation statements if a fatal exception occurs, without executing isValid() or the validation query. Fatal exceptions include specific SQL_STATE codes.

    • 57P01 (ADMIN SHUTDOWN)
    • 57P02 (CRASH SHUTDOWN)
    • 57P03 (CANNOT CONNECT NOW)
    • 01002 (SQL92 disconnect error)
    • JZ0C0 (Sybase disconnect error)
    • JZ0C1 (Sybase disconnect error)
    • Any SQL_STATE code that starts with "08"

    Exception codes need to be overwritten. For details, see disconnectionSqlCodes.

    disconnectionSqlCodes

    -

    Exception code, which is an SQL_STATE code separated by commas (,). This parameter is valid only when fastFailValidation is set to true.

    jmxName

    -

    This parameter registers a DataSource as a JMX MBean with a specified name that adheres to the JMX object name syntax.

    registerConnectionMBean

    true

    Whether to register and connect to the JMX MBean.

Configuring the Hikari CP Connection Pool

  1. Download the JDBC driver package. For details, see Downloading the JDBC or ODBC Driver.

  2. Add the JDBC, HikariCP, and SLF4J driver packages to the project and configure parameters related to the database connection pool.

    Table 2 Hikari CP connection pool parameters

    Parameter

    Default Value

    Description

    driverClassName

    Enter the value of org.postgresql.Driver.

    Name of the database driver.

    jdbcUrl

    -

    URL for connecting to the database.

    username

    -

    Username.

    password

    -

    Password.

    autoCommit

    true

    Whether to automatically submit transactions when the connection returns to the connection pool.

    connectionTimeout

    30000

    Maximum timeout interval for obtaining connections from the connection pool.

    idleTimeout

    60000

    Maximum lifetime of an idle connection. This setting takes effect only when the value of minimumIdle is less than that of maximumPoolSize.

    • If the number of idle connections is greater than the value of minimumIdle and the idle time of a connection is greater than the value of idleTimeout, the connection is deleted from the connection pool.
    • 0 indicates no timeout.

    keepaliveTime

    0

    Interval for checking whether idle connections are available, in milliseconds. 0 indicates that the function is disabled.

    maxLifetime

    1800000

    Maximum connection lifetime, in milliseconds. 0 indicates no limit.

    connectionTestQuery

    -

    Query statement for connection detection.

    minimumIdle

    10

    Minimum number of idle connections. To improve performance, you are advised not to set this parameter. The size of the connection pool is fixed.

    maximumPoolSize

    10

    Maximum number of connections.

    metricRegistry

    -

    This parameter can only be accessed through programmatic configuration or the IoC container.

    This parameter specifies the Codahale/Dropwizard MetricRegistry instance used by the pool to record various metrics.

    healthCheckRegistry

    -

    This parameter can only be accessed through programmatic configuration or the IoC container.

    This parameter specifies the Codahale/Dropwizard HealthCheckRegistry instance used by the pool to record health information.

    poolName

    -

    Name of a connection pool.

    initializationFailTimeout

    1

    Whether the connection pool fails to initialize quickly.

    • If the value is greater than 0, the system attempts to obtain a connection within the specified duration (connectionTimeout + initializationFailTimeout). If unsuccessful, the pool is not enabled, and an exception is thrown.
    • If the value is 0, the system attempts to obtain and verify the connection. If verification fails, the pool is not enabled.
    • If the value is less than 0, the pool starts without attempting connection initialization.

    isolateInternalQueries

    false

    Whether to isolate HikariCP queries in a transaction. This setting takes effect when autoCommit is set to false.

    allowPoolSuspension

    false

    Whether to allow the connection pool to be suspended and resumed through JMX. When the connection pool is suspended, the connection does not time out until the connection pool is restored.

    readOnly

    false

    Whether the connection is read-only.

    registerMbeans

    false

    Whether to enable JMX.

    catalog

    -

    Default database catalog.

    connectionInitSql

    -

    SQL statement executed after the connection pool is initialized.

    transactionIsolation

    -

    Default transaction isolation level.

    validationTimeout

    5000

    Timeout interval for connection detection. The value must be greater than the value of connectionTimeout. The minimum value is 250.

    leakDetectionThreshold

    0

    Maximum duration a connection can be lent out.

    The minimum value is 2000 milliseconds, used for logging connection leakage.

    schema

    -

    Default database schema.

    threadFactory

    -

    The java.util.concurrent.ThreadFactory instance used by the connection pool for thread creation. This parameter can only be accessed through programmatic configuration or the IoC container.

    scheduledExecutor

    -

    The java.util.concurrent.ScheduledExecutorService instance used by the connection pool to execute scheduled tasks. This parameter can only be accessed through programmatic configuration or the IoC container.

Configuring the Druid Connection Pool

  1. Download the JDBC driver package. For details, see Downloading the JDBC or ODBC Driver.

    Download the Druid driver package from https://druid.apache.org/downloads/.

  2. Add the JDBC and Druid driver packages to the project and configure parameters related to the database connection pool.

    Table 3 Druid connection pool parameters

    Parameter

    Default Value

    Description

    url

    -

    URL for connecting to the database.

    username

    -

    Username.

    password

    -

    Password.

    driverClassName

    Enter the value of org.postgresql.Driver.

    Name of the database driver.

    initialSize

    0

    Number of physical connections established during initialization. Initialization occurs when the init method is invoked explicitly or when the getConnection method is invoked for the first time.

    maxActive

    8

    Maximum number of connections in the thread pool.

    minIdle

    0

    Minimum number of idle threads in the thread pool. Druid periodically scans the number of connections. If the number exceeds the specified parameter, redundant connections are closed. If fewer connections are available, new ones are created. This parameter helps manage connections during high request volumes, though it can be time-consuming.

    connectTimeout

    -

    Timeout interval for connecting to the database, in milliseconds.

    socketTimeout

    -

    Timeout interval for the socket to connect to the database, in milliseconds.

    maxWait

    -1

    Waiting time for a new request when the connections in the connection pool are used up, in milliseconds.

    –1 indicates infinite waiting until timeout occurs.

    poolPreparedStatements

    false

    Whether to cache preparedStatement, that is, PSCache. The PSCache greatly improves the performance of the database that supports cursors.

    maxOpenPreparedStatements

    -

    If PSCache is enabled, the value of this parameter must be greater than 0.

    If the value is greater than 0, poolPreparedStatements will be automatically set to true.

    validationQuery

    SELECT 1

    SQL statement used to check whether a connection is valid.

    If validationQuery is null, the testOnBorrow, testOnReturn, and testWhileIdle parameters do not take effect because the three parameters are used to verify the validity of the database connection by running the SQL statement specified by validationQuery.

    testOnBorrow

    -

    When applying for a connection, the validationQuery command checks its validity. This configuration may reduce performance, so use it cautiously.

    testOnReturn

    -

    When a connection is returned, the validationQuery command checks its validity. This configuration may also impact performance, so use it cautiously.

    testWhileIdle

    true

    Whether a connection should be checked when it is requested. It is best to set this parameter to true to ensure security without compromising performance. If the idle time is greater than the value of timeBetweenEvictionRunMills, running the validationQuery command to verify the connection's validity will not have any effect.

    timeBetweenEvictionRunsMillis

    60s

    The validationQuery command checks connection validity. If the number of idle connections exceeds minIdle, redundant connections are closed. If fewer idle connections are available, new ones are added. Connections not used within the time specified by timeBetweenEvictionRunsMillis are disabled.

    This parameter also:

    1. Sets the interval for the Destroy thread to check connections.
    2. Functions as a reference for checking testWhileIdle. For details, see the description of the testWhileIdle attribute.

    minEvictableIdleTimeMillis

    30min

    Maximum lifetime of an idle connection before eviction. If the time since the last activity exceeds minEvictableIdleTimeMillis, the connection is closed by the Destroy thread.

    NOTE:

    This parameter conflicts with the timeBetweenEvictionRunsMillis parameter. You can leave this parameter empty.

    connectionInitSqls

    -

    The SQL statement is executed when the physical connection is initialized.

    exceptionSorter

    -

    When the database throws some unrecoverable exceptions, the connection is discarded.

    filters

    -

    This parameter configures an extension plug-in using an alias. The attribute type is string. Common plug-ins include the filters used for monitoring and statistics:

    • stat: monitoring statistics
    • log4j: log record
    • wall: SQL injection prevention

    proxyFilters

    -

    The type is List<com.alibaba.druid,filter.Filter>. You can configure both filter and proxyFilters.

    removeAbandoned

    false

    Whether to reclaim leaked connections.

    When getNumActive() approaches getMaxActive(), the system reclaims invalid connections not used within the removeAbandonedTimeout period (300 seconds by default). Connections exceeding this timeout are forcibly closed.

    removeAbandonedTimeout

    300s

    Time limit for Druid to forcibly reclaim connections, in seconds. Druid will forcibly reclaim a connection from the pool after a specified time has elapsed since the connection was established, starting from the moment the program retrieves the connection from the pool.

    logAbandoned

    false

    Whether to print a log when reclaiming leaked connections.

    This parameter specifies whether to record the stack information of the current thread to logs when the removeAbandoned occurs.

    removeAbandonedTimeoutMillis

    5min

    Timeout interval for reclaiming connections. If removeAbandoned is set to true, Druid periodically checks whether the thread pool overflows. If the thread pool is not in the running state and the specified time is exceeded, the thread pool is reclaimed.

    maxEvictableIdleTimeMillis

    7hours

    Maximum idle time. The default value is 7 hours.

    maxPoolPrepareStatementPerConnectionSize

    20

    Maximum number of SQL statements that can be cached for each connection.

    keepAlive

    false

    Number of minIdle connections to maintain when the pool is initialized.

    If the number of connections falls below minIdle and idle time exceeds minEvictableIdleTimeMillis, the keepAlive operation is performed to maintain the minIdle value.

    notFullTimeoutRetryCount

    0

    Number of retry times when the sum of the number of lent connections in the connection pool and the number of available connections is less than the maximum allowed connections. The default value is 0.

    logSlowSql

    false

    Whether to print slow SQL statements. The value should be of the Boolean type.