00316_DBCP Connection Pool

1. Connection Pooling Overview

(1) Use the pool to manage the connection so that the connection can be reused;

(2) There is a pool, so we do not have to create connection ourselves, but through the pool to obtain connection objects;

(3) When connection is finished, calling connection's close () method does not actually close the connection, but instead connection "returns" to the pool. The pool can then use the connection object again;

(4) Java provides a common interface for database connection pooling: Javax.sql.DataSource, each vendor needs to have its own connection pool implement this interface. So the application can easily switch the connection pool of different vendors;

(5) Common connection pool: DBCP, c3p0.

2. DBCP Connection Pool

(1) DBCP is also an open source connection pool, is one of Apache common members, in enterprise development is also more common, Tomcat built-in connection pool;

(2) Download and import Commons-dbcp-1.4.jar and Commons-pool-1.5.6.jar package, download link Commons-dbcp-1.4.jar and commons-pool-1.5.6.jar password: q81g;

(3) Writing tool classes

① a tool class that joins a database table, which is done in the form of a DBCP connection pool, a rule interface for a connection pool is provided in Java:

DataSource: It is a connection pool provided in Java as an alternative to the DriverManager tool. The implementation class of the DataSource interface is provided in the DBCP package, and we want to use the specific connection pool Basicdatasource class.

1 ImportJavax.sql.DataSource;2 3 ImportOrg.apache.commons.dbcp.BasicDataSource;4 5  Public classJdbcutils {6      Public Static FinalString DRIVER = "Com.mysql.jdbc.Driver";7      Public Static FinalString URL = "Jdbc:mysql://localhost:3306/mybase";8      Public Static FinalString USERNAME = "root";9      Public Static FinalString PASSWORD = "root";Ten     /* One * Create connection Pool Basicdatasource A      */ -      Public StaticBasicdatasource DataSource =NewBasicdatasource (); -     //Static code block the     Static { -         //basic configuration of Connection pool objects -Datasource.setdriverclassname (DRIVER);//This is the driver of the database to connect to -Datasource.seturl (URL);//Specify the database address to connect to +Datasource.setusername (USERNAME);//Specify the user name to connect to the data -Datasource.setpassword (PASSWORD);//Specify the password to connect to the data +     } A  at     /* - * Return Connection pool object -      */ -      Public StaticDataSource Getdatasource () { -         returnDataSource; -     } in}

(4) Increase

Initial data:

　　

1 Importjava.sql.SQLException;2 3 ImportOrg.apache.commons.dbutils.QueryRunner;5 Importorg.junit.Test;6 7 /*8 * Demo use Dbutils tool to complete database table additions and deletions9  */Ten  Public classDemo { One     //Insert Function A @Test -      Public voidInsert () { -         Try { the             //gets an object to execute the SQL statement Queryrunner -Queryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ()); -String sql = "INSERT into Sort (sname) VALUES (?)"; -Object[] params = {"Remote control" }; +             intline =qr.update (SQL, params); -             //result set processing +System.out.println ("line =" +Line ); A  at}Catch(SQLException e) { -             Throw NewRuntimeException (e); -         } -     } -  -}

Operation Result:

　　

(5) by deleting

1 Importjava.sql.SQLException;2 3 ImportOrg.apache.commons.dbutils.QueryRunner;4 Importorg.junit.Test;5 6 /*7 * Demo use Dbutils tool to complete database table additions and deletions8  */9  Public classDemo {Ten     //Remove Features One @Test A      Public voidDelete () { -         Try { -             //Create a Queryrunner object to complete the execution of the SQL statement theQueryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ()); -             //Execute SQL statement -String sql = "DELETE from Sort WHERE sid =?"; -Object[] params = {7 }; +             intline =qr.update (SQL, params); -             //processing of result sets +System.out.println ("line=" +Line ); A  at}Catch(SQLException e) { -             Throw NewRuntimeException (e); -         } -     } -  -}

Operation Result:
　　

(6) Change

1 Importjava.sql.SQLException;2 3 ImportOrg.apache.commons.dbutils.QueryRunner;4 Importorg.junit.Test;5 6 /*7 * Demo use Dbutils tool to complete database table additions and deletions8  */9  Public classDemo {Ten     //Update Feature One @Test A      Public voidUpdate () { -         Try { -             //Create a Queryrunner object to complete the execution of the SQL statement theQueryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ()); -             //Execute SQL statement -String sql = "UPDATE Sort SET sid = Sid+2 WHERE sname=?"; -Object[] params = {"Remote control" }; +             intline =qr.update (SQL, params); -             //processing of result sets +System.out.println ("line=" +Line ); A  at}Catch(SQLException e) { -             Throw NewRuntimeException (e); -         } -     } -  -}

Operation Result:

　　

(7) Check

1 Importjava.sql.SQLException;2 3 ImportOrg.apache.commons.dbutils.QueryRunner;4 ImportOrg.apache.commons.dbutils.handlers.BeanHandler;5 Importorg.junit.Test;6 7 /*8 * Demo use Dbutils tool to complete database table additions and deletions9  */Ten  Public classDemo { One     //query function to encapsulate the first record in the result set into a specified javabean.  A @Test -      Public voidsearch () { -         Try { the             //Get Queryrunner -Queryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ()); -             //Execute SQL statement -String sql = "SELECT * from Sort"; +Object[] params = {}; -Sort p = qr.query (sql,NewBeanhandler<sort> (Sort.class), params); +             //result set processing A System.out.println (p); at  -}Catch(SQLException e) { -             Throw NewRuntimeException (e); -         } -     } -  in}

Operation Result:

　　

(8) Common configuration items

　　

Reference documentation Links: Reference documents

00316_DBCP Connection Pool