Spring Boot's data source and connection pool

• ? Create by [email protected] 2018-8-2

One: Dependence

Using spring boot's default data source spring.datasource requires only the following dependencies to be imported:

<dependency>    <groupId>org.springframework.boot</groupId> ???????????    <artifactId>spring‐boot‐starter‐jdbc</artifactId> ???????????</dependency> ???????<dependency> ???????    <groupId>mysql</groupId> ???????????    <artifactId>mysql‐connector‐java</artifactId> ???????????    

Or you are using JPA:

<dependency>     <groupId>org.springframework.boot</groupId>     <artifactId>spring-boot-starter-data-jpa</artifactId></dependency>

Look at the JPA dependencies, which already contain JDBC.

Two: Data source

To configure our MySQL database connection information:

spring:??datasource:????username:?root????password:?123456????url:?jdbc:mysql://192.168.15.22:3306/jdbc????driver‐class‐name:?com.mysql.jdbc.Driver

1. How do I view the current data source?

Writing unit Tests

@RunWith(SpringRunner.class)@SpringBootTestpublic class RobotsApplicationTests {    @Autowired    DataSource dataSource;    @Test    public void test(){        System.out.println(dataSource.getClass());    }}

View Print:

class org.apache.tomcat.jdbc.pool.DataSource

Summarize

So the effect of this configuration is that the default is to use Org.apache.tomcat.jdbc.pool.DataSource as the data source,

And the relevant configuration of the data source is inside the datasourceproperties, as follows:

@ConfigurationProperties(prefix = "spring.datasource")public class DataSourceProperties        implements BeanClassLoaderAware, EnvironmentAware, InitializingBean {    ...    private String name = "testdb";    private String driverClassName;    private String url;    private String username;        private String password;        .....

2. Automatic configuration principle

Find org.springframework.boot.autoconfigure.jdbc the class under the package DataSourceConfiguration

Abstract class Datasourceconfiguration {@ConditionalOnClass (org.apache.tomcat.jdbc.pool.DataSource.class) @Conditi Onalonproperty (name = "Spring.datasource.type", Havingvalue = "Org.apache.tomcat.jdbc.pool.DataSource", Matchifmissing = True) Static class Tomcat extends Datasourceconfiguration {@Bean @ConfigurationProperti Es (prefix = "spring.datasource.tomcat") Public Org.apache.tomcat.jdbc.pool.DataSource DataSource (Da                    Tasourceproperties properties) {Org.apache.tomcat.jdbc.pool.DataSource DataSource = CreateDataSource (            Properties, Org.apache.tomcat.jdbc.pool.DataSource.class);            Databasedriver databasedriver = databasedriver. Fromjdbcurl (Properties.determineurl ());            String validationquery = Databasedriver.getvalidationquery ();                if (validationquery! = null) {Datasource.settestonborrow (true); Datasource.setvalidationquery (Validationquery);        } return DataSource; }    }    ......

The above is the automatic configuration code, the principle is probably if the class exists under Classpath, org.apache.tomcat.jdbc.pool.DataSource.class and the value specified in the configuration file spring.datasource.type is org.apache.tomcat.jdbc.pool.DataSource , or does not write will be considered to pass. You can inject beans only by entering this configuration code DataSource .

Springboot can be supported by default;

org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource、

Of course, other than the Tomcat data source is self-reliant, others are missing states.

3. Customizing the data source

To find the bottom of this class, if spring.datasource.type the value does not belong to the above, then you can define your own data source:

    @ConditionalOnMissingBean(DataSource.class)    @ConditionalOnProperty(name = "spring.datasource.type")    static class Generic {        @Bean        public DataSource dataSource(DataSourceProperties properties) {            //使用DataSourceBuilder创建数据源，利用反射创建响应type的数据源，并且绑定相关属性            return properties.initializeDataSourceBuilder().build();        }    }

4. Automatic execution of SQL statements

Opens the Datasourceautoconfiguration automatic configuration class, which is injected when the DataSource is automatically configured, and DataSourceInitializer continues to open the class.

We find that the class has a method annotated @PostConstruct , which is used to perform any initialization method after the dependency injection is complete. The initialization method calls therunSchemaScripts();

The first sentence of the method calls the getScripts() method, gets the SQL script,

So we want to initialize some database scripts that we can follow in this rule

schema‐*.sql、data‐*.sql

For example:

schema.sql，schema‐all.sql；

You can also use the following to specify a specific location

??schema: ?????????‐?classpath:department.sql

Role:

1), runschemascripts (); Run the build table statement;

2), rundatascripts (); Run the SQL statement that inserts the data;

5. Operational database

The JdbcTemplate operations database is automatically configured, example:

@RunWith(SpringRunner.class)@SpringBootTestpublic class RobotsApplicationTests {    @Autowired    JdbcTemplate jdbcTemplate;    @Test    public void test(){        jdbcTemplate.queryForList("SELECT * FROM user");    }}

Three: Connection pool

Why put the data source and the connection pool together, because when we use the default data source as described above, the database link pool is enabled by default. In other words, you don't have to care about the connection pool at all, it's already there!

1. Default Connection Pool Rules

Before TOMCAT7, Tomcat essentially applied the JDBC data source implemented by DBCP connection pooling technology, but after TOMCAT7, Tomcat provided a new JDBC connection pooling scheme as a replacement or alternative to DBCP, addressing many of the disadvantages of using dbcp before, and improves performance. For more information, please refer to: http://wiki.jikexueyuan.com/project/tomcat/tomcat-jdbc-pool.html

Spring Boot prepares us for the best database connection pool scenario by configuring the required connection pool parameters in the properties file (for example, application.properties).

After the introduction of the SPRING-BOOT-STARTER-JDBC, the internal TOMCAT-JDBC package is included with the Tomcat connection pool. The DataSource object is then created by automating the configuration Datasourceautoconfigurer.

Springboot when creating a default DataSource, the rules are as follows:

• First look for creating a Tomcat connection pool

• If you do not have a Tomcat connection pool, you will find the Create HIKARICP

• If you do not have a HIKARICP connection pool, you will find the Create DBCP

• If you do not have a DBCP connection pool, you will find the Create DBCP2

• You can use the Spring.datasource.type property to specify the connection pool type

  spring.datasource.type=org.apache.commons.dbcp.BasicDataSource

2. Controlling Connection Pool behavior

In the data source talk, we already know that spring data uses TOMCAT-JDBC by default, so add configuration item spring.datasource.tomcat.* directly in APPLICATION.YML to control the behavior of the link pool. such as the following configuration.

spring:    datasource:        url: jdbc:mysql://localhost:3306/jackieathome?useSSL=false        username: root        password: mypassword        # 6.x版本的MySQL JDBC驱动类为com.mysql.cj.jdbc.Driver        # 5.X版本的MySQL JDBC驱动类为com.mysql.jdbc.Driver        driver-class-name: com.mysql.cj.jdbc.Driver        tomcat:            max-wait: 10000            max-active: 30            test-on-borrow: true            max-idle: 5

3.Tomcat Common Properties

Properties	Description
defaultAutoCommit	(Boolean) The default auto-commit status of connections created by connection pooling. If not set, the default JDBC-driven default value is used (if not set, the method is not called setAutoCommit ).
defaultReadOnly	(Boolean) The connection pool creates a default read-only state for connections. If not set, the method will not be called setReadOnly . (Some drivers do not support read-only mode, such as Informix)
defaultTransactionIsolation	(string) The default transaction isolation state of the connection created by the connection pool. The value range is: (reference Javadoc) NONE``READ_COMMITTED``READ_UNCOMMITTED``REPEATABLE_READ``SERIALIZABLE If the value is not set, no method is called and the JDBC driver is the default.
defaultCatalog	(string) The default catalog of connections created by the connection pool.
driverClassName	The fully qualified Java class name (string) of the JDBC driver to use. The driver must be accessible from the same class loader as the Tomcat-jdbc.jar
username	(string) to the JDBC driver in order to establish the connection user name for the connection. Note that the DataSource.getConnection(username,password) method does not use the credentials passed in to the method by default, but uses the configuration information here. See for alternateUsernameAllowed more details.
password	(string) to the JDBC driver in order to establish a connection password. Note that the DataSource.getConnection(username,password) method does not use the credentials passed in to the method by default, but uses the configuration information here. See for alternateUsernameAllowed more details.
maxActive	The maximum number of active connections that the pool can allocate at the same time (shaping value). The default is 100 .
maxIdle	The maximum number of connections (integer values) that the pool should always keep. The default is maxActive:100 . Idle connections are periodically checked (if enabled), and idle connections that detention time are minEvictableIdleTimeMillis freed. (Please refer to testWhileIdle )
minIdle	(integer value) the minimum number of connections that the pool should always keep. If the validation query fails, the connection pool shrinks the value. The default value is taken from initialSize:10 (refer to testWhileIdle ).
initialSize	(integer value) The number of initial connections that were created when the connector started. The default is 10 .
maxWait	(integer value) The maximum time, in milliseconds, that a connection pool waits (when no connection is available) to return a connection before throwing an exception. Default is 30000 (30 seconds)
testOnBorrow	(Boolean) The default value is false . Whether to validate the object before it is loaned out from the pool. If the object fails validation, clears it from the pool and then borrows the next. Note: In order for true the value to take effect, the validationQuery parameter must be a non-empty string. To achieve more efficient validation, you can use the validationInterval .
testOnReturn	(Boolean) The default value is false . The alignment is validated before returning the object to the pool. Note: In order for true the value to take effect, the validationQuery parameter must be a non-empty string.
testWhileIdle	(boolean value) whether the object is validated by the free object cleaner, if present. If the object validation fails, it is purged from the pool. Note: In order for true the value to take effect, the validationQuery parameter must be a non-empty string. The default value for this property is false that the value must be set in order to run the Purge/test thread for the pool. (See also timeBetweenEvictionRunsMillis )
validationQuery	(string) The SQL query used to validate these connections before returning the connections in the pool to the caller. If this value is specified, the query does not have to return any data, except that it does not throw an SQLException exception. The default is null . The instance value is: SELECT 1 (mysql) ( select 1 from dual Oracle) ( SELECT 1 MySQL Server).
validationQueryTimeout	(integer value) the time-out (in seconds) before the connection validation fails. Implemented by invoking on the executed validationQuery statement java.sql.Statement.setQueryTimeout(seconds) . The pool itself does not cause the query to time out, and is fully enforced by JDBC. If the value is less than or equal to 0, the feature is disabled. The default is -1 .
validatorClassName	(string) implements org.apache.tomcat.jdbc.pool.Validator the interface and provides a class name for a parameterless (possibly implicit) constructor. If this value is specified, a Validator instance is created through the class to validate the connection instead of any validation query. The default is null , the example value is: com.mycompany.project.SimpleValidator .
timeBetweenEvictionRunsMillis	(integer value) the sleep time (in milliseconds) between idle connection validation/cleanup thread runs. cannot be less than 1 seconds. This value determines how often we check for idle connections, the frequency of discarded connections, and how often we validate idle connections. Default is 5000 (5 seconds)
numTestsPerEvictionRun	(integer value) the Tomcat JDBC connection pool does not use this property.
minEvictableIdleTimeMillis	(integer value) the minimum time, in milliseconds, that an object remains idle in the pool before it is determined that it should be purged. Default is 60000 (60 seconds)
accessToUnderlyingConnectionAllowed	(Boolean) A property that is not used. can be accessed by calling on a connection that is placed within the pool unwrap . See javax.sql.DataSource An Introduction to an interface, or call through reflection, getConnection or map an object to a javax.sql.PooledConnection .
removeAbandoned	(Boolean) This value is the flag value, which means that if the connection time is exceeded removeAbandonedTimeout , the obsolete connection is cleared. If the value is set to true , if the connection time is greater than removeAbandonedTimeout , the connection is considered an obsolete connection and should be cleared. If the app closes a connection failure, set the value to be true able to restore the app's database connection. Please see also logAbandoned . The default value is false .
removeAbandonedTimeout	(integer value) the number of seconds before an obsolete connection (which is still in use) can be purged. The default is 60 (60 seconds). This value should be set to the longest running query that the app might have.
logAbandoned	The (Boolean) flag can be used to record stack traces for application code that discards the connection. Because of the build stack trace, logging of obsolete connections increases the overhead of each borrowed connection. Default isfalse
connectionProperties	(string) The connection properties sent to the JDBC driver when a new connection is established. The string format must be: [Propertyname=property;] *。 Note: The user and Password properties are explicitly passed in, so there is no need to include them here. Default isnull。
poolPreparedStatements	Property not Used (Boolean value)
maxOpenPreparedStatements	(integer value) unused property

4.Tomcat JDBC Enhanced Properties

Properties	Description
initSQL	The string value. A custom query that runs when the connection is first created. The default value is null .
jdbcInterceptors	String. org.apache.tomcat.jdbc.pool.JdbcInterceptora list of subclass class names that inherit from a class, separated by semicolons. For formatting and examples, see Configuring the JDBC interceptor below. These interceptors will be inserted into java.sql.Connection the object's action queue. Pre-defined interceptors are: org.apache.tomcat.jdbc.pool.interceptor``ConnectionState -record autocommit, read-only, catalog, and transaction isolation levels. org.apache.tomcat.jdbc.pool.interceptor``StatementFinalizer--Record open statements and close them when the connection returns to the pool. For a detailed description of more predefined interceptors, refer to the JDBC Interceptor
validationInterval	The long integer value. The frequency time value (in seconds) that is set to avoid over-validation. Run validation at this frequency at most. If the connection should be validated but not verified during this interval, it will be re-validated. The default is 30000 (30 seconds).
jmxEnabled	Boolean value. Whether to register the connection pool with JMX. The default is true .
fairQueue	Boolean value. If you want to treat the call fairly with a true FIFO getConnection , take the value true . The implementation is used for the list of idle connections org.apache.tomcat.jdbc.pool.FairBlockingQueue . The default value is true . You must use this flag if you want to use an asynchronous connection to get functionality. Setting this flag ensures that threads receive connections in the order in which they are connected. In performance testing, there is a big difference in how locks and lock waits are implemented. When fairQueue=true , depending on the operating system that is running, there is a decision process. If the system is running on a Linux operating system (properties), in os.name = linux order to prohibit this Linux proprietary behavior, but still want to use a fair queue, then simply before the connection pool class load, will be org.apache.tomcat.jdbc.pool.FairBlockingQueue.ignoreOS=true added to the system properties.
abandonWhenPercentageFull	The value of the integral type. Unless the number of connections in use exceeds the abandonWhenPercentageFull percentage defined in, the discarded connection is not closed and reported (because it timed out). The value range is 0-100. The default value is 0, which means that the removeAbandonedTimeout connection should be closed as soon as it is reached.
maxAge	The long integer value. Connection hold time (in milliseconds). When the connection is to be returned to the pool, the connection pool checks whether the condition is reached, now - time-when-connected > maxAge closes the connection if the condition is reached, and no longer returns it to the pool. The default value 0 , which means that the connection will remain open, does not perform any age checks when the connection is returned to the pool.
useEquals	Boolean value. If you want the class to be ProxyConnection used String.equals , set the value to and true , if you want to use it when comparing the name of the method == , set it to false . This property cannot be used in any of the interceptors that have been added, because those interceptors are configured separately. The default value is true .
suspectTimeout	The value of the integral type. The time-out (in seconds). The default value is 0 . Similar to removeAbandonedTimeout , but does not treat the connection as an obsolete connection and may close the connection. If logAbandoned set to true , it will only record the warning. If the value is less than or equal to 0, no suspicious checks are performed. A suspicious check is performed if the timeout value is greater than 0 and the connection has not been discarded, or if the discard check is disabled. If a connection is suspected, the WARN information is logged and a JMX notification is sent.
rollbackOnReturn	Boolean value. If, when the connection is returned to the autoCommit==false pool, the pool calls the Rollback method on the connection, thereby terminating the transaction. The default value is false .
commitOnReturn	Boolean value. If autoCommit==false , then, when the connection is returned to the pool, the pool invokes the commit method on the connection, which completes the transaction, or if it rollbackOnReturn==true ignores the property. The default value is false .
alternateUsernameAllowed	Boolean value. For performance reasons, the JDBC connection pool defaults to ignoring the DataSource.getConnection(username,password) call, returning only the connections that were previously pooled with global configuration Properties username password . But configured, the connection pool can also allow different credentials to be used to request each connection. In order to enable this DataSource.getConnection(username,password) feature described in the call, simply alternateUsernameAllowed set the true . If you request a connection that is User 1/password 1, and the user 2/password 2 credentials are used before the connection, the connection is closed and the requested credentials are re-opened. In this way, the pool's capacity is always managed at the global level, not limited to the schema level. The default value is false . This property is added to bug 50025 as an improvement scenario.
dataSource	(Javax.sql.DataSource) injects the data source into the connection pool, allowing the pool to take advantage of the data source to obtain the connection, rather than using the java.sql.Driver interface to establish the connection. It is ideal for pooling XA connections or established connections using a data source rather than a connection string. The default value is null .
dataSourceJNDI	String. The Jndi name of the data source found in Jndi, which is then used to establish the database connection. See datasource the description of the attribute. The default value is null .
useDisposableConnectionFacade	Boolean value. If you want to place a façade object on the connection so that the connection cannot be reused after it is closed, set the value to true . This prevents the thread from continuing to reference a connection that has been closed and continues to query on the connection. The default value is true .
logValidationErrors	Boolean value. When set to true , the error in the validation phase can be logged to the log file, and the error is logged as SEVERE. Given backward compatibility, the default value is false .
propagateInterruptState	Boolean value. Propagates the interrupted state of an interrupted thread (which has not yet cleared the interrupt state). Given backward compatibility, the default value is false .
ignoreExceptionOnPreLoad	Boolean value. Whether to ignore connection creation errors when initializing the pool. The value is true ignored, and false when set, throws an exception, declaring that the pool initialization failed. The default value is false .

This article original, reproduced please indicate the source!

Spring Boot's data source and connection pool