problem

A strange problem with a project recently is that the database often reports that the connection is closed every few hours.

Even with the following configuration, it is still not possible, and there is no article on the Internet explaining the pit.

test-on-borrow: true
test-while-idle: true
validation-query: select 1 from dual

Troubleshooting

If you can't find it online, you can only speculate on it by yourself. Because mybatis is a configuration of multiple data sources, I have a special config for each db as a connection configuration.

Looking at the code of DataSourceConfig, I suddenly wondered if it was because I used the default method of creating DataSource, so I didn't read the configuration I wrote in application.yml:

 @Bean(name = "db1DataSource")
 @ConfigurationProperties(prefix = "spring.datasource.db1")
 @Primary
 public DataSource dbDataSource() {
 return DataSourceBuilder.create().build();
 }

Follow up build() method decisively

	public DataSource build() {
		Class<? extends DataSource> type = getType();
		DataSource result = BeanUtils.instantiate(type);
		maybeGetDriverClassName();
		bind(result);
		return result;
	}

A breakpoint shows that the result returned at this point is a completely new DataSource

So we can write our configuration parameters by modifying the dbDataSource() method:

 @Value("${spring.datasource.db1.url}")
 private String url;
 @Value("${spring.datasource.db1.username}")
 private String username;
 @Value("${spring.datasource.db1.password}")
 private String password;
 @Value("${spring.datasource.db1.tomcat.test-on-borrow}")
 private boolean testOnBorrow;
 @Value("${spring.datasource.db1.tomcat.test-while-idle}")
 private boolean testWhileIdle;
 @Value("${spring.datasource.db1.tomcat.validation-query}")
 private String validationQuery;
 @Value("${spring.datasource.db1.tomcat.max-idle}")
 private int maxIdle;
 @Value("${spring.datasource.db1.tomcat.min-idle}")
 private int minIdle;
 @Value("${spring.datasource.db1.tomcat.initial-size}")
 private int initialSize;
 @Value("${spring.datasource.db1.tomcat.max-active}")
 private int maxActive;
 @Value("${spring.datasource.db1.tomcat.time-between-eviction-runs-millis}")
 private int timeBetweenEvictionRunsMillis;
 @Bean(name = "db1DataSource")
 @ConfigurationProperties(prefix = "spring.datasource.db1")
 @Primary
 public DataSource dbDataSource() {
 org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource();
 dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
 dataSource.setUrl(url);
 dataSource.setUsername(username);
 dataSource.setPassword(password);
 dataSource.setMaxActive(maxActive);
 dataSource.setMinIdle(minIdle);
 dataSource.setMaxIdle(maxIdle);
 dataSource.setTestOnBorrow(testOnBorrow);
 dataSource.setTestWhileIdle(testWhileIdle);
 dataSource.setValidationQuery(validationQuery);
 dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
 dataSource.setInitialSize(initialSize);
 return dataSource;
 //return DataSourceBuilder.create().build();
 }

Secondary investigation

The above method can really solve the problem, but it suddenly occurred to me that the default creation of DataSource does not have the necessary basic information such as url, username, password, etc. So why can these configuration parameters be written in again?

At this point, I saw an @Bean (name = db1DataSource) on our dbDataSource method, so I dared to guess that the injection of our configuration parameters was first created through Spring's IOC injection. Through my Debug, I found that this is true.

Interruption points for the bind method of the DataBinder class,

	public void bind(PropertyValues pvs) {
		MutablePropertyValues mpvs = (pvs instanceof MutablePropertyValues) ?
				(MutablePropertyValues) pvs : new MutablePropertyValues(pvs);
		doBind(mpvs);
	}

We can see the call path of the method.

See our familiar refresh method, this part of Spring source code related see Spring source code analysis

At this point, there is only one last doubt. Since our url, username, password and so on can be injected into DataSource through IOC, why not other parameters? As I went up the DataSource class, I found its parent interface, PoolConfiguration, and saw all the parameters and getset methods.

Take another look at the parameters in my application.yml configuration file

spring:
 datasource:
 db1:
 url: 
 username: 
 password: 
 driver-class-name: oracle.jdbc.driver.OracleDriver
 tomcat:
 max-wait: 10000
 max-active: 30
 test-on-borrow: true
 max-idle: 5
 db2:
 xxx
 ....

Find the pit at last!

When Spring data used tomcat-jdbc connection pool by default, the configuration parameters were

spring:
 datasource:
 url: 
 username: 
 password: 
 driver-class-name: oracle.jdbc.driver.OracleDriver
 tomcat:
 max-wait: 10000
 max-active: 30
 test-on-borrow: true
 max-idle: 5

When using multiple data source configurations, it is simply assumed that the past can be copied, so when Spring IOC is injected, the tomcat.max-wait read does not match the setMaxWait method in DataSource. Naturally it doesn't work.

So this problem only needs to change the configuration file to the following.

spring:
 datasource:
 db1:
 url: 
 username: 
 password: 
 driver-class-name: oracle.jdbc.driver.OracleDriver
 max-wait: 10000
 max-active: 30
 test-on-borrow: true
 test-while-idle: true
 validation-query: select 1 from dual
 max-idle: 5
 db2:
 xxx
 ....

summary

From the result of this question, it is really simple, but from the process, not only let me review the process of spring IOC again, but also let me feel this step by step dissection of the problem, the achievement of connecting multiple knowledge points. If I hadn't studied Spring's source code before, I probably wouldn't have thought of seeing bean s injected this time.