Java learning notes-Spring database data source DBCP configuration instructions

Data connection is required to connect to the database. Generally, DBCP is used in Spring to configure the source. configure the corresponding data configuration in xml to connect to the database. You can check the following configurations:

<Bean id = "jdbc" class = "org. springframework. beans. factory. config. PropertyPlaceholderConfigurer">
<Property name = "locations" value = "/WEB-INF/jdbc. properties"/>
</Bean>
<Bean id = "dataSource" class = "org. apache. commons. dbcp. BasicDataSource" destroy-method = "close">
<Property name = "driverClassName" value = "$ {jdbc. driverClassName}"/>
<Property name = "url" value = "$ {jdbc. url}"/>
<Property name = "username" value = "$ {jdbc. username}"/>
<Property name = "password" value = "$ {jdbc. password}"/>
<Property name = "testOnBorrow" value = "false"/>
<Property name = "testWhileIdle" value = "true"/>
<! -- Initial value when the connection pool starts -->
<Property name = "initialSize" value = "10"/>
<! -- Maximum value of the connection pool -->
<Property name = "maxActive" value = "100"/>
<! -- Maximum idle value. After a peak time, the connection pool can slowly release a portion of the connections that are no longer in use, until they are reduced to maxIdle. -->
<Property name = "maxIdle" value = "50"/>
<! -- Minimum idle value. When the number of idle connections is less than the threshold value, the connection pool will pre-apply for some connections to avoid too late to apply when the peak traffic comes -->
<Property name = "minIdle" value = "10"/>
<! -- # Give a simple SQL statement for verification -->
<Property name = "validationQuery" value = "select getdate ()"/>
<! -- # Verify the connection when pulling out -->
<Property name = "removeAbandonedTimeout" value = "120"/>
<Property name = "removeAbandoned" value = "true"/>
<! -- # Interval between running and determining connection timeout tasks, in milliseconds. The default value is-1, indicating that the task is not executed. -->
<Property name = "timeBetweenEvictionRunsMillis" value = "3600000"/>
<! -- # Connection timeout. The default value is half an hour. -->
<Property name = "minEvictableIdleTimeMillis" value = "3600000"/>
</Bean>
<Bean id = "JdbcTemplate" class = "org. springframework. jdbc. core. JdbcTemplate">
<Property name = "dataSource" ref = "dataSource"/>
</Bean>

With the above configuration, you can directly use JdbcTemplate for database operations.
The following is a detailed configuration table:

Common Link Configuration:
Parameter description
The username passed to the JDBC driver for connection Establishment
Password the password passed to the JDBC driver for connection Establishment
Url passed to the JDBC driver for establishing a connection
Complete and valid java class name of the JDBC driver used by driverClassName
ConnectionProperties the connection parameter sent to the JDBC driver when a new connection is established. The format must be [propertyName = property;] *
Note: The user/password parameter will be passed explicitly, so it is not required here.

Transaction attribute Configuration:
Default parameter description
DefaultAutoCommit true default auto-commit status of the connection pool created
The default read-only status of the connection created by the defaultReadOnly driver default connection pool. If no value is set, the setReadOnly method will not be called. (Some drivers do not support read-only mode, such as Informix)
DefaultTransactionIsolation driver default the default TransactionIsolation status of the connection created by the connection pool. One of the following lists: (refer to javadoc)
NONE
READ_COMMITTED
READ_UNCOMMITTED
REPEATABLE_READ
SERIALIZABLE
Default catalog of the connection created in the defaultCatalog connection pool
Data source connection quantity Configuration:
Default parameter description
InitialSize 0 initial connection: number of initial connections created when the connection pool is started, which is supported after version 1.2.
MaxActive 8 maximum active connections: Maximum number of active connections that can be allocated by the connection pool at the same time. If this parameter is set to a non-positive value, no limit is imposed.
MaxIdle 8 maximum idle connections: the maximum number of connections allowed to remain idle in the connection pool. idle connections exceeding the limit will be released.
MinIdle 0 min idle connections: the minimum number of connections allowed to remain idle in the connection pool. A connection smaller than this number will create a new connection. If it is set to 0, no new connections will be created.
MaxWait unlimited maximum wait time: when no connection is available, the maximum wait time (in milliseconds) for the connection pool to wait for the connection to be returned. If it is set to-1, an exception is thrown.

Health Check of data source connection:
Default parameter description
ValidationQuery SQL query is used to verify the connection obtained from the connection pool. If this parameter is specified before the connection is returned to the caller, the query must be an SQL SELECT statement and at least one row of records must be returned.
TestOnBorrow true indicates whether to perform a test before removing the connection from the pool. If the test fails, remove the connection from the pool and try to retrieve another one.
Note: If this parameter is set to true, the validationQuery parameter must be set to a non-null string.
TestOnReturn false indicates whether the test is performed before return to the pool.
Note: If this parameter is set to true, the validationQuery parameter must be set to a non-null string.
TestWhileIdle false indicates whether the connection is verified by the idle connection recycler (if any). If the detection fails, the connection will be removed from the pool.
Note: If this parameter is set to true, the validationQuery parameter must be set to a non-null string.
TimeBetweenEvictionRunsMillis-1 time value of sleep during idle connection to the recycler thread, in milliseconds. If it is set to a non-positive value, the idle connection to the recycler thread is not run.
NumTestsPerEvictionRun 3 check the number of connections during each idle connection to the recycler thread (if any)
MinEvictableIdleTimeMillis 1000*60*30 minimum time for a connection to stay idle in the pool and not to be collected by idle connection recycler threads (if any), in milliseconds

Cache statement:
Default parameter description
PoolPreparedStatements false enable the pool's prepared statement pool Function
MaxOpenPreparedStatements does not limit the maximum number of statements that can be opened by the statement pool at the same time. If it is set to 0, no limit is imposed.

Connection leakage recovery:
Default parameter description
RemoveAbandoned false indicates whether to delete the leaked connection if they exceed the removeAbandonedTimout limit. if this parameter is set to true, the connection is considered leaked and can be deleted. If the idle time exceeds removeAbandonedTimeout. if it is set to true, it can be used to fix the database connection for a program with no closed connection.
RemoveAbandonedTimeout 300 indicates the timeout value that the leaked connection can be deleted, in seconds.
LogAbandoned false indicates whether to print the stack traces log of the program when the Statement or connection is leaked. The leaked Statements and connection logs are added to each connection to open or generate a new Statement because stack trace needs to be generated.

Note:
The Java database connection has an "eight-hour problem", so destroy-method = "close" must be added. The "eight-hour problem" means that the database is automatically closed when a connection is idle for eight hours, but the data source is unknown.
In highly concurrent scenarios, you can set testOnBorrow to false and testWhileIdle to true. In this way, empty links in the background are detected regularly and useless connections are cleared, it does not check whether there is an empty link for 8 hours every time.