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.
Parameter | Default Value | Description |
|---|---|---|
driverClassName | Enter the value of org.postgresql.Driver. | Name of the database driver. |
url | N/A | URL for connecting to the database. |
username | N/A | Username. |
password | N/A | Password. |
connectionProperties | N/A | 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 | N/A | 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 | N/A | 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 | N/A | Transaction isolation level. The default transaction isolation policy is used for connections created through this pool. The value can be one of the following:
|
defaultCatalog | N/A | 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.
|
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 | N/A | Maximum waiting time for obtaining a connection from the connection pool.
|
validationQuery | SELECT 1 | Query confirmation SQL statement, which validates the connection before it is returned to the caller by the connection pool.
|
validationQueryTimeout | N/A | 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 | N/A | 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.
|
poolPreparedStatements | false | This determines whether the preprocessing statement pool in the connection pool will be applied. |
maxOpenPreparedStatements | N/A | 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.
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.
Exception codes need to be overwritten. For details, see disconnectionSqlCodes. |
disconnectionSqlCodes | N/A | Exception code, which is an SQL_STATE code separated by commas (,). This parameter is valid only when fastFailValidation is set to true. |
jmxName | N/A | 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. |
Parameter | Default Value | Description |
|---|---|---|
driverClassName | Enter the value of org.postgresql.Driver. | Name of the database driver. |
jdbcUrl | N/A | URL for connecting to the database. |
username | N/A | Username. |
password | N/A | 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.
|
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 | N/A | 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 | N/A | 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 | N/A | 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 | N/A | Name of a connection pool. |
initializationFailTimeout | 1 | Whether the connection pool fails to initialize quickly.
|
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 | N/A | Default database catalog. |
connectionInitSql | N/A | SQL statement executed after the connection pool is initialized. |
transactionIsolation | N/A | 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 | N/A | Default database schema. |
threadFactory | N/A | 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 | N/A | 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. |
Download the Druid driver package from https://druid.apache.org/downloads/.
Parameter | Default Value | Description |
|---|---|---|
url | N/A | URL for connecting to the database. |
username | N/A | Username. |
password | N/A | 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 | N/A | Timeout interval for connecting to the database, in milliseconds. |
socketTimeout | N/A | 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 | N/A | 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 | N/A | When applying for a connection, the validationQuery command checks its validity. This configuration may reduce performance, so use it cautiously. |
testOnReturn | N/A | 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:
|
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 | N/A | The SQL statement is executed when the physical connection is initialized. |
exceptionSorter | N/A | When the database throws some unrecoverable exceptions, the connection is discarded. |
filters | N/A | 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:
|
proxyFilters | N/A | 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. |
Create the db.properties file in the resource directory.
# Database connection parametersurl=jdbc:postgresql://10.10.0.13:8000/gaussdbusername=userpassword=passvalidationQuery=select 1validationQueryTimeout=300#driverClassName=JDBC driver namedriverClassName=org.postgresql.Driver# Number of initialized connectionsinitialSize=1# Maximum number of connectionsmaxActive=20# Number of core threads. If the number of core threads is greater than this configured value, the threads are released.minIdle=10
Example code:
import com.alibaba.druid.pool.DruidDataSource;import com.alibaba.druid.pool.DruidDataSourceFactory;import com.alibaba.druid.pool.DruidPooledConnection;import java.io.IOException;import java.io.InputStream;import java.sql.ResultSet;import java.sql.Statement;import java.util.Properties;public class TestDataSource {private static DruidDataSource dataSource;public static void main(String[] args) throws Exception {Properties properties = loadProperties();dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);dataSource.setTimeBetweenEvictionRunsMillis(50 * 1000);dataSource.setRemoveAbandoned(true);dataSource.setRemoveAbandonedTimeout(120);dataSource.setSocketTimeout(5000);dataSource.setConnectTimeout(5000);dataSource.setQueryTimeout(5);final DruidPooledConnection connection = dataSource.getConnection();final Statement statement = connection.createStatement();execute(statement);}public static void execute(Statement statement) {ResultSet resultSet = null;try {resultSet = statement.executeQuery("select 1");while (resultSet.next()) {String str = resultSet.getString(1);System.out.println("n1 :" + str);return;}} catch (Exception e) {e.printStackTrace();}}/*** Load the configuration file and obtain parameters from the configuration file.*/public static Properties loadProperties() {InputStream inputStream = TestDataSource.class.getClassLoader().getResourceAsStream("db.properties");Properties ps = new Properties();try {ps.load(inputStream);} catch (IOException e) {e.printStackTrace();}return ps;}}