Spring Data Source Management

Spring can manage data sources independently without relying on containers, but relies on a third-party open-source data source management framework.

Spring contains Implementation Packages for two data sources in the third-party dependency package. One is Apache DBCP and the other is c3p0. You can use any of the two configuration data sources in the spring configuration file.

DBCP Data source:
DBCP class package is located in/lib/Jakarta-commons/commons-dbcp.jar, DBCP is a database connection pool dependent on Jakarta commons-pool Object pool mechanism,

Therefore, the/lib/Jakarta-commons/commons-pool.jar must be included in the class path.

The following is the configuration snippet for configuring the MySQL data source using DBCP:

<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/sampledb" />              <property name="username" value="root" />              <property name="password" value="1234" />          </bean>     

Basicdatasource provides the close () method to close the data source. Therefore, you must set the destroy-method = "close" attribute so that when the spring container is closed,

The data source can be closed normally. In addition to the required data source attributes, there are also some common attributes:

Defaultautocommit: sets whether to use the automatic submission mechanism for connections returned from the data source. The default value is true;
Defaultreadonly: sets whether the data source can only perform read-only operations. The default value is false;
Maxactive: Maximum number of connections to the database. If it is set to 0, there is no limit;
Maxidle: Maximum number of waiting connections. If it is set to 0, there is no limit;
Maxwait: the maximum number of waiting seconds. The unit is milliseconds. If the wait time is exceeded, an error message is returned;
Validationquery: the SQL statement used to verify whether the connection is successful. The SQL statement must return at least one row of data. For example, you can simply set it to "select count (*) from user ";
Removeabandoned: whether to interrupt itself. The default value is false;
Removeabandonedtimeout: After several seconds, the data connection is automatically disconnected. If removeabandoned is set to true, this value is provided;
Logabandoned: Indicates whether to record the interrupt event. The default value is false;

C3p0 Data Source

C3p0 is an open-source JDBC Data Source implementation project. It is released in the lib directory together with hibernate to implement the connection and statement pools described in the jdbc3 and jdbc2 extension specifications. C3p0 class package is located in/lib/c3p0/c3p0-0.9.0.4.jar. The following describes how to configure an ORACLE 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:oracle:thin:@localhost:1521:ora9i "/>              <property name="user" value="admin"/>              <property name="password" value="1234"/>          </bean> 

Like basicdatasource, combopooleddatasource provides a close () method to close the data source. This ensures that the data source can be successfully released when the spring container is closed.

C3p0 has richer configuration attributes than DBCP. Through these attributes, you can control the data source effectively:

Acquireincrement: When connections in the connection pool are used up, c3p0 creates the number of new connections at a time;

Acquireretryattempts: Defines the number of times that a new connection fails to be retrieved from the database. The default value is 30;

Acquireretrydelay: Interval between two connections, in milliseconds. The default value is 1000;

Autocommitonclose: When the connection is closed, all uncommitted operations are rolled back by default. The default value is false;

Automatictesttable: C3p0 creates an empty table named test and uses its own query statement for testing. If this parameter is defined, preferredtestquery is ignored.

You cannot perform any operation on this test table. It will be used for the c3p0 test. The default value is null;

Breakafteracquirefailure: Failed connection retrieval will cause all threads waiting for connection retrieval to throw an exception. However, the data source is still valid,

Continue to get the connection when getconnection () is called next time. If it is set to true, the data source will be declared disconnected and permanently closed after the connection fails to be obtained. The default value is false;

Checkouttimeout: When the connection pool is used up, the client calls getconnection () and waits for the time to obtain the new connection. After the timeout, sqlexception will be thrown. If it is set to 0, the client waits indefinitely. Unit: milliseconds. The default value is 0;

Connectiontesterclassname: Test the connection by implementing the connectiontester or queryconnectiontester class. The class name must be set to a fully qualified name.

The default value is com. mchange. v2.c3p0. impl. defaultconnectiontester;

Idleconnectiontestperiod: The number of seconds after which all idle connections in the connection pool are checked. The default value is 0, indicating no check;
Initialpoolsize: The number of connections created during initialization. The value should be between minpoolsize and maxpoolsize. The default value is 3;
Maxidletime: Maximum idle time. connections that exceed the idle time will be discarded. 0 or negative. The default value is 0;
Maxpoolsize: The maximum number of connections retained in the connection pool. The default value is 15;

Maxstatements: JDBC standard parameter, used to control the number of preparedstatement loaded in the data source. However, the pre-Cache statement belongs to a single connection rather than the entire connection pool.

Therefore, you need to consider multiple factors when setting this parameter. If both maxstatements and maxstatementsperconnection are 0, the cache is disabled. The default value is 0;

Maxstatementsperconnection: Maximum number of cached statement owned by a single connection in the connection pool. The default value is 0;

Numhelperthreads: C3p0 is asynchronous, and slow JDBC operations are completed by helping the process. Scaling these operations can effectively improve performance,

Multiple operations are executed simultaneously through multiple threads. The default value is 3;

Preferredtestquery: Define test statements executed for all connection tests. This parameter can significantly improve the test speed when connection tests are used.

The test table must exist at the initial data source. The default value is null;

Propertycycle: The maximum number of seconds that a user can wait before modifying system configuration parameters. The default value is 300;

Testconnectiononcheckout: Because of high performance consumption, please use it only when needed. If it is set to true, the validity of each connection is verified when it is submitted.

We recommend that you use idleconnectiontestperiod or automatictesttable to improve the connection test performance. The default value is false;

Testconnectiononcheckin: If it is set to true, the connection validity will be verified when the connection is obtained. The default value is false.

Reference properties by reading the configuration file:

<bean id="propertyConfigurer"         class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">              <property name="location" 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}" />          </bean>  

Define the property value in the JDBC. properties property file:
JDBC. driverclassname = com. MySQL. JDBC. Driver
JDBC. url = JDBC: mysql: // localhost: 3309/sampledb
JDBC. Username = root
JDBC. Password = 1234
It is often prompted that developers may accidentally enter spaces before and after $ {XXX}. These space characters will be merged with the variable and used as the attribute value. For example, there are spaces in the attribute configuration items before and after the parsing, And the username value is "1234", which will cause the final error, so be careful.

Obtain the 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 data source of the application server is opened to callers using JNDI. Spring specifically provides the jndiobjectfactorybean class that references the JNDI resources. The following is a simple configuration:

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">              <property name="jndiName" value="java:comp/env/jdbc/bbt"/>          </bean>     

Use jndiname to specify the name of the referenced JNDI data source.
Spring 2.0 provides a Jee namespace for obtaining J2EE resources. The Jee namespace can effectively simplify the reference of J2EE resources. The configuration for referencing the JNDI data source using the Jee namespace is as follows:

<beans xmlns=http://www.springframework.org/schema/beans        xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance        xmlns:jee=http://www.springframework.org/schema/jee        xsi:schemaLocation="http://www.springframework.org/schema/beans         http://www.springframework.org/schema/beans/spring-beans-2.0.xsdhttp://www.springframework.org/schema/jeehttp://www.springframework.org/schema/jee/spring-jee-2.0.xsd">          <jee:jndi-lookup id="dataSource" jndi-name=" java:comp/env/jdbc/bbt"/>          </beans>   

Spring Data Source implementation class
Spring also provides a simple data source implementation class drivermanagerdatasource, which is located in the org. springframework. JDBC. datasource package. This class implements the javax. SQL. datasource interface, but it does not provide a pooled Connection Mechanism. Every time you call getconnection () to obtain a new connection, you simply create a new connection. Therefore, this data source class is suitable for unit testing or simple independent applications because it does not require additional dependent classes.
Next, let's take a look at the simple use of drivermanagerdatasource: Of course, we can also directly use drivermanagerdatasource through configuration.

DriverManagerDataSource ds = new DriverManagerDataSource ();          ds.setDriverClassName("com.mysql.jdbc.Driver");          ds.setUrl("jdbc:mysql://localhost:3309/sampledb");          ds.setUsername("root");          ds.setPassword("1234");          Connection actualCon = ds.getConnection(); 

Difference between C3PO and DBCP

DBCP does not automatically recycle idle connections. C3PO automatically recycles idle connections.

Summary
No matter what persistence technology is used, data sources must be defined. Spring comes with an implementation class package for two data sources. You can customize it on your own.

In actual deployment, we may directly use the data source provided by the application server itself. In this case, we can reference the data source in JNDI through jndiobjectfactorybean or Jee namespace.