jdbc--Database Connection Pool

1, when using the development of database-based web programs, the traditional mode is now the main program in the Resume database connection, when the SQL operation is completed after the database connection is disconnected. There are some problems with this pattern:

--The normal JDBC database connection is obtained using DriverManager, and each time a connection is made to the database, the connection is loaded into memory and the user name and password are verified. When a database connection is required, a request is made to the database and then disconnected after execution is completed. Such a way would consume a lot of resources and time. The database's connection resources are not being reused very well. When the number of online users is very large, frequent connection to the database will occupy a lot of system resources, prone to crash;

--For each database connection, you need to disconnect after you have finished using it. Otherwise, if the program is abnormal and does not end properly, it will cause memory leaks in the database system and will eventually cause the database to be restarted.

--This development does not control the number of connection objects created, the system resources will be allocated without scruple, if too many connections, may also lead to memory leaks, the server crashes.

2, in order to solve the above database connection problem, can use database connection pool technology. The basic idea of database connection pool is to set up a "buffer pool" for the database, pre-put a certain number of connections in the buffer pool, when the resume database connection is needed, just take one from the buffer pool and return it after use.

3. The database connection pool is responsible for allocating, managing, and releasing database connections, which allows applications to reuse an existing database connection instead of re-establishing one.

4, the advantages of database connection pool:

--1) resource reuse;

--2) faster system reaction speed;

--3) New means of resource allocation;

--4) Unified connection management to avoid database connection leaks.

5. In JDBC, the database connection pool is represented by Javax.sql.DataSource, DataSource is an interface that is typically implemented by the server, and some open source organizations provide implementations (such as DBCP data 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, often referred to datasource as a connection pool.

6, the use of DBCP data connection pool:

--related packages: 1) commons-dbcp-1.4.jar;2) Commons-pool-1.5.5.jar;

--Create a DBCP datasource data source directly

 Public voidTestdatasource ()throwsexception{//Creating an DBCP data source instanceBasicdatasource DataSource =NewBasicdatasource (); //specify required properties for the data source instanceDatasource.setusername ("Scott"); Datasource.setpassword ("ORCL"); Datasource.seturl ("Jdbc:oracle:thin: @localhost: 1521:orcl"); Datasource.setdriverclassname ("Oracle.jdbc.driver.OracleDriver"); //to set additional properties for a data source//1. Specify the number of initialized connections in the database connection poolDatasource.setinitialsize (10); //2. Specify the maximum number of connections: the number of connections that can be requested simultaneously to the database at the same timeDatasource.setmaxactive (50); //3, specify minimum number of connections: the number of minimum idle connections saved in the database connection poolDatasource.setminidle (5); //4. The maximum time (in ms) to wait for the database connection pool to allocate connections, which throws an exception if the time is exceededDatasource.setmaxwait (5000); //getting a connection from a DataSource data sourceConnection conn =datasource.getconnection (); SYSTEM.OUT.PRINTLN (conn);}

--Create a DataSource data source by using the Datasourcefactory class:

 Public voidTestdatasourcefactory ()throwsexception{//The property information for the DataSource data source is first placed in the configuration file, where the key-value pair of the configuration file must be a property in DataSourceProperties Properties =NewProperties (); InputStream instream=NewFileInputStream ("Dbcp.properties"); //load the DBCP properties fileproperties.load (instream); //creating datasource data sources with DatasourcefactoryDataSource DataSource =Basicdatasourcefactory.createdatasource (properties); Basicdatasource Basicdatasource=(Basicdatasource) DataSource; System.out.println (Basicdatasource.getmaxwait ());}

#配置信息
Username=Scottpassword=orcl1url=jdbc:oracle:thin: @localhost: 1521: Orcldriverclassname =oracle.jdbc.driver.OracleDriverinitialSize=10maxactive=50minidle=5  Maxwait=5000

The second method is recommended in the above two methods, because the second way to create the data source is more flexible, you can directly modify the configuration file to set the corresponding properties of the data source, and the second method of creating the data source is transparent, we do not need to know how it was created.

7, the use of C3P0 database connection pool:

--The package involved: C3p0-0.9.1.2.jar

--Code implementation one:

 Public void throws Exception {    new  combopooleddatasource ();    " Oracle.jdbc.driver.OracleDriver " );    " Jdbc:oracle:thin: @localhost: 1521:orcl " );     Cpds.setuser ("Scott");    Cpds.setpassword ("ORCL");        System.out.println (Cpds.getconnection ());}

--code implementation two (through the configuration file implementation):

 Public void throws sqlexception{    new combopooleddatasource ("helloc3p0");    System.out.println (Datasource.getconnection ());     = (Combopooleddatasource) DataSource;    System.out.println (Cpds.getacquireincrement ());}

--Configuration file C3p0-config.xml:

<C3p0-config>    <Named-configname= "Helloc3p0">        <!--Basic properties of the data source -        < Propertyname= "User">Scott</ Property>        < Propertyname= "Password">Orcl</ Property>        < Propertyname= "Driverclass">Oracle.jdbc.driver.OracleDriver</ Property>        < Propertyname= "Jdbcurl">Jdbc:oracle:thin: @localhost: 1521:ORCL</ Property>                <!--request a specified number of connections to the database server at once if the number of connections in the database is insufficient -        < Propertyname= "Acquireincrement">3</ Property>        <!--number of connections when initializing a database connection pool -        < Propertyname= "Initialpoolsize">5</ Property>        <!--minimum number of database connections in the database connection pool -        < Propertyname= "Minpoolsize">5</ Property>        <!--Maximum number of database connections in a database connection pool -        < Propertyname= "Maxpoolsize">10</ Property>                 <!--C3P0 The maximum number of statement that a database connection pool can maintain -        < Propertyname= "Maxstatements">20</ Property>        <!--The number of statement objects that can be used at the same time for each connection -        < Propertyname= "Maxstatementsperconnection">5</ Property>    </Named-config></C3p0-config>

jdbc--Database Connection Pool