I. Overview

Usually, when operating a database, users want to request a connection from the database, and it usually takes a relatively large amount of resources and a long time to create a connection. If a website has millions of visits per day, the database server needs to create millions of connections. This is extremely important for database resources. The core idea of connection pool technology (from Baidu): connection reuse, through the establishment of a database connection pool and a set of connection use, allocation, management strategies, so that the connection in the connection pool can be efficient. Secure reuse avoids the overhead of frequent establishment and closure of database connections. Open source DBCP and CP30 connection pool components are the focus of this paper.

Second, DBCP Connection Pool Components

1)SUN company agrees that if it is connection pool technology, then it must implement javax.sql.DataSource interface!

2)DBCP is an open source connection pool technology implemented by Apache. Using DBCP data source, applications need to introduce two jar files, one is commons-dbcp-1.4.jar, the other is commons-pool-1.5.6.jar. The following number is version number. You can download the corresponding components from the official website.

3)Tomcat's connection pool is realized through DBCP connection pool. The database connection pool can be integrated with the application server or used independently of the application.

4)DBCP core class: BasicDataSource.

5) Code implementation

a. Hard coding

// 1. Hard-coded connection pool
	@Test
	public void testDbcp() throws Exception {
		private Connection conn = null;
		// DBCP Connection Pool Core Class
		BasicDataSource dataSource = new BasicDataSource();
		// Connection pool parameter configuration: Connection string drives user name password to initialize the maximum number of connections
		dataSource.setUrl("jdbc:mysql://localhost:3306/day15");
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUsername("root");
		dataSource.setPassword("abc");
		dataSource.setInitialSize(3);//Initialize connection pool size with three connections
		dataSource.setMaxActive(6);  //Maximum connection pool size
		dataSource.setMaxIdle(3000);// Maximum idle time 3 seconds

		// Get Connections
		conn = dataSource.getConnection();

		//conn.prepareStatement("delete from dept where id=15").executeUpdate();
		
		for(int i=0;i<23;i++){
			String value ="Lucy"+i;
			String sql="insert into dept(deptName) values(?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, value);
			pstmt.executeUpdate();
		}
		// close resource
		conn.close();
	}

Configuration parameters are set through DBCP core classes to obtain database connections.

b. Connection pool is realized by configuration file, which is scalable and easy to maintain.

/**
	 * 2,Configuration Mode to Realize Connection Pool
	 * Recommended use for easy maintenance
	 * The key in the configuration file should be the same as the attribute in the Basic Data Source
	 * @throws Exception
	 */
	@Test
	public void testProp() throws Exception {
                 private Connection conn = null;

// Load Properties configuration file Properties prop = new Properties();// Get input stream InputStream in = App_DBCP.class.getResourceAsStream("db.properties"); // Load property configuration file prop.load(in); // Create data source DataSource Source = BasicDataSourceFactory directly through configuration parameters. Urce (prop);// Get the connection conn = dataSource.getConnection();conn.prepareStatement("delete from dept where id=14").executeUpdate(); //close the resource conn.close();}

The db. properties file is as follows (under the same package as the Java file in which the above code resides):

url=jdbc:mysql://localhost:3306/day15
driverClassName=com.mysql.jdbc.Driver
username=root
password=abc
initialSize=3
maxActive=6
maxIdle=3000

3. C3P0 Connection Pool Component

1)C3P0 connection pool is the most commonly used connection pool technology! The Spring framework supports C3P0 connection pool technology by default.

2)C3P0 Core Class: Combopooled DataSource

3) Introduce the corresponding jar package: c3p0-0.9.1.2.jar, download the corresponding components and import the jar package.

4) Code implementation

a. Hard coding

/**
	 * 1,Hard Coding: Managing Connections Using c3p0 Connection Pool
	 * 
	 * @throws Exception
	 */
	@Test
	public void testC3p0() throws Exception {
		// Creating Connection Pool Core Tool Classes
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		// Setting parameters: url driver user name password initial connection number maximum connection number idle time
		dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/day15");
		dataSource.setDriverClass("com.mysql.jdbc.Driver");
		dataSource.setUser("root");
		dataSource.setPassword("abc");
		dataSource.setInitialPoolSize(3);
		dataSource.setMaxPoolSize(6);
		dataSource.setMaxIdleTime(2000);// Two seconds

		// Get Connections
		Connection conn = dataSource.getConnection();
                //Execute sql statements
		conn.prepareStatement("delete from dept where id=13").executeUpdate();

		// close resource
		conn.close();
	}

Is it very similar to DBCP connection pool? Every mistake, it's very similar to ___________.
b. How to configure parameters (which is a little different)

/**
	 * 2,XML Configuration: Connection pool management using c3p0
	 * 
	 * @throws Exception
	 */
	@Test
	public void testXml() throws Exception {
		// Creating Connection Pool Core Tool Classes
		// Automatically load the configuration file of c3p0 under src [c3p0-config.xml], which can be copied and imitated from the source code
		 ComboPooledDataSource dataSource = new ComboPooledDataSource();

		// Input parameters, using named configuration: load the configuration file in oracle_config in c3p0-config.xml, and the default configuration information is <default-config> without passing parameters.
//		ComboPooledDataSource dataSource = new ComboPooledDataSource(
//				"oracle_config");

		// Get Connections
		Connection conn = dataSource.getConnection();

		conn.prepareStatement("delete from dept where id=12").executeUpdate();

		// close resource
		conn.close();

	}

c3p0-config.xml:

<c3p0-config>
  <default-config>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/day15</property> 
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="user">root</property>
    <property name="password">abc</property>
    <property name="initialPoolSize">3</property>
    <property name="maxPoolSize">6</property>
    <property name="maxIdleTime">2000</property> 

    <!--
    <user-overrides user="swaldman">
      <property name="debugUnreturnedConnectionStackTraces">true</property>
    </user-overrides>
    -->

  </default-config>

<!--
  <named-config name="oracle_config">
    <property name="maxStatements">200</property>
    <property name="jdbcUrl">jdbc:test</property>
    <user-overrides user="poop">
      <property name="maxStatements">300</property>
    </user-overrides>
   </named-config>
-->

</c3p0-config>

This configuration file can be written with reference to the configuration file in the source code of the c3p0 component.

IV. Summary

The next blog is to write a JdbcUtils tool class through C3P0 and DbUtils. Come on!