Spring c3p0 configuration parameters

Spring c3p0 configuration parameters

Applicationcontext. xml file:

Java code

1. <? XML version = "1.0" encoding = "UTF-8"?>
2. <Beans xmlns = "http://www.springframework.org/schema/beans"
3. Xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance"
4. Xmlns: Jee = "http://www.springframework.org/schema/jee"
5. Xsi: schemalocation = "http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
6. Http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.0.xsd ">
7. <Bean id = "datasource"
8. Class = "com. mchange. v2.c3p0. combopooleddatasource"
9. Destroy-method = "close">
10. <Property name = "driverclass">
11. <Value> com. MySQL. JDBC. Driver </value>
12. </Property>
13. <Property name = "jdbcurl">
14. <Value> JDBC: mysql: // 192.168.3.110: 3306/dbname? Useunicode = true & amp; characterencoding = GBK </value>
15. </Property>
16. <Property name = "user">
17. <Value> root </value>
18. </Property>
19. <Property name = "password">
20. <Value> root </value>
21. </Property>
23. <! -- The minimum number of connections retained in the connection pool. -->
24. <Property name = "minpoolsize">
25. <Value> 5 </value>
26. </Property>
28. <! -- The maximum number of connections retained in the connection pool. Default: 15 -->
29. <Property name = "maxpoolsize">
30. <Value> 30 </value>
31. </Property>
33. <! -- The number of connections obtained during initialization. The value must be between minpoolsize and maxpoolsize. Default: 3 -->
34. <Property name = "initialpoolsize">
35. <Value> 10 </value>
36. </Property>
38. <! -- Maximum idle time. connections are dropped if they are not used within 60 seconds. If it is 0, it will never be discarded. Default: 0 -->
39. <Property name = "maxidletime">
40. <Value> 60 </value>
41. </Property>
43. <! -- The number of connections that c3p0 obtains at the same time when connections in the connection pool are exhausted. Default: 3 -->
44. <Property name = "acquireincrement">
45. <Value> 5 </value>
46. </Property>
48. <! -- JDBC standard parameter, used to control the number of preparedstatements loaded in the data source. However, due to the pre-Cache statements
49. Belongs to a single connection pool rather than the entire connection pool. Therefore, you need to consider many factors when setting this parameter.
50. If both maxstatements and maxstatementsperconnection are 0, the cache is disabled. Default: 0 -->
51. <Property name = "maxstatements">
52. <Value> 0 </value>
53. </Property>
55. <! -- Check all idle connections in the connection pool every 60 seconds. Default: 0 -->
56. <Property name = "idleconnectiontestperiod">
57. <Value> 60 </value>
58. </Property>
60. <! -- Defines the number of repeated attempts after a new connection fails to be obtained from the database. Default: 30 -->
61. <Property name = "acquireretryattempts">
62. <Value> 30 </value>
63. </Property>
65. <! -- Failed to obtain the connection will cause all threads waiting for the connection pool to obtain the connection to throw an exception. However, the data source is still valid.
66. Retained, and continue to get the connection when getconnection () is called the next time. If it is set to true
67. After the connection fails, the data source is declared disconnected and permanently closed. Default: false -->
68. <Property name = "breakafteracquirefailure">
69. <Value> true </value>
70. </Property>
72. <! -- Because of high performance consumption, use it only when needed. If set to true
73. Will verify its validity. We recommend that you use idleconnectiontestperiod or automatictesttable
74. To improve the connection test performance. Default: false -->
75. <Property name = "testconnectiononcheckout">
76. <Value> false </value>
77. </Property>
78. </Bean>
79. <! -- Hibernate sessionfactory -->
80. <Bean id = "sessionfactory"
81. Class = "org. springframework. Orm. hibernate3.localsessionfactorybean">
82. <Property name = "datasource">
83. <Ref local = "datasource"/>
84. </Property>
85. <Property name = "mappingresources">
86. <List>
87. <Value> COM/XH/hibernate/Vo/user. HBM. xml </value>
88. </List>
89. </Property>
90. <Property name = "hibernateproperties">
91. <Props>
92. <Prop key = "hibernate. dialect"> org. hibernate. dialect. mysqldialect </prop>
93. <Prop key = "hibernate. show_ SQL"> true </prop>
94. <Prop key = "hibernate. generate_statistics"> true </prop>
95. <Prop key = "hibernate. Connection. release_mode"> auto </prop>
96. <Prop key = "hibernate. autoreconnect"> true </prop>
97. </Props>
98. </Property>
99. </Bean>
100. </Beans>
     

Configure the spring data source c3p0 and DBCP

No matter what persistence technology is used, you must access the database through data connections. In spring, data connections are obtained through data sources. In previous applications, data source 1
Is provided by the Web application server. In spring, you can not only obtain the data source of the application server through JNDI, but also directly configure the data source in the spring container. In addition, you also
You can create a data source using code to perform unit tests without dependency.
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
/Lib/Jakarta-commons/commons-dbcp.jar, DBCP is a dependency on Jakarta
The database connection pool of the commons-pool Object pool mechanism. Therefore, the/lib/Jakarta-
Commons/commons-pool.jar. The following is the configuration snippet for configuring the MySQL data source using DBCP: XML Code
<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 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 together with hibernate in the lib directory, and implements the description of jdbc3 and jdbc2 extension specifications.
Connection and statement
Pool. C3p0 class package is located in/lib/c3p0/c3p0-0.9.0.4.jar. The following code configures an ORACLE data source using c3p0: XML
<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: 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
You cannot perform any operations 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
Continue to get the connection when getconnection () is used. If it is set to true, the data source will be declared disconnected and permanently closed after the connection fails to be obtained. Default Value:
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. Default Value:
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 is
Instead of the entire connection pool. Therefore, you need to consider multiple factors when setting this parameter. If maxstatements and
If maxstatementsperconnection is 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
Verify the validity. 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. Application Server Data Source
Open the caller using JNDI. Spring provides the jndiobjectfactorybean class that references the JNDI resources. The following is a simple configuration: XML Code
<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 following is the configuration for referencing the JNDI data source using the Jee namespace: XML Code
<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.xsd
Http://www.springframework.org/schema/jee
Http://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 itself 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,
It does not provide a pooled Connection Mechanism. Every time getconnection () is called to obtain a new connection, it simply creates a new connection. Therefore, this data source class is suitable for unit testing.
Or a simple independent application, 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
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 ();
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 Application Server
The data source provided by the server itself. In this case, the data source in JNDI can be referenced through jndiobjectfactorybean or Jee namespace. Difference between DBCP and C3PO configuration: C3PO: DBCP: XML Code
<Bean id = "datasource" class = "com. mchange. v2.c3p0. combopooleddatasource" Destroy-method = "close">
<Property name = "driverclass">
<Value> oracle. JDBC. Driver. oracledriver </value>
</Property>
<Property name = "jdbcurl">
<Value> JDBC: oracle: thin: @ 10.10.10.6: 1521: databasename </value>
</Property>
<Property name = "user">
<Value> testadmin </value>
</Property>
<Property name = "password">
<Value> 123456 </value>
</Property>
</Bean>
XML Code
<Bean id = "datasource" class = "org. Apache. commons. DBCP. basicdatasource" Destroy-method = "close">
<Property name = "driverclassname">
<Value> oracle. JDBC. Driver. oracledriver </value>
</Property>
<Property name = "url">
<Value> JDBC: oracle: thin: @ 10.10.10.6: 1521: databasename </value>
</Property>
<Property name = "username">
<Value> testadmin </value>
</Property>
<Property name = "password">
<Value> 123456 </value>
</Property>
</Bean>