JDBC Connection Pool

Main content:

• Introduction to Database connection pooling
• Required interfaces and methods
• Implementation steps

A. Database connection pool:

1. Pre-Request multiple available database connections to the database, save to the collection object , each time the application removes the available database connections from the collection object, performs database operations, and puts the connection back into the collection object after the operation is complete. Enables the reuse of database connections.

2. The establishment of database connection is the most time-consuming operation in the whole database operation, we only do one database operation to switch the database connection once, this is very affect the efficiency
3. Is it possible for us to get a database connection from one cache at a time, and then return it to that cache place, with no real closing connection?

Two. Write a connection pool to implement the Java.sql.DataSource interface. The DataSource interface defines the two overloaded getconnection methods:

1.Connection getconnection ()

2.Connection getconnection (string Username, string password)

Three. Steps to implement the DataSource interface and implement the connection pooling function:

1. Create a connection to the database in bulk in the DataSource constructor and add the created connection to the LinkedList object.

1. Implement the Getconnection method, and let the Getconnection method take a connection back to the user from LinkedList each time it is called.

2. When the user finishes using connection and calls the Connection.close () method, the collection object should ensure that it returns itself to LinkedList instead of returning the conn to the database. Collection The difficulty of programming here is to ensure that you return yourself to LinkedList.

Database Connection Pool instance:

1  Public classSimpledatasource {2     Private StaticLinkedlist<connection> connpool=NULL;3      4      Public Static voidMain (string[] args) {5         NewSimpledatasource ();6         7     }8     9     PublicSimpledatasource () {Ten        Try{ OneClass.forName ("Com.mysql.jdbc.Driver"); AConnpool=NewLinkedlist<connection>(); -          for(inti=0;i<10;i++){ -Connection conn=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/test", "H3", "111111"); the Connpool.add (conn); - SYSTEM.OUT.PRINTLN (conn); -         } -}Catch(Exception e) { + e.printstacktrace (); -     }  +  } A      PublicConnection Getconn () {//returns a Connection object from LinkedList at         returnconnpool.removelast (); -     } -      -      Public voidCloseconn (Connection conn) {//"Close" The database connection, not the true meaning of the shutdown - Connpool.add (conn); -      } in      -      Public voidprintpoolsize () { toSYSTEM.OUT.PRINTLN ("Connection Pool size:" +connpool.size ()); +     } -      the      Public voidReleasepool ()throwsexception{//Close each link; Shut down the database connection, real close *Iterator it=connpool.iterator (); $Connection conn=NULL;Panax Notoginseng          while(It.hasnext ()) {} -conn=(Connection) It.next (); the conn.close (); +     } A}

To test the database connection pool:

1  Public classTestpool {2     Public Static voidMain (string[] args) {3       Try{4Simpledatasource sds=NewSimpledatasource ();5       //Sds.releasepool ();//To close a database connection6        7Connection conn=sds.getconn ();8SYSTEM.OUT.PRINTLN ("Connection used ....." +conn);9 sds.printpoolsize ();Ten         One QUERYEMP (conn); A Sds.closeconn (conn); - sds.printpoolsize (); -System.out.println (""); the}Catch(Exception e) { - e.printstacktrace (); -         }  -    } +      -     Public Static voidqueryemp (Connection conn) { +       Try{ AString sql= "SELECT * FROM Employees"; atStatement stmt=conn.createstatement (); -ResultSet rs=stmt.executequery (SQL); -            while(Rs.next ()) { -System.out.print ("Work No.:" +rs.getint (1)); -System.out.println ("Work No.:" +rs.getstring ("last_name"))); -            } in rs.close (); -}Catch(Exception e) { to e.printstacktrace (); +       }  -    } the}

DBCP Database Connection pool:

Basicdatasource BDS = new Basicdatasource ();
Bds.setdriverclassname ("Org.gjt.mm.mysql.Driver");
Bds.seturl ("Jdbc:mysql:///test");
Bds.setusername ("root");
Bds.setpassword ("123456");
Bds.setinitialsize (1);
Bds.setmaxactive (1);
Bds.setminidle (1);
Connection con = bds.getconnection ();
System.out.println (con);
Con.close ();
con = bds.getconnection ();
System.out.println (con);
Con.close ();

C3P0 Database Connection pool:

Combopooleddatasource CDs = new Combopooleddatasource ();
Cds.setdatasourcename ("Org.gjt.mm.mysql.Driver");
Cds.setjdbcurl ("Jdbc:mysql:///test");
Cds.setuser ("root");
Cds.setpassword ("123456");
Cds.setinitialpoolsize (10);
Cds.setmaxpoolsize (50);
Cds.setminpoolsize (2);
Cds.setmaxstatements (200);
Connection con = cds.getconnection ();
System.out.println (con);
Con.close ();

JDBC Connection pooling