connection pooling: Resolves resource waste and improves code performance. This section targets: Use the DBCP,C3P0 connection pool to complete the operation of the base database. Use Dbutils to complete crud operations. The solution to the database connection pool is that when the application starts, the system proactively establishes enough database connections and makes these connections a pool of connections. Each time an application requests a database connection, it does not need to reopen the connection, but instead pulls the existing connection out of the connection pool, and then does not close the database connection until it is finished, and returns the connection directly to the connection pool. By using connection pooling, you will greatly improve the efficiency of your program. Database connection pooling is a project for connection objects. Common parameters for database connection pooling are as follows. A, the initial number of connections for the database B, the maximum number of connections for the connection pool C, the minimum number of connections for the connection Pool D, the capacity of the connection pool to increase each time public interface: Javax.sql.DataSource. Common connection pools: DBCP, c3p0 (main) Custom connection pool code implementation improvements (enhanced Close method): A serious problem in the custom connection pool, when the user calls getconnection () to get the connection, The release () method must be used for the return of the connection, and if the user calls Conn.close () The connection is actually freed, no connection will appear in the connection pool. Method Enhancements: A, inheritance, subclasses inherit the parent class, and the parent class's methods are replicated to enhance. Usage Prerequisites: There must be a parent class, and there is an inheritance relationship. B, decorator design pattern, this design pattern is dedicated to the enhancement method. Use premise: Must have interface disadvantage: need to implement all the methods of the interface C, dynamic Proxy: Create the proxy class dynamically at run time, complete the enhancement operation. Similar to decorators usage Prerequisites: interface Difficulty: Requires reflection technology D, bytecode enhancement, runtime creation of target class subclasses for enhanced Common third-party frameworks: Cglib, javassist, etc. Connection pooling frees up resource issues (WEB_10 video 05 custom Connection pool Code implementation improvements (enhanced Close method)): Using c3p0 is also java.sql.connection, as long as it is jdbc are all objects of this interface! After use must con.close () Drop, use connection pool, execute  CON.CLOSE  The TCP connection to the database is not closed, but the connection is returned to the pool, and if not close , the connection will be occupied until the connection in the direct connection pool is exhausted. As to how to do con.close is not the real sense of closing the connection? Instead, direct the connection back to the pool? Generally there are two ways to: one: using the adorner mode, a real connection is introduced into the adorner construction, the adorner implements the connection, uses the construction incoming connection delegate overrides all methods and overwrites the close method: java code ?
| 123456789101112131415161718192021222324252627 |
public class connectiondecorator implements connection { private Connection con = null; Public connectiondecorator (Connection con) { This.con = con; } public void close () throws sqlexception { // Rewrite! } public void commit () throws Sqlexception { this.con.commit (); } public statement createstatement () throws SQLException { return This.con.createStAtement (); } public statement createstatement ( int resultsettype, int resultsetconcurrency) throws sqlexception { return this.con.createstatement (resultsettype, resultsetconcurrency); }   &NBSP, ...} |
the Connection objects that are then controlled by the connection pool are packaged using the adorner two: Dynamic proxy: Using dynamic proxy re-implementation close method, each Get Connection is an agent after the object. A perfect connection pool, its architectural design is very complex,connection#close problem is one of the many design difficulties of connection pool. Summary: A summary of learning MySQL and JDBC! MYSQL mysql Basic Command SQL (full name) statement basis: DML, DDL, DCL, DQL database constraint query (single table, multi-table left and right connections, etc.) truncate and delete comparison? Et cetera second, JDBC programming step 1, load drivers c3p0-config.xmldb.properties 2, get connections Connection pooling Concepts Step J3P0 (XML) 3, dbutils complete crud (add and revise) encapsulates JDBC operations, simplifies JDBC operations JavaBean components (encapsulating data) packages: Com.scalpet.domain4, After the connection pool is exhausted: releasing the resource concludes that the incoming DataSource object will automatically close the connection connection object every time the dbutils is created Queryrunner ~ So no more worrying about the problem of not closing the object ~ If there's no incoming datasource, Need to manually shut down exercise A, build the project---Java PROJECTB, import the JDBC Connection jar Package---Jar packages are added in the new Folder Lib C, import C3p0jar package D, write C3p0-config.xml file E, Writing the C3P0 tool class---its core tool class Combopooleddatasource (named configuration, default configuration)---Tool classes are placed under the new package Com.scalpel.utils package F, Write the JavaBean component user----component under new package Com.scalpel.domain package G, import dbutils jar package H, write Dbutils test JavaClass Code implementation: C3p0-config.xml<?xml version= "1.0" encoding= "UTF-8"?><c3p0-config> < default-config> <property name= "Driverclass" >com.mysql.jdbc.Driver</property> <property name= "Jdbcurl" >jdbc:mysql:///web</property> <property name= "User" >root</property> <property name= "Password" >12345678</property> <property name= "Initialpoolsize" >5</property> <property name= "Maxpoolsize" >20</property> </default-config> <named-config Name= "Scalpel" > <property name= "Driverclass" >com.mysql.jdbc.Driver</property> <property name= "Jdbcurl" >jdbc:mysql:///web</property> &nBsp; <property name= "User" >root</property> <property name= "Password" >12345678</property> </named-config> </c3p0-config> c3p0utils Tool Class: Package Com.scalpel.jdbc.utils; import Java.sql.connection;import Java.sql.sqlexception; import Javax.sql.datasource; import Com.mchange.v2.c3p0.ComboPooledDataSource; public class C3p0utils { //using a named configuration private static Combopooleddatasource DataSource = new Combopooleddatasource ("Scalpel"); /* * Get data source (Connection pool) * / public static DataSource Getdatasource () {&Nbsp; return dataSource; } /* * Get Connected */ public static Connection getconnection () { try { return Datasource.getconnection (); } catch (SQLException e) { throw new RuntimeException (); &Nbsp; } }} userbean Component class: Package com.scalpel.domain; public class User { private int id; private String name; private String password; public int getId () { return id; } public void setId (int id) { this.id = id; } public String getName () { return name; } public void SetName (String name) { this.name = name; } public String GetPassword () { return password; } public void SetPassword (String password) { This.password = password; } } connectivity Test class: Package Com.scalpel.jdbc.link; import Java.sql.sqlexception;import Java.util.list; import Org.apache.commons.dbutils.queryrunner;import Org.apache.commons.dbutils.handlers.beanlisthandler;import Org.junit.test; import Com.scalpel.domain.User; Import Com.scalpel.jdbc.utiLs. C3p0utils; public class Linkjdbc { /* * Add Users */ @ test public void AddUser () { try { //1. Connecting a data source connection pool queryrunner qr = new QueryRunner ( C3p0utils.getdatasource ()); //2. Writing SQL statements string sql = "INSERT into user values (?,?, null)";nbsp; //3. Add params parameter object[] params = {"Ctxixi", "123"}; //4. Execute SQL statement int rows = Qr.update (SQL, params); //5. Determine whether the execution succeeds if (Rows > 0) { &Nbsp; system.out.println ("Add success"); } else { System.out.println ("Add failed"); } } catch (SQLException e) { e.printstacktrace (); } } /* * Query users, use beanlisthandler */ @Test public Void Queryalluserinf () { try { queryrunner qr = new Queryrunner (C3p0utils.getdatasource ()); string sql = "SELECT * from User"; List<User> queryuser = qr.query (sql, new beanlisthandler< User> (User.class)); for (User user:queryuser) { system.out.println (User.getid () + "," + user.getname () + ":" + User.getpassword ()); } } catch ( SQLException e) { //TODO auto-generated Catch block e.printstacktrace (); } } }
MySql & JDBC & Connection Pooling & Summary
