Connect to MySQL database using JDBC-typical case Study (iii)----using Apach DBCP connection Pool D

Problem:

This case requires the use of Apach DBCP to connect the data pool refactoring class dbutility to the Connectionsource class, refactoring the case "implement Dbutility", providing access to the connection, closing the function " The Empdao class in which you use Connectionsource to obtain a connection.

Scheme:

When accessing a database directly using JDBC, you need to avoid the pitfalls:

1. Every data operation request is a step to establish a database connection, open the connection, access the data, and close the data. The establishment and opening of the database is a waste of resources and time-consuming process, if the frequent occurrence of such database operations, it is bound to reduce the performance of the system.

2. The Connection object represents the connection process of the database system and is a limited resource. If the system uses a lot of users, it is possible to exceed the limit of the database server, resulting in a system crash.

Database connection pooling is the most common way to solve these problems. So-called connection pooling, you can create and hold the components of a database connection. A connection pool can pre-create and encapsulate some connection objects and cache them, and you can "borrow" a "connection" to the connection pool when you need to use a connection object, and then "return" it to the connection pool after it is exhausted. The main features of the database connection pool are as follows:

1> the creation and deallocation of connection pool objects.

When the 2> server starts, a specified number of database connections are created.

3> provides available connections for user requests. If there is no idle connection and the number of connections does not exceed the maximum, create a new database connection.

4> identifies a connection that the user no longer uses as an available connection and waits for another user to request it.

5> the Connection object is released when the number of idle connections is excessive.

Connection pooling components generally need to implement the Javax.sql.DataSource interface in the JDBC specification. The DataSource interface defines the method Getconnection () method that gets the connection object. The common connection pool components are dbcp, c3p0 and Proxool, and this case takes Apache's DBCP component as an example to implement the database connection pool. The simple application code is as follows;

Basicdatasource ds=new Basicdatasource ();d s.seturl ("Jdbc:mysql://127.0.0.1:3306/emp");d s.setdriverclassname (" Com.mysql.jdbc.Driver ");d s.setusername (" Wonderq ");d S.setpassword (" root "); Connection con=ds.getconnection (); Statement stmt=con.createstatement (); ResultSet rs=stmt.executequery ("SELECT count (*) from EMP") if (Rs.next ()) {System.out.println (Rs.getint (1));} Stmt.close (); Con.close ();

Steps:

The implementation of this case needs to follow the following steps;

Step one: Import the jar packages required to use the DBCP component

Under current engineering, import the jar packages required to use the DBCP component,    including Commons-dbcp.jar and Commons-pool.jar two jar packages, the names of these two jar packages may be different from the version, the last version of the name information, for example: Commons-dbcp-1.4.jar Commons-pool-1.5.jar sometimes need commons-collections-3.1.jar.

Step Two: Reconstruct db.properties

Refactor this file to add the information required to create a database connection pool in the file, including the number of initialized connections, maximum number of idle connections, minimum number of idle connections, maximum number of connections, and time-out collection. The contents of the document are as follows:

<span style= "White-space:pre" ></span>jdbc.driver=com.mysql.jdbc.driverjdbc.url=jdbc:mysql:// 127.0.0.1:3306/empjdbc.user=wonderqjdbc.password=rootdatasource.initialsize=10;//Initialize connection size datasource.maxidle=20 ;//maximum number of idle datasource.minidle=5;//minimum idle number datasource.maxactive=50;//maximum number of connections datasource.maxwait=1000//timeout wait time, in milliseconds.

Step three: Wear a Connectionsource class, and then add the Init method to the class that initializes the data source information in the method, as shown in the following code:

Package Dao;import Java.io.ioexception;import Java.sql.connection;import java.sql.sqlexception;import Java.util.properties;import Org.apache.commons.dbcp.basicdatasource;public class Connectionsource {private static Basicdatasource datasource=null;public Connectionsource () {}public static void init () {Properties dbprops=new Properties ();//The configuration file can be modified according to the actual different try {Dbprops.load (ConnectionSource.class.getClassLoader (). getResourceAsStream (" Db.properties "));} catch (Exception e) {e.printstacktrace ();} try {String driverclassname=dbprops.getproperty ("Jdbc.driver"); String url=dbprops.getproperty ("Jdbc.url"); String username=dbprops.getproperty ("Jdbc.user"); String password=dbprops.getproperty ("Jdbc.password"); String initialsize=dbprops.getproperty ("datasource.initialsize"); String minidle=dbprops.getproperty ("Datasource.minidle"); String maxidle=dbprops.getproperty ("Datasource.maxidle"); String maxwait=dbprops.getproperty ("datasource.maxwait"); String maxactive=dbprops.getproperty ("datasource.maxactive");DataSource =new Basicdatasource ();d atasource.setdriverclassname (driverclassname);d atasource.seturl (URL); Datasource.setusername (username);d Atasource.setpassword (password);//Initialize the number of connections if (initialsize!=null) { Datasource.setinitialsize (Integer.parseint (InitialSize));} Minimum idle connection if (minidle!=null) {Datasource.setminidle (Integer.parseint (Minidle));} Maximum idle connection if (maxidle!=null) {Datasource.setmaxidle (Integer.parseint (Maxidle));} Time-out payback (in milliseconds) if (maxwait!=null) {datasource.setmaxwait (Long.parselong (maxwait));} Maximum number of connections if (maxactive!=null) {if (!maxactive.trim (). Equals ("0")) {datasource.setmaxactive (Integer.parseint ( maxactive));}}} catch (Exception e) {e.printstacktrace (); System.out.println ("Failed to create connection pool! Please check the settings!!! ");}}}

Step Four: Add a method to get a connection

In the Connectionsource class, add a method to get the connection, getconnection, as shown in the following code:

public static synchronized Connection getconnection () throws Sqlexception{if (Datasource==null) {init ();} Connection con=null;if (datasource!=null) {con=datasource.getconnection ();} return con;}

Step Four : Refactoring Empdao class

Refactor the Empdao class to get the connection using the Getconnection () method of the Connectionsource class in the class, as shown in the following code:

Package Dao;import Java.sql.connection;import Java.sql.resultset;import java.sql.sqlexception;import Java.sql.statement;public class Empdao {public static void main (String [] args) {Empdao dao=new Empdao ();d ao.findall ();} public void FindAll () {Connection con=null; Statement Stmt=null; ResultSet rs=null;try {con=connectionsource.getconnection (); Stmt=con.createstatement (); Rs=stmt.executequery (" Select Empno,ename,sal,hiredate from emp; "); while (Rs.next ()) {System.out.println (Rs.getint ("empno") + "," +rs.getstring ("ename") + "," +rs.getdouble ("sal") + "," + Rs.getdate ("HireDate"));}} catch (SQLException e) {System.out.println ("Database access Exception! "); throw new RuntimeException (e);} Finally{try {if (rs!=null) {rs.close ();} if (stmt!=null) {stmt.close ();} if (con!=null) {con.close ();}} catch (SQLException e) {System.out.println ("an exception occurred while releasing the resource! ");}}}}

Here, the Close method of the connection class is called to close the connection and the connection is returned to the connection pool.

Run the Empdao class with the output as follows:

Consistent with the previous case output.

This section, first update to here, next continue to write: How to update and insert the EMP data.

Connect to MySQL database using JDBC-typical case Study (iii)----using Apach DBCP connection Pool D