Introduction to Java Database Connection Pool Tomcat
- 2021-12-13 08:15:19
- OfStack
Directory 1, Configuration Parameter 1.1, Basic Configuration 1.2, System Attribute 1.3, General Parameter 1.4, Enhanced Parameter 2, Usage 2.1, Direct Usage 2.1. 1, Introduced Dependency 2.1. 2, Usage Example 2.2, Used as Resource Configuration in Tomcat 3.2, Used 3.1. 1 in SpringBoot, Introduced Dependency 3.1. 2, Single Data Source 3.1. 3, Multiple Data Sources
Foreword:
The Tomcat connection pool 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 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, Spring Boot 2.3. 12. RELEASE.
1. Configuration parameters
1.1. Basic Configuration
| 参数 | 默认值 | 描述 |
| factory | 必须，需要是 org.apache.tomcat.jdbc.pool.DataSourceFactory | |
| type | 应该是 javax.sql.DataSource 或 javax.sql.XADataSource |
1.2. System Properties
| 参数 | 默认值 | 描述 |
| org.apache.tomcat.jdbc.pool.onlyAttemptCurrentClassLoader | false | 是否只使用当前的类加载器(加载该连接池的类加载器)来加载动态类 |
1.3, 1 general parameters
These parameters and
DBCP
1, but some defaults are not 1.
| 参数 | 默认值 | 描述 |
| defaultAutoCommit | 驱动的默认值 | 是否自动提交 |
| defaultReadOnly | 驱动的默认值 | 是否只读 |
| defaultTransactionIsolation | 驱动的默认值 |
默认的事务隔离级别 |
| defaultCatalog |
默认的 catalog。(目录，类似于模式名，但比模式名更加抽象； |
|
| driverClassName | 驱动名称 | |
| url | 连接 url | |
| username | 用户名 | |
| password | 密码 | |
| maxActive | 100 | 最大活动连接数 |
| maxIdle | 100 | 最大空闲连接数 |
| minIdle | 10 | 最小空闲连接数 |
| initialSize | 10 | 初始连接数 |
| maxWait | 3000 | 从连接池获取连接，最大等待时间(秒) |
| testOnBorrow | false | 从连接池获取连接时，是否验证有效性；如果验证失败，则丢弃该连接。 |
| testOnConnect | true | 连接创建时，是否验证有效性 |
| testOnReturn | false | 连接返回连接池时，是否验证有效性 |
| testWhileIdle | false | 连接空闲时，是否验证有效性 |
| validationQuery | null |
连接校验的查询sql |
| validationQueryTimeout | -1 | 校验查询的超时时间(秒)；非正数表示不启用该特性。 |
| validatorClassName | null | 校验的类名，需实现 org.apache.tomcat.jdbc.pool.Validator 接口并包含1个无参构造函数。 |
| timeBetweenEvictionRunsMillis | 5000 | 校验空闲连接的时间周期(毫秒)，不能设为小于 1 秒，非正表示不验证 |
| minEvictableIdleTimeMillis | 60000 | 空闲连接至少多长时间(毫秒)后，才会被校验 |
| removeAbandoned | false | 是否删除泄露的连接 |
| removeAbandonedTimeout | 60 | 连接泄露的超时时间(秒) |
| logAbandoned | false | 连接删除时是否打印堆栈信息 |
| connectionProperties | null |
连接属性，格式为： [propertyName=property;]* |
1.4. Enhanced parameters
| 参数 | 默认值 | 描述 |
| initSQL | null | 连接创建时，执行的初始化 SQL |
| jdbcInterceptors | null |
jdbc 拦截器，需要继承 org.apache.tomcat.jdbc.pool.JdbcInterceptor；已存在的拦截器： |
| validationInterval | 3000 | 连接校验的最短间隔(毫秒) |
| jmxEnabled | true | 是否注册连接池到 JMX |
| fairQueue | true | 是否使用公平队列，如果为 true，获取连接时将按照 "先进先出" 的原则 |
| abandonWhenPercentageFull | 0 | 泄露连接达到 abandonWhenPercentageFull 比例才关闭这些连接，0 表示有泄露连接立马关闭 |
| maxAge | 0 | 连接最大存活时间；在从连接池获取连接和连接返回连接池时进行该项检测，如果 now - time-when-connected > maxAge，则关闭该连接；0 表示不进行该项检测。 |
| useEquals | true | 是否使用 String.equals 来判断 ProxyConnection 是否相等 |
| suspectTimeout | 0 | 和 removeAbandonedTimeout 类似，但该设置只是打印日志并不删除连接；大于 0 才生效。 |
| rollbackOnReturn | false | 连接在返回连接池时是否自动回滚事务。 |
| commitOnReturn | false | 连接在返回连接池时是否自动提交事务；如果 rollbackOnReturn==true 则忽略该参数。 |
| alternateUsernameAllowed | false | 从连接池获取连接时是否允许设置新的凭证。默认情况下，连接池会忽略 DataSource.getConnection(username,password) 的调用，直接返回1个已创建的连接；如果要使用不同的凭证来获取连接，即 DataSource.getConnection(username,password) 生效，可把该参数设为 true。 |
| dataSource | null | 设置数据源，连接池将从该数据源获取连接 |
| dataSourceJNDI | null | 数据源的 jndi |
| useDisposableConnectionFacade | true | 是否使用连接外观；设置为 true 可以防止连接关闭后的重复使用。 |
| logValidationErrors | false | 是否记录校验的错误 |
| propagateInterruptState | false | 是否传播线程中断状态 |
| ignoreExceptionOnPreLoad | false | 是否忽略创建连接时的错误 |
| useStatementFacade | true | 如果希望使用包装 statement，以便在设置了 statement 代理时，在已关闭的 statement 上调用 equals() and hashCode() 方法，需将此设置为 true。 |
For detailed explanation, please refer to the official website document: https://tomcat.apache.org/tomcat-8. 5-doc/jdbc-pool.html
2. Use
2.1. Direct use
2.1. 1, Introducing dependencies
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>8.5.72</version>
</dependency>2.1. 2. Use examples
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 actual use 1 Generally, the data source is initialized when the application starts, and the application obtains the 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. Allocate as a resource in Tomcat
First, copy the driver package of the corresponding database to
Tomcat
Or applied
lib
Directory, and then in the
content.xml
Configure resources in,
content. xml can be located in the following location:
conf/context.xml
For all applications
conf/Catalina/localhost
For a single application, it is suitable for
Tomcat
External deployment application
{应用}/META-INFcontext.xml
For a single application
Examples of configuring resources are as follows:
<Resource name="jdbc/testDb"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="false"
validationQuery="SELECT 1"
validationInterval="30000"
timeBetweenEvictionRunsMillis="30000"
maxActive="20"
maxIdle="20"
minIdle="5"
maxWait="10000"
initialSize="5"
removeAbandonedTimeout="60"
removeAbandoned="true"
logAbandoned="true"
minEvictableIdleTimeMillis="30000"
jmxEnabled="true"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
username="root"
password="123456"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://10.40.9.11:3306/mydb?useUnicode=true&characterEncoding=UTF-8" />
It can be passed through
jndi
To find this resource, the search method is demonstrated here through jsp:
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="UTF-8"%>
<%@ page import="javax.naming.Context,
javax.naming.InitialContext,
javax.sql.DataSource,
java.sql.*"%>
<%
Connection connection = null;
try {
InitialContext initialContext = new InitialContext();
Context context = (Context) initialContext.lookup("java:comp/env");
DataSource dataSource = (DataSource)context.lookup("jdbc/testDb");
connection = dataSource.getConnection();
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("select version()");
if (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
%>
<html>
<body>
<h3> Pass jndi Find the data source and get the version information of the database </h3>
</body>
</html>
3.2. Use in SpringBoot
3.1. 1, Introducing dependencies
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.12.RELEASE</version>
<relativePath />
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>8.5.72</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>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;
@Configuration
public 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:
@Autowired
private 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;
@Configuration
public 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:
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 actual use 1 Generally, the data source is initialized when the application starts, and the application obtains the 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();
}
}
}
}