The JDBC Pool in Tomcat

What is a connection pool

What is it? Connection Pool -- 连接池

I won't explain it. Not too clear to see this article [Production Level Nodejs development practice-using connection pooling]（这篇文章的前半部分讲述了什么是连接池）

JDBC Connection pool in Tomcat

Its English document is in: [The Tomcat JDBC Connection Pool]

Tomcat is not only a very popular Servlet container, but it also provides us with a lot of very useful components. is one of the jdbc pool most practical and efficient implementations of JDBC connection pooling. Tomcat officially lists 15 Tomcat's advantages over other connection pools, which I don't explain in detail here.

jdbc poolThe component exists in the TOMCAT8 release version of the lib/tomcat-jdbc.jar package.

All of the classes we use exist under the name of the org.apache.tomcat.jdbc.pool package.

Depend on

We're going to mysql show this as a database.
Add mysql-connector Dependency

<dependency>    <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.34</version></dependency>

And, of course, ourtomcat-jdbc

<dependency>    <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> <version>8.0.15</version></dependency>

Note that the tomcat-jdbc dependency tomcat-juli package is the log frame in Tomcat. is almost dependent on all Tomcat packages. If you use maven installation dependencies, do not bother, because it will be automatically installed, if you download the jar package separately, or to download the jar package to CLASS_PATH .

Use code to describe

The nonsense is here, it's time to get some code. How do I create a JDBC connection pool instance?

Import java.sql.Connection;Import java.sql.PreparedStatement;Import Java.sql.ResultSet;Import java.sql.SQLException;Import Org.apache.tomcat.jdbc.pool.DataSource;Import org.apache.tomcat.jdbc.pool.PoolProperties;MainCreate Connection Pool Property Object Poolproperties Poolprops =New Poolproperties ();p Oolprops.seturl ("Jdbc:mysql://localhost:3306/test");p Oolprops.setdriverclassname ("Com.mysql.jdbc.Driver");p Oolprops.setusername ("Root");p Oolprops.setpassword ("123456");Create a connection pool, using the implementation provided by Tomcat, which implements the Javax.sql.DataSource interface DataSource DataSource =New DataSource ();Set the attribute datasource.setpoolproperties (poolprops) for the connection pool;try (Connection conn = Datasource.getconnection ()) {PreparedStatement state = Conn.preparestatement ( "SELECT * from book"); ResultSet result = State.executequery (); StringBuilder StringBuilder = new StringBuilder (); while (Result.next ()) {stringbuilder.append ( "[ID:" + Result.getint ( "id") +  ","). Append ( "Name:" + result.getstring ( "name") +  "Publisher:" + result.getstring ( "publisher"). Append (  "]\n"); } System.out.println (Stringbuilder.tostring ());} catch (SQLException e) {e.printstacktrace ();       

As you can see from the code, the creation of a connection pool is simple. PoolPropertiesThe class is the managed Connection pool property. We configure connection pooling, all through this class.

To take a look at the properties of this class, there are 50 properties that control the working state and life cycle of connections in the connection pool and pool. Next I'll explain the commonly used attributes.

    PrivateVolatile Properties dbproperties =New Properties ();PrivateVolatile String URL =NullPrivateVolatile String Driverclassname =NullPrivateVolatile Boolean Defaultautocommit =NullPrivateVolatile Boolean defaultreadonly =NullPrivateVolatileint defaulttransactionisolation = datasourcefactory.unknown_transactionisolation;PrivateVolatile String Defaultcatalog =NullPrivateVolatile String connectionproperties;PrivateVolatileint initialsize =10;PrivateVolatileint maxactive = default_max_active;PrivateVolatileint maxidle = maxactive;PrivateVolatileint minidle = InitialSize;PrivateVolatileint maxwait =30000;PrivateVolatile String validationquery;PrivateVolatileint validationquerytimeout =-1;PrivateVolatile String validatorclassname;PrivateVolatile Validator Validator;PrivateVolatileBoolean testonborrow =FalsePrivateVolatileBoolean Testonreturn =FalsePrivateVolatileBoolean testwhileidle =FalsePrivateVolatileint timebetweenevictionrunsmillis =5000;PrivateVolatileint numtestsperevictionrun;PrivateVolatileint minevictableidletimemillis =60000;PrivateVolatileBoolean accesstounderlyingconnectionallowed =TruePrivateVolatileBoolean removeabandoned =FalsePrivateVolatileint removeabandonedtimeout =60;PrivateVolatileBoolean logabandoned =FalsePrivateVolatile String name ="Tomcat Connection pool[" + (Poolcounter.addandget (1)) +"-" +system.identityhashcode (Poolproperties.class) +"]";Privatevolatile String password;PrivateVolatile String username;PrivateVolatileLong Validationinterval =30000;PrivateVolatileBoolean jmxenabled =TruePrivateVolatile String Initsql;PrivateVolatileBoolean testonconnect =FalsePrivateVolatile String jdbcinterceptors=NullPrivateVolatileBoolean fairqueue =TruePrivateVolatileBoolean useequals =TruePrivateVolatileint abandonwhenpercentagefull =0;PrivateVolatileLong MaxAge =0;PrivateVolatileBoolean uselock =FalsePrivateVolatile interceptordefinition[] Interceptors =NullPrivateVolatileint suspecttimeout =0;PrivateVolatile Object DataSource =NullPrivateVolatile String Datasourcejndi =NullPrivateVolatileBoolean alternateusernameallowed =FalsePrivateVolatileBoolean Commitonreturn =Falseprivate volatile boolean Rollbackonreturn = FALSE; private volatile boolean Usedisposableconnectionfacade = true; private volatile boolean Logvalidationerrors = FALSE; private volatile boolean Propagateinterruptstate = FALSE; private volatile boolean Ignoreexceptiononpreload = false;         

url,,, driverClassName username password These properties know the students of JDBC, I will not explain.

• Name of the connection pool

• DEFAULTAUTOCOMMIT Specifies the auto-commit (Auto-commit) state of the connection created by the connection pool.

• Defaultreadonly read-only properties of a connection to a database created by a connection pool

• Defaulttransactionisolation
  Specifies the transaction level (transactionisolation) of the connection created by the connection pool. The available values are one of the following: (Details visible Javadoc)
  NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ,SERIALIZABLE

• Minevictableidletimemillis

• jmxenabled to open JMX management functions

• Testwhileidle will use the test thread to test whether the connection in the pool is working properly.

• Validationquery
  Specifies whether the check of the Idle object eviction process passes when the connection enters an idle state (if there is an idle object eviction process). If the checksum does not pass, the connection is disconnected by the connection pool.
  Note: If the setting takes effect when the value is true, the Validationquery parameter must be a non-empty string.

• Testonborrow
  Specifies whether the connection is validated when it is invoked. If the checksum does not pass, the connection is disconnected by the connection pool and an attempt is made by the connection pool to invoke another connection.
  Specifies whether the connection is validated when it is returned to the pool.
  Note: If the setting takes effect when the value is true, the Validationquery parameter must be a non-empty string.

• Testonreturn
  Specifies whether the connection is validated when it is returned to the pool.
  Note: If the setting takes effect when the value is true, the Validationquery parameter must be a non-empty string.

• Validationinterval verifies the time interval in milliseconds.

• Timebetweenevictionrunsmillis the number of idle objects in milliseconds that the eviction process is running from the state into hibernation. A value of non-positive indicates that no idle object is running during the eviction process.

• Maximum number of working connections in the maxactive pool, this value is non-positive and is expressed without restriction

• InitialSize in milliseconds when there are no available connections in the connection pool waiting for the time available for the connection to return, the timeout throws an exception, and the value is 1 for an indefinite wait.

• Maxwait in milliseconds when there are no available connections in the connection pool waiting for the time available for the connection to return, the timeout throws an exception, and the value is 1 for an indefinite wait.

• The minimum number of idle connections in the Minidle pool, when the number of connections is less than this value, the pool creates a connection to replenish the number of values

• Maximum number of connections allowed in the Maxidle pool, non-positive when the value is not limited

• Numtestsperevictionrun Connection Pool checks the number of objects per free object eviction process (if there is an idle object eviction process)

• Minevictableidletimemillis the minimum time, in milliseconds, that a connection is kept idle in the pool before being evicted by an idle object, if there is an idle object eviction process.

• logabandoned
  Flags that clear information is logged in the log when an invalid connection is cleared. Records invalid statements and connections, and attaches the overhead of each connection opening or creating a new statement. If you enable "removeabandoned", a connection that is set to be invalid may be reclaimed by the connection pool.
  This mechanism will start when the following two conditions are met: (Getnumidle () < 2) and (Getnumactive () > Getmaxactive ()-3)
  For example: Suppose Maxactive=20, which currently has 18 active connections, 1 idle connections, and the "removeabandoned" mechanism will start. However, the connection is cleared only if the active connection is not used longer than "Removeabandonedtimeout" (the default is 300 seconds). When you traverse a result set, the connection that is used is not marked as an active connection.

• removeabandoned
  Whether to purge invalid connections that have exceeded the "removeabandonedtimout" setting.
  An invalid connection that exceeds the "removeabandonedtimout" setting will be cleared if the value is "true". Setting this property allows you to recover a connection from a program that does not have the appropriate shutdown connection.
  Records invalid statements and connections, and attaches the overhead of each connection opening or creating a new statement.

• Removeabandonedtimeout the time period in seconds to clear invalid connections.

• Jdbcinterceptors
  Sets the interceptor for the Tomcat JDBC Connection pool.
  Built-in interceptors: org.apache.tomcat.jdbc.pool.interceptor.ConnectionState
  Track status such as Autocommit, read-only status, catalog, and transaction isolation levels

Org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer tracks Open statement and closes them when the connection is returned.

Multiple interceptors ; are segmented, for example:

poolProps.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");

• Datasourcejndi
  Specify a jndi as the data source
• Accesstounderlyingconnectionallowed

The above attributes are best to keep the default values when you and I do not understand them.

The following are to be continued ...

The JDBC Pool in Tomcat