TOMCAT-DBCP database Connection pool configuration and the use of some pits

One, database connection pool

Development time will often need to do some operations on the database, such as common additions and deletions, and so on, when the amount of data is small, you can directly operate, but when the amount of data increased, every connection and release of the database will take a certain amount of time, this time, You can use database connection pool to keep the database link, reduce the cost of the connection database to the program, and can reduce the pressure of the database, then the database link pool is what kind of thing? As the name implies, it is a pool, the pool is placed on the database link, make an analogy fish pond, is to raise the pond, want to eat fish can go directly to fishing, do not have to personally buy fry fish and so on, database connection pool is put for the database link, unified all the links are set up, Use when you can directly from the inside to take, and then put back to the pool after use, since the use of this thing, then we do not have to write code to implement, some open source can be used directly, there are three kinds of open source connection pool, c3p0,dbcp,proxool these three kinds, for C3P0, Proxool these two kinds of useless, but simply used the DBCP pool, in this talk about how to use the DBCP database connection pool, and the use of some of the pits encountered

Figure 1, before using connection pooling

Figure 2 After using connection pooling

As shown in 1, before using the connection pool, you need to establish a link to the database each time, and need to be released at any time, in the case of large amounts of data, the need for a large database connection cost, and frequent access to the database and release, will also cause a great pressure on the database, figure 2, after using the database connection pool, Put all the links in the pool, do not release, when used directly from the pool to fetch, after use to put back in the pool, the pool to maintain a long link to the database, link disconnection will be automatically re-connected, if the connection is not enough then the subsequent users will need to wait

Second, the use of TOMCAT-DBCP jar package

Include the Tomcat-dbcp.jar, and the rest are some basic packages

Third, the configuration used

dbname. Driver=com.mysql.jdbc.driverdbname.url=jdbc:mysql://<your Ip>/<your dbname>?useUnicode=true& characterencoding=utf-8&autoreconnect=true&failoverreadonly=false&maxreconnects=10& Autoreconnectforpools=true&zerodatetimebehavior=converttonull&connecttimeout=3000dbname. Username=<your Username>dbname. Password=<your Password>dbname. Initialsize=15dbname. Minidle=10dbname. Maxidle=20dbname. Maxwait=5000dbname. Maxactive=20dbname.validationquery=select 1

　　

These configurations only need to be placed in the <yourname>.properties, the meaning of each one

Where Driver,url,username,password is the configuration of common database connections

InitialSize number of connections established for initialization minidle the minimum number of idle links kept in the database connection pool Maxidle The maximum number of connections maintained in the database connection pool maxwait wait for the database connection pool to allocate a connection for the longest time, After the error maxactivite the maximum number of active links, if it is multithreaded can be set to exceed the number of multi-threaded number of links validationquery test whether a connection is a valid SQL statement

　　

Third, Connection pool code

Public abstract class DB {private static hashmap<string, datasource> dstable = new hashmap<string, DataSource    > ();//Here remember to use the static private Basicdatasource DS;    Private PreparedStatement stmt = null; Private DataSource Getdatasource (String N) {if (Dstable.containskey (n)) {return dstable.get (n);//If different                database, multiple connection pools} else {synchronized (dstable) {ds = new Basicdatasource (); Ds.setdriverclassname (dbconfig.getstring ("db", N.concat (". Driver "));//The value of <yourname>.properties is read in Ds.seturl (dbconfig.getstring (" db ", N.concat (".                (Url "))); Ds.setusername (dbconfig.getstring ("db", N.concat (".                Username "))); Ds.setpassword (dbconfig.getstring ("db", N.concat (".                Password "))); Ds.setinitialsize (Dbconfig.getinteger ("db", N.concat (".                InitialSize "))); Ds.setminidle (Dbconfig.getinteger ("db", N.concat (".                Minidle "))); Ds.setmaxidle (Dbconfig.getinteger ("DB ", N.concat (".                Maxidle "))); Ds.setmaxwait (Dbconfig.getinteger ("db", N.concat (".                Maxwait "))); Ds.setmaxactive (Dbconfig.getinteger ("db", N.concat (".                Maxactive ")));                Ds.setvalidationquery (dbconfig.getstring ("db", N.concat (". Validationquery"));                Dstable.put (n, DS);        return DS;}    }} protected Connection conn; public Boolean open () throws SQLException {Basicdatasource bds= (basicdatasource) This.getdatasource (    This.getconnectionname ());        System.out.println ("Connection_number:" +bds.getnumactive () + "dstable:" +dstable);        This.conn = This.getdatasource (This.getconnectionname ()). getconnection ();    return true;    The public void close () throws SQLException {if (this.conn! = null) this.conn.close ();  } protected abstract String getconnectionname ();//This function can pass the name of the database in the public void preparestatement (String sql) according to its own requirements Throws SQLException {this.stmt = THIS.CONN.PReparestatement (SQL); } public void SetObject (int index, Object value, int type) throws SQLException {this.stmt.setObject (index, Valu    E, type);    The public void setobject (int index, Object value) throws SQLException {this.stmt.setObject (index, value);    } public int Execute () throws SQLException {return this.stmt.executeUpdate (); }}

The above is the use of the thread pool used by the code, just give the approximate wording, the specific Dbdao part needs to be based on their own needs to implement, such as batch processing, query, update and other functions, can be based on individual needs to modify, then how to determine the link you created is what you want it? There are two ways to test

1. Set up an empty database to see the number of links

2. See the number of links below Linux

Get ProcessID

PS Aux|grep <your Java name>

View links to Linked databases

Netstat-apn|grep <your processid>

You can see the number of specific links to check if your link pool is correct

Iv. Some of the pits encountered

Because the use of the time is multi-threaded form of use, encountered the most important pit is the use of static, because it is not too familiar, useless static, resulting in each thread has established a database connection pool, there is a "too many files open" error, This is due to the fact that the thread pool is useless with static.

TOMCAT-DBCP database Connection pool configuration and the use of some pits