Javaweb Getting Started-database connection pool (data source) __c#

An Introduction

The previous article mentioned that through the connection to access and manipulate the database, each connection to the database is to create a connection object, access is completed and then close the connection, as follows:

The disadvantage of this approach is obvious: the user needs to get a link to the database on each request, and the database creation connection typically consumes relatively large resources and is created for a long time. If the site 100,000 visits a day, the database server will need to create 100,000 connections, a huge waste of database resources, and very easy to cause the database server memory overflow, serious words or even downtime.

Is there any way to optimize it? The answer is yes, that is the database connection pool technology (data source).

Two uses the database connection pool optimizes the program performance

principle : The application Initializes a batch of connection objects into a pool (set) when it is started, and takes connection objects from the pool when the application needs to access the database. Once the operation database is complete, put the connection object back into the pool. This avoids the repeated creation of the connection object, as follows:

Three to implement their own database connection pool

Implementing your own connection pool requires implementing the Java.sql.DataSource interface. Two overloaded getconnection methods are defined in the DataSource interface:
Connection getconnection () Connection getconnection (string Username, string password) implements the DataSource interface and steps to connect pooling functionality:
Create a connection to the database in bulk in the DataSource constructor and add the created connection to the LinkedList object. Implement the Getconnection method so that the Getconnection method takes a connection from the LinkedList and returns it to the user each time it is invoked. When the user finishes using connection and calls the Connection.close () method, the collection object should ensure that it returns to the LinkedList instead of the Conn back to the database.

Package com.ricky;
Import Java.io.InputStream;
Import Java.io.PrintWriter;
Import Java.lang.reflect.InvocationHandler;
Import Java.lang.reflect.Method;
Import Java.lang.reflect.Proxy;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;
Import java.util.LinkedList;

Import java.util.Properties;

Import Javax.sql.DataSource;

Import com.itheima.exception.NotSupportException; public class myDataSource implements datasource{private static linkedlist<connection> pool = new Linkedlist<con
	Nection> ();
			static{try{InputStream in = SimpleConnectionPool.class.getClassLoader (). getResourceAsStream ("dbcfg.properties");
			Properties Props = new properties ();
			Props.load (in);
			Class.forName (Props.getproperty ("ClassName")); for (int i=0;i<10;i++) {Connection conn = drivermanager.getconnection (props.getproperty ("url"), Props.getproperty (
				"username"), Props.getproperty ("password"));
			POOL.ADD (conn); for (Connection Conn:pooL) {System.out.println ("Initialized connection is:" +conn);
		}}catch (Exception e) {throw new Exceptionininitializererror (e);
	} public linkedlist<connection> Getpool () {return pool; @Override Public synchronized Connection getconnection () throws SQLException {//conn.commit () Conn.pro conn.close (
			if (Pool.size () >0) {final Connection conn = Pool.removefirst (); Gets an instance of the proxy object Connection proxyconn = (Connection) proxy.newproxyinstance (Conn.getclass (). getClassLoader (), Conn.getclass (). Getinterfaces (), new Invocationhandler () {@Override public object Invoke (object proxy, Method met Hod, object[] args) throws Throwable {if (!method.getname (). Equals ("Close")) {//method to invoke the original object return me
					Thod.invoke (conn, args);
					}else{//close return Pool.add (conn);
			}
				}
			});
			System.out.println (Proxyconn.getclass (). GetName ());
		return proxyconn;
		}else{throw new RuntimeException ("The Server is Busy"); }} @Override PublIC Connection getconnection (string Username, string password) throws SQLException {throw new Notsupportexception ("Th
	Is method isn't supported by this DataSource "); @Override public PrintWriter Getlogwriter () throws SQLException {throw new Notsupportexception ("This method is not Su
	Pported by this DataSource "); @Override public int getlogintimeout () throws SQLException {throw new Notsupportexception ("This method is not support
	Ed by this DataSource "); @Override public void Setlogwriter (PrintWriter out) throws SQLException {throw new Notsupportexception ("This Metho
	D not supported by this DataSource ");  @Override public void setlogintimeout (int seconds) throws SQLException {throw new Notsupportexception (' This method
	Not supported by this DataSource "); @Override public boolean iswrapperfor (Class<?> iface) throws SQLException {throw new Notsupportexception ("Th
	Is method isn't supported by this DataSource "); @Override Public <T> T Unwrap(Class<t> iface) throws SQLException {throw new Notsupportexception ("This method isn't supported by this DataSource
	");
 }

}

four open source database connection pool

Many Web servers (Weblogic, WebSphere, Tomcat) now offer a datasoruce implementation, the implementation of a connection pool. Usually we put the implementation of DataSource, according to its English meaning is called the data source, the data source contains the database connection pool realization.
There are also open source organizations that provide an independent implementation of the data source:
DBCP database Connection pool C3P0 database connection pool actual application does not need to write the connection database code, obtains the database connection directly from the data source. Programmers should also use the implementation of these data sources as much as possible to improve the database access performance of the program.

1, DBCP

DBCP is an open source connection pool under the Apache Software fund organization, and Tomcat's connection pool is implemented with this connection pool. This database connection pool can be used either in combination with the application server or independently by the application.

Website address: http://commons.apache.org/proper/commons-dbcp/

How to use

1.1, Dbcp.properties

#连接数据库所用的 JDBC Driver Class,
driverclassname=com.mysql.jdbc.driver
url=jdbc:mysql://localhost:3306/day14
username=root
password=sorry

#<!--The number of initial connections created when the connection pool is started (the default is 0)-->
initialsize=10

# The maximum number of connections that can be connected at the same time in the connection pool, 0 is unrestricted, the default is 8
maxactive=20

#<!--The maximum number of idle connections in the connection pool (default is 8, set 0 is unrestricted), and over idle connections will be freed-->
maxidle=15

#<!--The minimum number of idle connections in the connection pool (default is 0, generally adjustable 5)-->
minidle=5

#< The maximum wait time for error messages is thrown out of the!--over time ( Unit is ms)-->
maxwait=60000

Connectionproperties=useunicode=true;characterencoding=utf8

#对于事务是否 Autocommit, the default value is True
defaultautocommit=true

#对于数据库是否只能读取, the default value is False
defaultreadonly=

# The default thing is to isolate a few, take value: none,read_uncommitted, read_committed, Repeatable_read, SERIALIZABLE
defaulttransactionisolation =repeatable_read

The underlying parameter description  
Defaultautocommit: The default value is True
Defaultreadonly if the transaction is autocommit, and the default value is False for the database to be read
InitialSize: The number of initial connections created when the connection pool is started (the default is 0)
Driverclassname: JDBC Driver Class used to connect to the database,
URL: url to connect to the database
Username: The account used to log in to the database
Password: the password used to log into the database
Maxactive: The maximum number of connections that can be connected at the same time in a connection pool, 0 for No limit, and 8 for
Maxidle: The maximum number of idle connections in the connection pool (default is 8, set 0 to No limit), more than idle connections will be freed, if set to a negative number means no limit (Maxidle cannot be set too small, because if the connection is open more quickly than the time of shutdown, it will cause a idle in the connection pool Rise more than maxidle, causing frequent connection destruction and creation)
Minidle: The minimum number of idle connections in the connection pool (default 0, generally adjustable 5), below which a new connection is created (the closer the parameter is to Maxidle, the better the performance, because the connection is created and destroyed, are required to consume resources; but not too large, because when the machine is idle, it also creates a connection below the Minidle number
Maxwait: The maximum latency (in MS) that the error message will be thrown over time, and the maximum time that the connection pool waits for the connection to be released when no connection is available, Exceeding this time limit throws an exception if setting-1 indicates an infinite wait (default is-1, typically adjustable to 60000ms, to avoid the request being indefinitely suspended because the thread pool is not sufficient)
Validationquery: Verifying that the connection was successful, SQL SELECT The instruction must return at least one row of
Removeabandoned: If a recycle (default is False)
Removeabandonedtimeout for unused connections after removeabandonedtimeout time is exceeded: Over time limit, recycle five connections (default 300 seconds), removeabandoned must be true
Logabandoned: Whether to log interrupt events, default to False

1.2 Sample Code

Package com.itheima.pool;

Import Java.io.InputStream;
Import java.sql.Connection;
Import java.util.Properties;

Import Javax.sql.DataSource;

Import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPDemo1 {public

	static void Main (string[] args) throws Exception {
		Properties props = new Properti Es ();
		InputStream in = DBCPDemo1.class.getClassLoader (). getResourceAsStream ("Dbcp.properties");
		Props.load (in);
		Basicdatasourcefactory facotry = new Basicdatasourcefactory ();
		DataSource ds = Facotry.createdatasource (props);
		Connection conn = Ds.getconnection ();
		System.out.println (Conn.getclass (). GetName ());
		Conn.close ()//change back to Pool
	}
}

2, C3P0

C3P0 is a more powerful, more flexible configuration of data sources, official website address: http://www.mchange.com/projects/c3p0/

2.1 Setting individual configuration items by code

Combopooleddatasource CPDs = new Combopooleddatasource ();
Cpds.setdriverclass (Props.getproperty ("Driverclass"));
Cpds.setjdbcurl (Props.getproperty ("Jdbcurl"));
Cpds.setuser (Props.getproperty ("user"));
Cpds.setpassword (Props.getproperty ("password"));

2.2 Through the configuration file

A c3p0-config.xml file is provided under the Classpath, as follows:

<?xml version= "1.0" encoding= "UTF-8"?> <c3p0-config> <default-config> <property name= " Driverclass ">com.mysql.jdbc.Driver</property> <property name=" Jdbcurl ">jdbc:mysql:///day14</ property> <property name= "user" >root</property> <property name= "password" >sorry</property > <property name= "initialpoolsize" >15</property> <property name= "MaxIdleTime" >30</property > <property name= "maxpoolsize" >20</property> <property name= "Minpoolsize" >5</property> & Lt;property name= "maxstatements" >2000</property> </default-config> <!--this app is massive!
		--> <named-config name= "Day14" > <property name= "Driverclass" >com.mysql.jdbc.Driver</property> <property name= "Jdbcurl" >jdbc:mysql:///day14</property> <property name= "user" >root</property > <property name= "password" >sorry</property> <propertY name= "initialpoolsize" >15</property> <property name= "MaxIdleTime" >30</property> < Property Name= "Maxpoolsize" >20</property> <property name= "Minpoolsize" >5</property> < Property Name= "Maxstatements" >2000</property> </named-config> </c3p0-config>

Get the data source method as follows:

Combopooleddatasource ds = new Combopooleddatasource ()//Get default-config
combopooleddatasource ds = new in configuration file Combopooleddatasource ("Day14");
Connection conn = Ds.getconnection ();

More detailed usage can refer to this article: http://my.oschina.net/lyzg/blog/55133

The explanation of the JDBC data source is over here, and the following article will introduce the database operating framework, such as Dbutils, MyBatis, and so on.