DBCP2 Configuration Detailed description (Chinese translation)

http://blog.csdn.net/kerafan/article/details/50382998

COMMON-DBCP2 database Connection Pool parameter description

Because of the version upgrade of the connection pool used by COMMONS-DBCP, the database pool connection configuration in Commons-dbcp2 has also changed, with the following configuration instructions: parameter Description

Username (User name)	The user name required to establish a connection through JDBC
Password Password	The password required to establish a connection through JDBC
Url (JDBC Connection string)	The URL required to establish a connection through JDBC
Driverclassname (JDBC driver)	The full name of the JDBC-driven class used
ConnectionProperties (Connection properties)	Connection parameters are sent to the JDBC driver when a new connection is established The format of the string must be [parameter name = argument value;] Tip: The user name and password attributes need to be explicitly stated, so these two parameters do not need to be included here

parameter Default value description

Defaultautocommit (Auto-submit)	Default values for JDBC drivers	The default autocommit status for connections is created through this pool. If not set, the Setautocommit method will not be called.
Defaultreadonly (Read-only settings)	Default values for JDBC drivers	The default read-only state of the connection is created from this pool. If not set, the Setreadonly method will not be called. (Some drivers do not support read-only mode, such as: Informix)
Defaulttransactionisolation (Transaction ISOLATION level)	Default values for JDBC drivers	Create the default transaction isolation policy for the connection through this pool, set the value to one of the following: (refer to Javadoc)(see Another transaction attribute summary in the note) NONE read_committed read_uncommitted Repeatable_read SERIALIZABLE
Defaultcatalog (directory, similar to schema name, but more abstract than schema name, Oracle, MySQL not supported, other, MS sql Server = database name)		Create the default catalog for the connection through this pool
Cachestate (Cache status)	True	If set to true, pooled connections will cache the current read-only state and autocommit settings at the first read or write, as well as subsequent writes. This saves additional queries to the database when any further calls to the getter are made. If you access the underlying connection directly, the read-only status and/or autocommit settings change the cached value will not be reflected to the current state, in which case the property should be set to False to disable caching.

Defaultquerytimeout (Query time-out)	Null	If the value is not NULL, specify an integer value to specify as the query time-out when creating the statement. If set to NULL, the driver default settings are used.
Enableautocommitonreturn (When the connection is returned to the pool, it is set to auto-commit)	True	If set to true, when the connection is returned to the connection pool, it is specified to be set to Autocommit = True
Rollbackonreturn (When the connection is returned to the pool, all operations are rolled back)	True	If set to True, Rollback () is automatically executed once the connection is returned to the connection pool, provided that autocommit = True and is not read-only.

parameter Default value description

initialsize (number of initial connections)	0	When this pool is started, the number of created connections initialized, starting in effect version: 1.2
maxtotal (maximum active connections)	8	can be assigned at the same time in this pool Maximum number of valid connections, if set to negative, does not limit
maxidle (maximum number of idle connections)	8	The maximum number of connections that can remain idle in the pool, beyond the set value An idle connection is released when it is returned to the connection pool, and is set to a negative number without limiting the
minidle (minimum number of idle connections)	0	minimum connections that can remain idle in the pool, less than When the value is set, the idle connection is created to try to keep the minimum number of idle connections >=minidle, as set to 0, without creating a The values set here are valid if: Timebetweenevictionrunsmillis (the number of dormant milliseconds when the Idle object's eviction thread is running) is set to positive.
maxwaitmillis (maximum wait time when getting a connection from a connection pool)	indefinitely (indeterminate)	is set to 1 if Without an available connection, the connection pool waits indefinitely until the connection is acquired. If the is set to N (milliseconds), the connection pool waits for n milliseconds and does not wait for an exception to be thrown.

Tip: If you set the value of Maxidle to very low on a high-load system, you might find that a new connection was immediately closed when it was just created. This is the active thread that closes the connection in a timely manner faster than those threads that open the connection, causing the number of idle connections to be greater than maxidle. The most appropriate configuration value for Maxidle in high-load systems is varied, but the default value is a good starting point.

parameter Default value description

Validationquery (Confirm query SQL) Validationquerytimeout (Confirm query SQL time-out)	No timeout	The query SQL used for connection validation before the connection pool returns the connection to the caller. If specified, the query must be a SQL SELECT statement that returns at least one row of data. If not specified, the connection is validated by calling the IsValid () method. Verify that the execution query time-out (in seconds) is connected to valid SQL. If set to a positive number, it is passed to the JDBC-driven setquerytimeout () method, which works on "Confirm valid query SQL".
Testoncreate (Verification of validity immediately after connection creation)	False	Indicates whether the object requires validation after it is created, and if the object validation fails, the lease attempt to trigger the creation of the object will fail.
Testonborrow (Validate validity when getting a connection from the connection pool)	True	Indicates whether validation is valid when the object is rented from the pool, if the object validation fails, the object is freed from the pond, and then we try to lease another
Testonreturn (Validation is valid when the connection is returned to the connection pool)	False	Indicates whether validation is required before the object is returned to the connection pool.
Testwhileidle (Verify validity when connection is idle)	False	Indicates whether the object needs to be verified by the object's expulsion (if any), and if an object fails validation, the object will be freed from the pool.
Timebetweenevictionrunsmillis (Number of sleep milliseconds when the idle object evicted the thread)	-1	The Idle object expels the number of dormant milliseconds when the thread is running, and if set to non-positive, the free object is not run to evict the thread.
Numtestsperevictionrun (Number of objects to check during each free object eviction thread running)	3	The number of objects that are checked during the eviction of each idle object during the run of the thread. (If any)
Minevictableidletimemillis (The idle connection will not be released until the minimum of n milliseconds is spent)	1000 * 60 * 30 (30 points)	The minimum number of idle milliseconds in the pool for an object that conforms to the object expulsion condition (if any)
Softminievictableidletimemillis (The idle connection will not be released until it is a minimum of n milliseconds, but with additional conditions)	-1	The minimum number of idle milliseconds in the pool for an object that conforms to the object expulsion condition, and the additional condition is that at least the number of connections specified by Minidle is retained in the pool. When the Minievictableidletimemillis is set to a positive number, the idle connection expulsion first detects the Minievictableidletimemillis, and when the idle connection is accessed by the evicted person, The values specified by Minievictableidletimemillis are compared first, regardless of the number of idle connections in the current pool, and then the number of connections specified by Softminevictableidletimemillis is compared, including the minidle condition.
Maxconnlifetimemillis (Maximum number of surviving milliseconds for a connection)	-1	The maximum number of surviving milliseconds for a connection. If this time is exceeded, the connection will fail at the next activation, deactivation, and checksum. If set to a value of 0 or less than 0, the lifetime of the connection is unlimited.
Connectioninitsqls (The initialization SQL executed when the connection was first created)	Null	The collection of SQL statements used to initialize the physical connection at the first creation time. These statements are executed only once when a connection is created by a configured connection factory.
Lifo (last in, first out)	True	Set to true to indicate that the connection pool (if an idle connection is available in the pool) returns the last leased object used (last entry). Set to False to indicate that the pool will behave as a FIFO queue (first in, out)-the connection will be taken from the pool of idle connection instances in the order in which they are returned

Logexpiredconnections (Write log ID when an expired connection is closed by the connection pool)

True

If the connection inventory time exceeds Maxconnlifetimemillis, the connection will be pumped by the connection pool, at which point the log is output. If set to False, this does not write the log.

parameter Default value description

Poolpreparedstatements (Sets whether the preprocessing statement pool for this connection pool is in effect)	False	Sets whether the preprocessing statement pool for this connection pool is in effect
Maxopenpreparedstatements (Maximum number of statements that can be allocated concurrently in the statement pool)	Unlimited	The maximum number of statements that can be allocated concurrently in the statement pool. Set to a negative number is not limited.

This setting also acts on the preprocessing statement pool. When an available statement pool is created for each connection, the preprocessing statements created by the following methods are pooled.

• Public PreparedStatement preparestatement (String sql)
• Public PreparedStatement preparestatement (String sql, int resultsettype, int resultsetconcurrency)

Hint-Make sure your connection leaves some resources to other statements. Pooled preprocessing statements may keep their cursors in the database, potentially causing the connected cursor to go out of bounds, especially if the value of maxopenpreparedstatements is set to the default value (infinite), and an application may open a large number of different preprocessing statements for each connection. To avoid this problem maxopenpreparedstatements should be set to a value that is less than the maximum number of cursors that a connection can open.

parameter Default value description

Accesstounderlyingconnectionallowed (Controls whether the Poolguard can access the underlying connection)

False

Controls whether the Poolguard can access the underlying connection

If access is allowed, use the following code structure:

Connection conn = Ds.getconnection ();
Connection Dconn = ((delegatingconnection) conn). Getinnermostdelegate ();
...
Conn.close ()

The default value is False, which is a potentially risky operation, and improper use can result in very serious consequences. (Close the underlying connection or continue to use it if the daemon connection is closed), you can use it sparingly only if you need direct access to the driver's unique extension.

Note: Do not close the underlying connection other than the original one

parameter Default value description

Removeabandonedonmaintenance Removeabandonedonborrow （）	False	Flag whether to delete abandoned connections that exceed the time specified by Removeabandonedtimout. If set to True, a connection is considered to be discarded and should be removed if it is not used at the time set by Removeabandonedtimeout. Creating a statement, preprocessing statements, callable statements, or using one of them to execute a query (using one of the execution methods) will reset the Lastused property of its parent connection. Setting this parameter to true in applications with fewer writes can restore a database connection from a connection shutdown failure.
Removeabandonedtimeout (Timeout when a discarded connection can be removed)	300 (SEC)	The time-out period in seconds that a discarded connection can be removed
logabandoned	False	Flag whether the log stack trace is turned on for abandoned statements or connected code in the application. Because a stack trace has been created, discarded statements and connection-related logs will be overwritten when each connection is opened or when a statement is created

If you enable removeabandoned, it is possible for a connection to be reclaimed by the pool, since it is considered abandoned at (Getnumidle () < 2) and (Getnumactive () > Getmaxtotal ()-3) when established , this mechanism will be triggered.

For example, maxtotal=20, where there are 18 active connections, a restricted connection, will trigger "removeabandoned". However, it will only be deleted if the active connection exceeds the number of seconds specified by "Removeabandonedtimeout" (default is 300 seconds). Traversing a result set is not counted as being used, creating a statement, preprocessing statements, callable statements, or using one of them to execute a query (using one of the execution methods) to reset the Lastused property of its parent connection.

Parameter	Default	Description
Fastfailvalidation (Verify fast failure)	False	If a fatal exception occurs, the validation statement will fail quickly. Instead of executing the isvalid (), the validation query statement is no longer executed. The fatal Exception code (sql_state) refers to the following: 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, starts with "08" To overwrite the exception code, see disconnectionsqlcodes
Disconnectionsqlcodes (Exception code)	Null	A comma-separated sql_state code,fastfailvalidation True, takes effect.

Top

2

Step

DBCP2 Configuration Detailed description (Chinese translation)