Database Connection Pool

Database Connection Pool

Role: Use pools to manage the lifecycle of connections, conserve resources, and improve performance.
Connection pooling interfaces provided by Java:Javax.sql.DataSource, the connection pool class of the connection pool vendor needs to implement this interface.

Pool parameters (all pool parameters have default values):

Initial Size: 10 x

Minimum number of idle connections: 3

Increment: Smallest unit created at a time (5)

Maximum number of idle connections: 12

Maximum number of connections: 20

Maximum wait time: 1000 milliseconds

Four connection parameters

Connection pooling also uses four connection parameters to complete the creation of connection objects!

The implemented interface

Connection pooling must be implemented: Javax.sql.DataSource Interface!

The connection object returned by the connection pool, its close () method is different! The close () call it is not closed, but the connection is returned to the pool!

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------

DBCP

Jar:commons-pool.jar, Commons-dbcp.jar

basicdatasource ds = new Basicdatasource ();
ds.setusername ("root");
ds.setpassword ("123");
ds.seturl ("jdbc:mysql://localhost:3306/mydb1");
ds.setdriverclassname ("Com.mysql.jdbc.Driver");
   
ds.setmaxactive (20); Maximum number of connections
ds.setmaxidle (10);//MAX idle
ds.setinitialsize (10);//Initialize
ds.setminidle (2);//min. idle

connection con = ds.getconnection ();

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------

C3p0

Jar:c3p0-0.9.2-pre1.jar, C3p0-oracle-thin-extras-0.9.2-pre1.jar, mchange-commons-0.2. Jarcombopooleddatasource ds = new  combopooleddatasource ();d S.setjdbcurl ("jdbc:mysql:// LOCALHOST:3306/MYDB1 ");d S.setuser(" root ");d S.setpassword (" 123 ");d S.setdriverclass ("   Com.mysql.jdbc.Driver "); Ds.setacquireincrement (5);/* Increase five */ds.setinitialpoolsize each time;// Initialize the number of connections ds.setminpoolsize (2);// Minimum connection ds.setmaxpoolsize (+);// Maximum connection Connection Con = Ds.getconnection ();

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------

C3P0 configuration file

1. Initialize the connection pool with the default configuration

Configuration file Requirements:

File name: Must be called c3p0-config.xml

File location: Must be under SRC

< Default-config >  <name= "xxx">xxx</Property  ></defualt-config>

2. Initializing a connection pool with a named configuration

<name= "Orcale-config"><Name  = "xxx">xxx</Property></  Named-config>

    /*** configuration file's default configuration *@throwsSQLException*/@Test Public voidFun2 ()throwssqlexception{/*** When creating a connection pool object, this object will automatically load the configuration file! We don't have to specify*/Combopooleddatasource DataSource=NewCombopooleddatasource (); Connection Con=datasource.getconnection ();        System.out.println (con);    Con.close (); }        /*** Using named configuration information *@throwsSQLException*/@Test Public voidFUN3 ()throwssqlexception{/*** constructor parameter specifies the name of the named configuration element! * <named-config name= "Oracle-config" >*/Combopooleddatasource DataSource=NewCombopooleddatasource ("Oracle-config"); Connection Con=datasource.getconnection ();        System.out.println (con);    Con.close (); }}

==============================================================================================

Tomcat Configuring connection Pooling
Create an XML file in Server.xml, or under conf/catalina/localhost/

 <context>     <resource name= "myc3p0"     type  = "Com.mchange.v2.c3p0.ComboPooledDataSource"     factory  = "Org.apache.naming.factory.BeanFactory"     user  = "root"     password  =" 123 "    classdriver  = "Com.mysql.jdbc.Driver"        jdbcurl  = "JDBC:MYSQL://127.0.0.1/MYDB1"     maxpoolsize  = "   minpoolsize  =" 5 "   initialpoolsize  = "Ten"    acquireincrement  = "2"/></context>  

--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------

Get Tomcat Resources

Context cxt = new InitialContext (); DataSource ds = (DataSource) cxt.lookup ("java:/comp/env/myc3p0"); Connection con = ds.getconnection ();

--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------

Modify Jdbcutils

 Public classJdbcutils {Private StaticDataSource DataSource =NewCombopooleddatasource (); Public StaticDataSource Getdatasource () {returnDataSource;}  Public StaticConnection getconnection () {Try {   returndatasource.getconnection (); } Catch(Exception e) {Throw NewRuntimeException (e); } }}

==============================================================================================

Dbutils

Jar:commons-dbutils.jar
Core class: Queryrunner, Resultsethandler

Queryrunner Method:
* UPDATE (): DDL, DML
* Query (): DQL
* Batch (): Batch processing

-------------

increase, delete, change

 Public void throws  new= "INSERT into user values (?,?,?)"  "U1", "Zhangsan", "123");}

-------------

Check

DataSource ds =Jdbcutils.getdatasource (); Queryrunner qr=NewQueryrunner (DS); String SQL= "SELECT * FROM Tab_student";//Convert the result set to a beanStudent stu = qr.query (sql,NewBeanhandler<student> (Student.class));//Convert the result set to a list of beanslist<student> list = qr.query (sql,NewBeanlisthandler<student> (Student.class));//Convert the result set into a mapmap<string,object> map = qr.query (sql,NewMaphandler ());//Convert the result set into List<map>list<map<string,object>> list = qr.query (sql,NewMaplisthandler ());//Convert the result set to a list of columnslist<object> list = qr.query (sql,NewColumnlisthandler ("name")) ;//Convert the result to a single-row single-column valueNumber number = (number) qr.query (SQL,NewScalarhandler ());

Batch Processing

DataSource ds =new= "INSERT into tab_student values (?,?,?,?)"  New// indicates to insert 10 rows of records for (int i = 0; i < params.length; i++) { c10/>New object[]{"s_300" + I, "name" + I, + I, i%2==0? " Male ":" Female "

Database Connection Pool