Configure datasource using spring

1. Use org. springframework. JDBC. datasource. drivermanagerdatasource
Note: When drivermanagerdatasource establishes a connection, a new connection is created as long as there is a connection, and there is no function of the connection pool.
<Bean id = "datasource" class = "org. springframework. JDBC. datasource. drivermanagerdatasource">
<Property name = "driverclassname"> <value >$ {JDBC. driverclassname} </value> </property>
<Property name = "url"> <value >$ {JDBC. url} </value> </property>

<Property name = "username"> <value >$ {JDBC. Username} </value> </property>
<Property name = "password"> <value >$ {JDBC. Password} </value> </property>

</Bean>
2. Use org. Apache. commons. DBCP. basicdatasource

Note: This is a recommended data source configuration method. It actually uses the connection pool technology.
<Bean id = "datasource" class = "org. Apache. commons. DBCP. basicdatasource">

<Property name = "driverclassname">
<Value> oracle. JDBC. Driver. oracledriver </value>

</Property>
<Property name = "url">
<Value> JDBC: oracle: thin: @ localhost: 1521: orcl </value>

</Property>
<Property name = "username">
<Value> test </value>
</Property>
<Property name = "password">
<Value> test </value>
</Property>
<Property name = "maxactive">
<Value> 255 </value>
</Property>
<Property name = "maxidle">
<Value> 2 </value>
</Property>
<Property name = "maxwait">
<Value> 120000 </value>
</Property>
</Bean>
3. Use org. springframework. JNDI. jndiobjectfactorybean
Note: jndiobjectfactorybean can obtain datasource through JNDI
<Bean id = "datasource" class = "org. springframework. JNDI. jndiobjectfactorybean">
<Property name = "jndiname"> <value> JAVA: COMP/ENV/jdbc/roseindiadb_local </value> </property>
</Bean>
Conclusion: The first of the three methods does not use the connection pool, so it is rarely used in the project. The third method requires configuring the data source in the Web server, which is not convenient for deployment, we recommend that you configure the data source in two ways.

Configure a data source
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 configuration data source in the spring configuration file.

DBCP Data Source
DBCP class package is located in <spring_home> </spring_home>/lib/Jakarta-commons/commons-dbcp.jar, DBCP is a database connection pool dependent on Jakarta commons-pool Object pool mechanism, therefore, the <spring_home> </spring_home>/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:
XML Code

1. <bean id = "datasource" class = "org. Apache. commons. DBCP. basicdatasource"
2. Destroy-method = "close">
3. <property name = "driverclassname" value = "com. MySQL. JDBC. Driver"/>
4. <property name = "url" value = "JDBC: mysql: // localhost: 3309/sampledb"/>
5. <property name = "username" value = "root"/>
6. <property name = "password" value = "1234"/>
7. </bean>

Basicdatasource provides the close () method to close the data source. Therefore, you must set the destroy-method = "close" attribute so that the data source can be normally closed when the spring container is closed. 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 <spring_home> </spring_home>/lib/c3p0/c3p0-0.9.0.4.jar. The following describes how to configure an ORACLE data source using c3p0:

XML Code

1. <bean id = "datasource" class = "com. mchange. v2.c3p0. combopooleddatasource"
2. Destroy-method = "close">
3. <property name = "driverclass" value = "oracle. JDBC. Driver. oracledriver"/>
4. <property name = "jdbcurl" value = "JDBC: oracle: thin: @ localhost: 1521: ora9i"/>
5. <property name = "user" value = "admin"/>
6. <property name = "password" value = "1234"/>
7. </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: Number of new connections created at one time in c3p0 when connections in the connection pool are used up;
Acquireretryattempts: defines the number of times that a new connection fails to be retrieved from the database. The default value is 30;
Acquireretrydelay: the interval between two connections, in milliseconds. The default value is 1000;
Autocommitonclose: by default, all uncommitted operations are rolled back when the connection is closed. 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, and the next call to getconnection () will continue to try to obtain the connection. 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. If it is set to 0, it throws sqlexception. 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: How many seconds to check idle connections in all connection pools. 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: Standard JDBC 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. These operations can effectively improve the performance. Multiple operations can be executed simultaneously through multiple threads. The default value is 3;
Preferredtestquery: defines the 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, 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:

1. <bean id = "propertyconfigurer"
2. Class = "org. springframework. Beans. Factory. config. propertyplaceholderconfigurer">
3. <property name = "location" value = "/WEB-INF/jdbc. properties"/>

4. </bean>
5. <bean id = "datasource" class = "org. Apache. commons. DBCP. basicdatasource"
6. Destroy-method = "close">
7. <property name = "driverclassname" value = "$ {JDBC. driverclassname}"/>
8. <property name = "url" value = "$ {JDBC. url}"/>
9. <property name = "username" value = "$ {JDBC. Username}"/>
10. <property name = "password" value = "$ {JDBC. Password}"/>
11. </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, <property name = "username" value = "$ {JDBC. the attribute configuration item of username} "> </property> has spaces before and after it is parsed, and the value of username is" 1234 ", which leads to a final error, therefore, be especially 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:

XML Code

1. <bean id = "datasource" class = "org. springframework. JNDI. jndiobjectfactorybean">
2. <property name = "jndiname" value = "Java: COMP/ENV/jdbc/bbt"/>

3. </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:

XML Code

1. <beans xmlns = http://www.springframework.org/schema/beans
2. xmlns: xsi = http://www.w3.org/2001/XMLSchema-instance
3. xmlns: Jee = http://www.springframework.org/schema/jee
4. xsi: schemalocation = "http://www.springframework.org/schema/beans

5. http://www.springframework.org/schema/beans/spring-beans-2.0.xsd

6. http://www.springframework.org/schema/jee
7. http://www.springframework.org/schema/jee/spring-jee-2.0.xsd ">

8. <Jee: JNDI-lookup id = "datasource" JNDI-name = "Java: COMP/ENV/jdbc/bbt"/>
9. </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.

Java code

1. drivermanagerdatasource DS = new drivermanagerdatasource ();

2. Ds. setdriverclassname ("com. MySQL. JDBC. Driver ");
3. Ds. seturl ("JDBC: mysql: // localhost: 3309/sampledb ");
4. Ds. setusername ("root ");
5. Ds. setpassword ("1234 ");
6. Connection actualcon = Ds. getconnection ();

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.

Difference between DBCP and C3PO Configuration:

C3PO: DBCP:

XML Code

1. <bean id = "datasource" class = "com. mchange. v2.c3p0. combopooleddatasource" Destroy-method = "close">
2. <property name = "driverclass">
3. <value> oracle. JDBC. Driver. oracledriver </value>
4. </property>
5. <property name = "jdbcurl">
6. <value> JDBC: oracle: thin: @ 10.10.6: 1521: databasename </value>

7. </property>
8. <property name = "user">
9. <value> testadmin </value>
10. </property>
11. <property name = "password">
12. <value> 123456 </value>
13. </property>
14. </bean>

XML Code

1. <bean id = "datasource" class = "org. Apache. commons. DBCP. basicdatasource" Destroy-method = "close">
2. <property name = "driverclassname">
3. <value> oracle. JDBC. Driver. oracledriver </value>
4. </property>
5. <property name = "url">
6. <value> JDBC: oracle: thin: @ 10.10.6: 1521: databasename </value>

7. </property>
8. <property name = "username">
9. <value> testadmin </value>
10. </property>
11. <property name = "password">
12. <value> 123456 </value>
13. </property>
14. </bean>