Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to use Java database connection pool Tomcat". In daily operation, I believe many people have doubts about how to use Java database connection pool Tomcat. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use Java database connection pool Tomcat". Next, please follow the editor to study!

Foreword:

Tomcat connection pooling is a highly concurrent connection pool rewritten from Tomcat 7 to replace the DBCP 1 connection pool previously used in Tomcat. It can be configured for use in Tomcat or can be used alone. This paper mainly introduces the basic use of Tomcat connection pool. the software versions used in this paper are Java 1.8.0 / 191, Tomcat 8.5.72 and Spring Boot 2.3.12.RELEASE.

1. Configuration parameter 1.1.The default value of the basic configuration parameter describes that the factory must be javax.sql.DataSource or javax.sql.XADataSource1.2, and the default value of the system property parameter describes whether org.apache.tomcat.jdbc.pool.onlyAttemptCurrentClassLoaderfalse only uses the current class loader (the class loader that loads the connection pool) to load the dynamic class 1.3. general parameters

These parameters are the same as DBCP, but some of the default values are different.

The parameter default value describes whether the default value of the defaultAutoCommit driver is automatically submitted whether the default value of the defaultReadOnly driver is read-only defaultTransactionIsolation driver

Default transaction isolation level

NONE 、 READ_COMMITTED 、 READ_UNCOMMITTED 、

REPEATABLE_READ 、 SERIALIZABLE

DefaultCatalog

Default catalog. (directory, similar to the schema name, but more abstract than the schema name

Oracle,MySQL is not supported, MS SQL Server = database name)

DriverClassName driver name url connection urlusername user name password password maxActive100 maximum number of active connections minIdle10 minimum number of idle connections initialSize10 initial number of connections maxWait3000 obtains connections from connection pool, maximum wait time (in seconds) testOnBorrowfalse verifies validity when obtaining connections from connection pool; if authentication fails, the connection is discarded. When the testOnConnecttrue connection is created, whether to verify the validity testOnReturnfalse connection returns to the connection pool, whether to verify the validity testWhileIdlefalse connection when idle, whether to verify the validity validationQuerynull

Query sql for connection check

If specified, the SQL does not need to return a result, as long as the SQLException; is not thrown. If it is not specified, it is verified by calling the isValid () method.

ValidationQueryTimeout-1 verifies the timeout of the query in seconds; a non-positive number indicates that the feature is not enabled. The class name of the validatorClassNamenull check, which implements the org.apache.tomcat.jdbc.pool.Validator interface and contains a no-argument constructor. The time period for timeBetweenEvictionRunsMillis5000 to verify idle connections (milliseconds) cannot be set to less than 1 second. Non-positive means that at least how long (milliseconds) minEvictableIdleTimeMillis60000 idle connections are not verified will be verified by removeAbandonedfalse whether to delete leaked connections removeAbandonedTimeout60 connection leaked timeout (seconds) whether stack information connectionPropertiesnull is printed when logAbandonedfalse connections are deleted

Connection attribute, in the format: [propertyName=property;] *

"user" and "password" will be excluded, so you don't need to include these two attributes here.

1.4.The default value of the enhanced parameter describes the initialization SQLjdbcInterceptorsnull performed when the initSQLnull connection is created.

Jdbc interceptor, which needs to inherit the existing interceptor of org.apache.tomcat.jdbc.pool.JdbcInterceptor;:

Org.apache.tomcat.jdbc.pool.interceptor.ConnectionState auto-commit, read-only, directory, and transaction isolation level tracking

Traces for org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer statement, turning off connections when they return to the connection pool

Minimum interval of validationInterval3000 connection verification (milliseconds) whether jmxEnabledtrue registers the connection pool to whether JMXfairQueuetrue uses fair queues. If true, the connection will be closed according to the "first-in-first-out" principle when abandonWhenPercentageFull0 leaks that the connection reaches the abandonWhenPercentageFull ratio. 0 means that the leaked connection will immediately close the maximum lifetime of the maxAge0 connection. This detection is performed when a connection is obtained from the connection pool and the connection returns to the connection pool, and if now-time-when-connected > maxAge, the connection is closed; 0 indicates that the detection is not performed. Whether useEqualstrue uses String.equals to determine whether ProxyConnection is equal suspectTimeout0 is similar to removeAbandonedTimeout, but this setting only prints the log and does not delete the connection; it takes effect only if it is greater than 0. Whether the rollbackOnReturnfalse connection automatically rolls back the transaction when it returns to the connection pool. Whether the commitOnReturnfalse connection automatically commits the transaction when it returns to the connection pool; if rollbackOnReturn==true, this parameter is ignored. Whether alternateUsernameAllowedfalse allows new credentials to be set when obtaining a connection from the connection pool. By default, the connection pool ignores the call to DataSource.getConnection (username,password) and returns a created connection directly; if you want to use different credentials to obtain the connection, that is, DataSource.getConnection (username,password) takes effect, you can set this parameter to true. DataSourcenull sets the data source from which the connection pool gets whether the jndiuseDisposableConnectionFacadetrue connecting to the dataSourceJNDInull data source uses the connection appearance; setting it to true prevents reuse after the connection is closed. Whether logValidationErrorsfalse records checked errors propagateInterruptStatefalse propagates thread interrupt status ignoreExceptionOnPreLoadfalse ignores errors in connection creation useStatementFacadetrue if you want to use wrapper statement so that the equals () and hashCode () method is called on the closed statement when the statement proxy is set, set this to true.

For detailed instructions, please refer to the official website document: https://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html

2. Use 2.1,2.1.1 directly, introduce dependency org.apache.tomcat tomcat-jdbc 8.5.722.1.2, use example package com.abc.demo.general.dbpool;import org.apache.tomcat.jdbc.pool.DataSource;import org.apache.tomcat.jdbc.pool.PoolProperties;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement Public class TomcatPoolCase {public static void main (String [] args) {PoolProperties poolProperties = new PoolProperties (); poolProperties.setName ("Tomcat connection Pool"); poolProperties.setUrl ("jdbc:mysql://10.40.9.11:3306/mydb?useUnicode=true&characterEncoding=UTF-8"); poolProperties.setDriverClassName ("com.mysql.cj.jdbc.Driver"); poolProperties.setUsername ("root"); poolProperties.setPassword ("123456") PoolProperties.setJmxEnabled (true); poolProperties.setTestWhileIdle (false); poolProperties.setTestOnBorrow (true); poolProperties.setValidationQuery ("SELECT 1"); poolProperties.setTestOnReturn (false); poolProperties.setValidationInterval (30000); poolProperties.setTimeBetweenEvictionRunsMillis (30000); poolProperties.setMaxActive (100); poolProperties.setInitialSize (10); poolProperties.setMaxWait (10000); poolProperties.setRemoveAbandonedTimeout (60) PoolProperties.setMinEvictableIdleTimeMillis (30000); poolProperties.setMinIdle (10); poolProperties.setLogAbandoned (true); poolProperties.setRemoveAbandoned (true); poolProperties.setJdbcInterceptors ("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;" + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"); DataSource datasource = new DataSource (); datasource.setPoolProperties (poolProperties) Connection connection = null; try {connection = datasource.getConnection (); Statement st = connection.createStatement (); ResultSet rs = st.executeQuery ("select version ()"); if (rs.next ()) {System.out.println (rs.getString (1)) }} catch (SQLException e) {e.printStackTrace ();} finally {close (connection);} / / in practice, the data source is initialized when the application starts, and the application acquires a connection from the data source. The data source is not closed. Datasource.close ();} private static void close (Connection connection) {if (connection! = null) {try {connection.close ();} catch (SQLException e) {e.printStackTrace ();} 2.2, configured in Tomcat as a resource

First copy the driver package of the corresponding database to the lib directory of Tomcat or application, and then configure the resources in content.xml

Content.xml can be located in the following locations:

Conf/context.xml: for all applications

Conf/Catalina/localhost: suitable for deploying applications outside Tomcat for a single application

{App} / META-INFcontext.xml: for a single application

Examples of configuring resources are as follows:

You can find this resource through jndi, and here is a demonstration of how to find it through jsp:

Find the data source through jndi and get the version information of the database 3.2, using 3.1.1 in SpringBoot, Introduction of dependent org.springframework.boot spring-boot-starter-parent 2.3.12.RELEASE org.springframework.boot spring-boot-starter-web org.springframework spring-jdbc org.apache.tomcat tomcat-jdbc 8.5.72 mysql mysql-connector-java 3.1.2, single data source

Application.yml configuration:

Spring: datasource: tomcat-pool: name: Tomcat connection pool url: jdbc:mysql://10.40.9.11:3306/mydb?useUnicode=true&characterEncoding=UTF-8 driver-class-name: com.mysql.cj.jdbc.Driver username: root password: 123456 test-while-idle: true test-on-borrow: true validation-query: select 1 test-on-return: false validation-interval: 30000 time -between-eviction-runs-millis: 30000 max-active: 100 initial-size: 10 max-wait: 10000 remove-abandoned-timeout: 60 min-evictable-idle-time-millis: 30000 min-idle: 10 log-abandoned: true remove-abandoned: true jdbc-interceptors: org.apache.tomcat.jdbc.pool.interceptor.ConnectionState Org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer

Data source configuration class:

Package com.abc.demo.config;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.jdbc.DataSourceBuilder;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.sql.DataSource @ Configurationpublic class DataSourceConfig {@ Bean ("dataSource") @ ConfigurationProperties (prefix = "spring.datasource.tomcat-pool") public DataSource dataSource () {return DataSourceBuilder.create () .type (org.apache.tomcat.jdbc.pool.DataSource.class) .build ();}}

Use:

@ Autowiredprivate DataSource dataSource;3.1.3, multiple data sources

Application.yml configuration:

Spring: datasource: tomcat-pool: db1: name: Tomcat connection pool url: jdbc:mysql://10.40.9.11:3306/mydb?useUnicode=true&characterEncoding=UTF-8 driver-class-name: com.mysql.cj.jdbc.Driver username: root password: 123456 test-while-idle: true test-on-borrow: true validation-query: select 1 test-on -return: false validation-interval: 30000 time-between-eviction-runs-millis: 30000 max-active: 100 initial-size: 10 max-wait: 10000 remove-abandoned-timeout: 60 min-evictable-idle-time-millis: 30000 min-idle: 10 log-abandoned: true remove-abandoned: true jdbc-interceptors: org.apache.tomcat.jdbc.pool.interceptor.ConnectionState Org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer db2: name: Tomcat connection pool url: jdbc:mysql://10.110.74.187:3306/egmp?useUnicode=true&characterEncoding=UTF-8 driver-class-name: com.mysql.cj.jdbc.Driver username: root password: InsYR0ot187! Test-while-idle: true test-on-borrow: true validation-query: select 1 test-on-return: false validation-interval: 30000 time-between-eviction-runs-millis: 30000 max-active: 100 initial-size: 10 max-wait: 10000 remove-abandoned-timeout: 60 min-evictable-idle-time-millis: 30000 min-idle: 10 log-abandoned : true remove-abandoned: true jdbc-interceptors: org.apache.tomcat.jdbc.pool.interceptor.ConnectionState Org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer

Data source configuration class:

Package com.abc.demo.config;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.jdbc.DataSourceBuilder;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.sql.DataSource @ Configurationpublic class DataSourceConfig {@ Bean ("dataSource1") @ ConfigurationProperties (prefix = "spring.datasource.tomcat-pool.db1") public DataSource dataSource1 () {return DataSourceBuilder.create () .type (org.apache.tomcat.jdbc.pool.DataSource.class) .build () } @ Bean ("dataSource2") @ ConfigurationProperties (prefix = "spring.datasource.tomcat-pool.db2") public DataSource dataSource2 () {return DataSourceBuilder.create () .type (org.apache.tomcat.jdbc.pool.DataSource.class) .build ();}}

Use:

@ Autowired@Qualifier ("dataSource1") private DataSource dataSource1;@Autowired@Qualifier ("dataSource2") private DataSource dataSource2; so far, the study on "how to use Java database connection pool Tomcat" is over. I hope you can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.