JDBC: Database Connection Pool

The tools you need

Commons-pool2-2.3.jar: Dependency libraries for connection pooling implementations

Commons-dbcp2-2.0.1.jar: Implementation of connection pooling

C3p0-0.9.2.1.jar

If you use C3p0 to appear Method ' initializationerror ' not found,opening the test class, add

Mchange-commons-java-0.2.3.4.jar Bag

The JDBC driver used by Ojdbc6.jar

I. The need for a JDBC database connection pool

When using the development of a database-based Web program, the traditional pattern is basically the following steps:
-Establish a database connection in the main program (such as servlet, beans).
-Perform SQL operations
-Disconnect database connections.
This pattern of development, the existence of the problem:
The normal JDBC database connection is obtained using DriverManager, which loads the Connection into memory each time a connection is made to the database, and then validates the user name and password (which takes 0.05s~1s time). When a database connection is required, a request is made to the database and then disconnected after execution is complete. Such a way would consume a lot of resources and time. Database connection resources are not well reused. If there are hundreds of or even thousands of people online, frequent database connection operations will consume a lot of system resources, which can even cause the server to crash.
For each database connection, you will have to disconnect after you have finished using it. Otherwise, if the program fails to close, it will cause a memory leak in the database system and will eventually cause the database to be restarted.
This development does not control the number of connection objects being created, and system resources are allocated without consideration, such as excessive connections, which can also lead to memory leaks and server crashes.

database connection pooling (connection pool)

    in order to solve the problem of database connection in traditional development, database connection pooling technology can be adopted.
    database connection pool basic idea
    database connection pool allows applications to reuse an existing database connection instead of re-establishing one.
    database connection pooling creates a certain number of database connections to the connection pool at initialization time, the number of these database connections is from the The minimum number of database connections to set. Regardless of whether these database connections are being used, the connection pool will always be guaranteed to have at least so many connections. The maximum number of database connections for a connection pool

How database connection pooling works

Two open-source database connection pools

The JDBC database connection pool uses Javax.sql.DataSource to represent that DataSource is just an interface that is typically implemented by servers (Weblogic, WebSphere, Tomcat), and some open source organizations provide implementations:
DBCP Database Connection Pool
C3P0 Database Connection Pool
DataSource is often referred to as a data source, which contains two parts of connection pooling and connection pooling management, and it is customary to often call DataSource a connection pool

DBCP Data source

@Test//okpublic void Testdbcpwithbasicdatasource () throws Exception {Basicdatasource DataSource = null;// Create a data source instance DataSource = new Basicdatasource ();//Set basic properties Datasource.setusername ("Scott");d Atasource.setpassword (" Qiaolezi ");d Atasource.seturl (" Jdbc:oracle:thin: @localhost: 1521:orcl ");d atasource.setdriverclassname (" Oracle.jdbc.driver.OracleDriver ");//other properties//Set the initial connection number of the database Datasource.setinitialsize (5);// Maximum number of idle connections Datasource.setmaxidle (8);//maximum number of connections datasource.setmaxtotal (20);//Set the database at least 2 idle connections, The minimum number of idle connections saved in the database connection pool Datasource.setminidle (2);/* The maximum number of wait milliseconds to get a connection (if blockwhenexhausted is set to blocked), and if the timeout is thrown, the exception is less than 0: Block indeterminate time, Default-1 */datasource.setmaxwaitmillis (1000*6),//whether to enable LIFO, default Truedatasource.setlifo (True),//check validity when acquiring a connection, Default Falsedatasource.settestonborrow (FALSE);//check validity at idle, default Falsedatasource.settestwhileidle (FALSE);//Minimum idle time to evict a connection Default 1800000 milliseconds (30 minutes) Datasource.setminevictableidletimemillis (1800000); */* object is idle for how long after eviction, when idle time > This value and idle connection > maximum idle number of direct eviction, No longer judged by Minevictableidletimemillis (default eviction policy) */datasource.setsoftminevictableIdletimemillis (1800000);//eviction scan interval (milliseconds) if negative, the eviction thread is not run, default -1datasource.settimebetweenevictionrunsmillis (-1); Connection conn = Datasource.getconnection (); SYSTEM.OUT.PRINTLN (conn);}

PS: Data sources and database connections are different, there is no need to create multiple data sources, it is the factory that produces database connections, so the entire application needs only one data source.
When the database access is finished, the program closes the database connection as before: Conn.close (); But the code above does not close the physical connection to the database, it simply frees the database connection and returns it to the database connection pool.

/** * 1. Load DBCP's properties configuration file: Key (attributes from Basicdatasource) * 2. Call Basicdatasourcefactory's CreateDataSource (properties) method to get DataSource instance * 3. Get database connection from DataSource instance * @throws Exception */@Test//okpublic void Testdbcpwithdatasourcefactory () throws exception{ Properties Properties = new properties (); InputStream instream = JDBC_Tools.class.getClassLoader (). getResourceAsStream ("Dbcp.properties");p roperties.load (instream);D atasource DataSource = Basicdatasourcefactory.createdatasource ( properties); System.out.println (Datasource.getconnection ());//test Basicdatasource Basicdatasource = (basicdatasource) DataSource; System.out.println (Basicdatasource.getmaxwaitmillis ());}

Dbcp.properties

Username=scottpassword=qiaolezidriverclassname=oracle.jdbc.driver.oracledriverurl=jdbc:oracle:thin: @localhost : 1521:orclinitialsize=5minidle=5maxwaitmillis=5000

Note key to conform to JavaBean

C3P0 Data source

@Testpublic void Testc3p0 () throws Exception {Combopooleddatasource CPDs = new Combopooleddatasource (); Cpds.setdriverclass ("Oracle.jdbc.driver.OracleDriver");          Cpds.setjdbcurl ("Jdbc:oracle:thin: @localhost: 1521:orcl"); Cpds.setuser ("Scott");                                  Cpds.setpassword ("Qiaolezi"); System.out.println (Cpds.getconnection ());}

Using Combopooleddatasource

/** * 1. Create C3p0-config.xml, refer to C3p0-0.9.2.1\doc index.html Help document Appendix A:configuration Properties * 2. Create DataSource DataS Ource = Combopooleddatasource ("helloc3p0"); Instance * 3. Get database connection from DataSource instance * @throws Exception */@Test//okpublic void Testc3p0withconfig () throws Exception{datasource DataSource = new Combopooleddatasource ("helloc3p0"); XML <named-config name= "Helloc3p0" > System.out.println (DataSource); System.out.println (Datasource.getconnection ()); Combopooleddatasource com = (combopooleddatasource) DataSource; System.out.println (Com.getmaxstatements ());}

C3p0-config. Xml

<c3p0-config> <named-config name= "helloc3p0" > <!--Specify the basic properties of the connection data source-<property name= "user" >sco tt</property> <property name= "Passwword" >qiaolezi</property> <property name= "Driverclass" > oracle.jdbc.driver.oracledriver</property> <property name= "Jdbcurl" >jdbc:oracle:thin: @localhost: 1521 :orcl</property> <!--If the number of connections in the database is low, how many connections to the database server are requested at a time-<property name= "Acquireincrement" >50</p Roperty> <!--number of connections when initializing a database connection pool-<property name= "Initialpoolsize" >100</property> &L t;! --The minimum number of database connections in the database connection pool-<property name= "Minpoolsize" >50</property> <!--the maximum number of database connections in the database connection pool--&G    T <property name= "Maxpoolsize" >1000</property> <!--the number of statement the database connection pool can maintain--<property name= " Maxstatements ">0</property> <!--the number of Statement objects that can be used at the same time for each connection--<property name=" maxstatement Sperconnection ">5</propertY> </named-config></c3p0-config> 

Finally, you can perform overloads of the Getconnection () method in the Jdbc_tools tool class, and later Connection.close (). Instead of closing the connection, return the used database connection to the database connection pool

JDBC: Database Connection Pool