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:

Connection number-related attributes:

Connection monitoring and maintenance related attributes:

Cache-related attributes:

Connection leak recovery related attributes:

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.

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