In Spring, there are three ways to create a data source:

1. Get the data source in the application server through JNDI;
2. Configure the data source in the Spring container;
3. Create the data source in code, which is appropriate for unit testing without container dependencies.

1 Configuring a Data Source

Spring includes implementation packages for two data sources in third-party dependencies: Apache DBCP; The other one is C3P0. We can configure these data sources directly in the Spring configuration file.

1.1 DBCP

The Database Connection Pool (DBCP) is a Database Connection Pool that relies on the Jakarta Commons-pool object Pool mechanism, so commons-pool.jar must also be included in the classpath. Here is a configuration fragment that uses DBCP to configure the MySql data source:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">        
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />       
    <property name="url" value="jdbc:mysql://localhost:3309/db" />       
    <property name="username" value="root" />       
    <property name="password" value="xxxxxx" />       
</bean>   
Copy the code

The BasicDataSource provides the close() method to close the data source, so destroy-method= “close” must be set so that the data source can be closed properly when the Spring container is closed.

In addition to the required data source attributes, there are some common attributes.

Transaction attributes:

attribute	The default value	instructions
defaultAutoCommit	true	The connection status is auto-commit by default.
defaultReadOnly	Driver defaults	The default read-only state of the connection. If this is not set, the setReadOnly method will not be called. (Some drivers do not support read-only mode, such as Informix)
defaultTransactionIsolation	Driver defaults	Connect to the default TransactionIsolation state. There are values: NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, SERIALIZABLE.

Connection number-related attributes:

attribute	The default value	instructions
initialSize	0	Number of initial connections: The number of initial connections created when the connection pool is started.
maxActive	8	Maximum active Connections: The maximum number of active connections that the connection pool can allocate at any one time. If the value is set to a non-positive number, there is no limit.
maxIdle	8	Maximum number of idle connections: The maximum number of connections allowed to remain idle in the pool. If the value is set to negative, there is no limit.
minIdle	0	Minimum Free connections: The minimum number of connections allowed to remain idle in the connection pool. If this number is lower than this, new connections will be created. If set to 0, no connections will be created.
maxWait	infinite	Max Wait time: The maximum amount of time, in milliseconds, that a connection pool can wait for a connection to be returned when no connection is available, beyond which an exception will be thrown. If set to -1, the wait is infinite.

Connection monitoring and maintenance related attributes:

attribute	The default value	instructions
validationQuery	There is no	Configure an SQL query to verify that the connection fetched from the connection pool is available. If specified, the query must be an SQL SELECT and must return at least one row of records. MySQL > select 1; Select 1 from Dual
testOnBorrow	true	Indicates whether to test before removing a connection from the pool and, if the test fails, to remove the connection from the pool and try to retrieve another new connection.Pay attention to: The validationQuery parameter must be set correctly for this parameter to take effect.
testOnReturn	false	Indicates whether to test before returning to the pool.Pay attention to: As with testOnBorrow, the validationQuery parameter must be set correctly for this parameter to take effect.
testWhileIdle	false	Indicates whether the connection will be detected by the idle connection collector (if any). If the detection fails, the connection is removed from the pool.Pay attention to: The validationQuery parameter must be set correctly for this parameter to take effect.
timeBetweenEvictionRunsMillis	– 1	The period in milliseconds that the idle connection collector thread runs. If set to non-positive, the idle connection collector thread is not run.Pay attention to: When this parameter is enabled, the validationQuery parameter must be set correctly.
numTestsPerEvictionRun	3	The number of connections that need to be detected each time the idle connection collector thread (if any) runs.
minEvictableIdleTimeMillis	1000 times 60 times 30	The minimum amount of time, in milliseconds, that a connection can remain idle in the pool without being reclaimed by the idle connection collector thread.

Cache-related attributes:

attribute	The default value	instructions
poolPreparedStatements	false	If prepared Statement is set to True, all callableStatements and PreparedStatements will be cached.
maxOpenPreparedStatements	Don’t limit	The maximum number of open statements that can be assigned simultaneously. 0 indicates no limit.

Connection leak recovery related attributes:

attribute	The default value	instructions
removeAbandoned	false	Whether to delete the leaked connection. If set to true, connections that might be compromised are deleted. Assuming 10 maxActive connections, 8 active connections, and 1 idle connection,The 10-8-1 = 1The removeAbandonedTimeout is used to check whether the active connection has been unused for longer than the removeAbandonedTimeout. If a long connection is required, removeAbandoned will need to be longer, otherwise a normal connection will be removed by mistake.
removeAbandonedTimeout	300	The period, in seconds, during which leaked connections can be deleted.
logAbandoned	false	Whether to print a stack log when a Statement or connection is leaked.

Assuming the database is MySQL, the classic “8-hour problem” can occur if the data source is not configured properly. The reason for this is that bY default MySQL will automatically close a connection on the database side if it has been idle for more than 8 hours. The data source doesn’t know that the connection is closed, so when it returns the useless connection to a DAO, the DAO throws an exception that it can’t get a Connection.

DBCP’s testOnBorrow is set to true by default, so connections are checked before they are taken out of the connection pool because the “8-hour problem” doesn’t occur. However, if detection is performed every time a connection is fetched, performance problems can occur in high-concurrency applications.

Therefore, it is recommended that testOnBorrow be set to false for high concurrency; TestWhileIdle set testWhileIdle to true to enable the idle connection collector. Finally put timeBetweenEvictionRunsMillis value set to less than eight hours, so that was shut down by the MySQL free connection, will be cleared out. This not only solves the “8-hour problem”, but also ensures high performance

* * note: * * because MySQL itself interactive – timeout (in s) parameters can be set free connection of expiration time, so we want to get to this parameter value, and then configure DBCP timeBetweenEvictionRunsMillis attribute values.

1.2 C3P0

C3P0 is an open source JDBC data source implementation project that implements the Connection and Statement pools specified by the JDBC3 and JDBC2 extension specifications.

Here is the configuration fragment for configuring the MySql data source using C3P0:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">        
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver" />       
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3309/db" />       
    <property name="use" value="xxx" />       
    <property name="password" value="xxxxxx" />       
</bean>   
Copy the code

C3P0 also provides a close() method to close the data source, so that we can be sure that the Spring container will successfully close the data source when it is shut down.

attribute	The default value	instructions
acquireIncrement		The number of new connections created at one time when there are no free connections in the connection pool.
acquireRetryAttempts	30	The number of repeated attempts after obtaining a new connection from the database failed.
acquireRetryDelay	1000	Try to get the interval between connections in milliseconds.
autoCommitOnClose	false	When the connection is closed, all uncommitted operations are rolled back.
automaticTestTable	null	An empty table named Test is created and tested using its own query statement. If this parameter is defined, the preferredTestQuery attribute is ignored. We cannot do anything on this Test table, which is only used for C3P0 tests.
breakAfterAcquireFailure	false	Failure to acquire a connection causes all threads waiting to acquire a connection to throw an exception. However, the data source remains in effect, and the next time you call getConnection(), you continue to try to get the connection. After an attempt to obtain a connection fails, the data source declares that it is disconnected and permanently closed.
checkoutTimeout	0	The amount of time, in milliseconds, that the client waits to get a new connection after calling getConnection() when the pool runs out of connections. An SQLException is thrown after a timeout. If this parameter is set to 0, the wait is indefinite.
connectionTesterClassName	com.mchange.v2.C3P0.impl.DefaultConnectionTester	The connection is tested by a class that implements ConnectionTester or QueryConnectionTester with a fully qualified name.
idleConnectionTestPeriod	0	How many seconds to check all idle connections in the connection pool. 0 indicates no check.
initialPoolSize	3	The number of connections created during initialization should be between minPoolSize and maxPoolSize.
maxIdleTime	0	Maximum idle time. Connections exceeding the idle time will be discarded. A value of 0 or negative indicates never discarding.
maxPoolSize	15	Maximum number of connections reserved in the connection pool.
maxStatements	0	A JDBC standard parameter used to control the number of PreparedStatements loaded in the data source. However, the pre-cached Statement belongs to a single Connection rather than the entire Connection pool. So, set this parameter to many considerations, if maxStatements and maxStatementsPerConnection is 0, the cache is closed.
maxStatementsPerConnection	0	Maximum number of cache statements that can be held by a single connection in the connection pool.
numHelperThreads	3	C3P0 operates asynchronously, and slow JDBC operations are done with HelperThreads. Multiple operations can be executed simultaneously through multi-threading, which can effectively improve performance.
preferredTestQuery	null	Defines test statements that all connection tests execute. In the case of connection tests, this parameter can significantly improve test speed. The table to be tested must exist at the time of the original data source.
propertyCycle	300	Duration for modifying system configuration parameters (unit: s).
testConnectionOnCheckout	false	Because of high performance consumption, please enable it only when required. If set to true, each connection commit is validated. Methods such as idleConnectionTestPeriod or automaticTestTable are recommended to improve the performance of connection tests.
testConnectionOnCheckin	false	If set to true, the validity of the connection is verified when the connection is acquired.

2 JNDI Data source

If the application is configured on a high-performance application server (such as WebLogic or Websphere), we may prefer to use the data source provided by the application server. The application server’s data source is used by the caller in JNDI mode, and Spring specifically provides a JndiObjectFactoryBean class that references JNDI resources. Here is a simple configuration:

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean"
	  p:jndiName="java:comp/env/jdbc/ds"/>
Copy the code

Spring2.0+ provides a jee namespace for obtaining J2EE resources, which effectively simplifies referencing J2EE resources:

<? xml version="1.0" encoding="UTF-8"? > <beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:jee="http://www.springframework.org/schema/jee"
       xsi:schemaLocation="Http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd">

    <jee:jndi-lookup id="dataSource" jndi-name="java:comp/env/jdbc/ds"/>

</beans>
Copy the code

Spring data source implementation class

Spring itself also provides a simple data source implementation class DriverManagerDataSource, it is located in org. Springframework). The JDBC datasource bags. This class implements the Javax.sql.datasource interface, but it does not provide a pooled connection mechanism and simply creates a new connection each time the getConnection() method is called to retrieve a new connection. It does not require additional dependent classes, so the data source class is suitable for use in unit testing.

The Spring data source implementation class can be used either directly through configuration or instantiated in code:

DriverManagerDataSource dataSource=new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("JDBC: mysql: / / 127.0.0.1:3306 / spring4");
dataSource.setUsername("root");
dataSource.setPassword("");

try {
	Connection connection=dataSource.getConnection();
	if(connection.isClosed()){
		System.out.println("Connection closed");
	}else{
		System.out.println("Connection open");
	}
} catch (SQLException e) {
	e.printStackTrace();
}
Copy the code