Spring -- c3p0 configuration details

Spring -- c3p0 configuration details

Today, let's talk about the c3p0 configuration in Spring in detail. Well, we will go directly to the topic without delay. Please refer to the specific configuration file below:

 

  
       
            
                 
    
     
Com. mysql. jdbc. Driver
              
             
                 
    
     
Jdbc: mysql :/// 192.168.3.110: 3306/DBName? UseUnicode = true & characterEncoding = GBK
              
             
                 
    
     
Root
              
             
                 
    
     
Root
              
         
             
                 
    
     
5
              
         
             
                 
    
     
30
              
        
             
                 
    
     
10
              
         
             
                 
    
     
60
              
         
             
                 
    
     
5
              
         
             
                 
    
     
0
              
         
             
                 
    
     
60
              
         
             
                 
    
     
30
              
         
             
                 
    
     
True
              
         
             
                 
    
     
False
              
         
        
        
            
                 
              
             
                 
                      
     
      
Com/xh/hibernate/vo/User. hbm. xml
                   
              
             
                 
                      
     
      
Org. hibernate. dialect. MySQLDialect
                       
     
      
True
                       
     
      
True
                       
     
      
Auto
                       
     
      
True
                   
              
         
    
   

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 the past, data sources were generally provided by Web application servers. 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 can create a data source through code, in order to conduct a unit test 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 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:

Xml code:

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/lib/c3p0/c3p0-0.9.0.4.jar. The following describes how to configure an Oracle data source using C3P0:

 

Xml Code

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:

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:

Xml Code

Use jndiName to specify the name of the referenced JNDI data source.

Spring2.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

 

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

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 data source provided by the application server. 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

 

 

        
             
   
    oracle.jdbc.driver.OracleDriver
          
         
                        
   
    jdbc:oracle:thin:@10.10.10.6:1521:DataBaseName
           
         
             
   
    testAdmin
          
         
             
   
    123456
          
     
  

Xml Code

 

 

        
             
   
    oracle.jdbc.driver.OracleDriver
          
         
                        
   
    jdbc:oracle:thin:@10.10.10.6:1521:DataBaseName
           
         
             
   
    testAdmin
          
         
             
   
    123456