Oracle Connection Pooling With Spring – 使用Spring的Oracle连接池

最后修改: 2020年 4月 19日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

Oracle is one of the most popular databases in large production environments. So, as Spring developers, it’s very common to have to work with these databases.

Oracle是大型生产环境中最受欢迎的数据库之一。因此，作为Spring的开发者，必须与这些数据库合作是非常普遍的。

In this tutorial, we’re going to talk about how we can make this integration.

在本教程中，我们将讨论如何进行这种整合。

2. The Database

2.数据库

The first thing we need is, of course, the database. If we don’t have one installed, we can get and install any of the databases available on the Oracle Database Software Downloads. But in case we don’t want to do any installation, we can also build any of the Oracle database images for Docker.

我们需要的第一件事当然是数据库。如果我们没有安装数据库，我们可以获得并安装Oracle数据库软件下载上的任何数据库。但如果我们不想做任何安装，我们也可以建立任何一个Oracle数据库镜像，用于Docker。

In this case, we’re going to use an Oracle Database 12c Release 2 (12.2.0.2) Standard Edition Docker image. Consequently, this keeps us from having to install new software on our computer.

在这种情况下，我们将使用Oracle数据库12c第2版（12.2.0.2）标准版Docker镜像。因此，这使我们不必在我们的计算机上安装新的软件。

3. Connection Pooling

3.连接池

Now we have the database ready for incoming connections. Next, let’s learn some different ways to do connection pooling in Spring.

现在我们的数据库已经准备好了，可以接受外来的连接。接下来，我们来学习一些在Spring中做连接池的不同方法。

3.1. HikariCP

3.1.HikariCP

The easiest way for connection pooling with Spring is using autoconfiguration. The spring-boot-starter-jdbc dependency includes HikariCP as the preferred pooling data source. Therefore, if we take a look into our pom.xml we’ll see:

使用Spring进行连接池的最简单方法是使用自动配置。spring-boot-starter-jdbc依赖性包括HikariCP作为首选池数据源。因此，如果我们看一下我们的pom.xml，我们会看到。

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

The spring-boot-starter-data-jpa dependency includes the spring-boot-starter-jdbc dependency transitively for us.

spring-boot-starter-data-jpa依赖性包括spring-boot-starter-jdbc依赖性，为我们过渡。

Now we only have to add our configuration into the application.properties file:

现在我们只需要将我们的配置添加到application.properties文件中。

# OracleDB connection settings
spring.datasource.url=jdbc:oracle:thin:@//localhost:11521/ORCLPDB1
spring.datasource.username=books
spring.datasource.password=books
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# HikariCP settings
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.poolName=HikariPoolBooks

# JPA settings
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
spring.jpa.hibernate.use-new-id-generator-mappings=false
spring.jpa.hibernate.ddl-auto=create

As you can see, we have three different section configuration settings:

正如你所看到的，我们有三个不同的部分配置设置。

  • The OracleDB connection settings section is where we configured the JDBC connection properties as we always do
  • The HikariCP settings section is where we configure the HikariCP connection pooling. In case we need advanced configuration we should check the HikariCP configuration property list
  • The JPA settings section is some basic configuration for using Hibernate

That is all we need. It couldn’t be easier, could it?

这就是我们需要的一切。这再简单不过了，不是吗？

3.2. Tomcat and Commons DBCP2 Connection Pooling

3.2.Tomcat和Commons DBCP2连接池

Spring recommends HikariCP for its performance. On the other hand, it also supports Tomcat and Commons DBCP2 in Spring Boot autoconfigured applications.

Spring推荐HikariCP的性能。另一方面，它还支持Spring Boot自动配置的应用程序中的Tomcat和Commons DBCP2。

It tries to use the HikariCP. If it isn’t available, then tries to use the Tomcat pooling. If neither of those is available, then it tries to use Commons DBCP2.

它试图使用HikariCP。如果它不可用，那么就尝试使用Tomcat池。如果这两个都不可用，那么它就尝试使用Commons DBCP2。

We can also specify the connection pool to use. In that case, we just need to add a new property to our application.properties file:

我们还可以指定要使用的连接池。在这种情况下，我们只需要在我们的application.properties文件中添加一个新的属性。

spring.datasource.type=org.apache.tomcat.jdbc.pool.DataSource

If we need to configure specific settings, we have available their prefixes:

如果我们需要配置特定的设置，我们有可用的前缀。

  • spring.datasource.hikari.* for HikariCP configuration
  • spring.datasource.tomcat.* for Tomcat pooling configuration
  • spring.datasource.dbcp2.* for Commons DBC2 configuration

And, actually, we can set spring.datasource.type to any other DataSource implementation. It isn’t necessary to be any of the three mentioned above.

而且，实际上，我们可以将spring.datasource.type设置为任何其他DataSource实现。它不一定是上面提到的三个中的任何一个。

But in that case, we will just have a basic out-of-the-box configuration. There will be many cases where we will need some advanced configurations. Let’s see some of them.

但在这种情况下，我们将只有一个基本的开箱即用的配置。在许多情况下，我们将需要一些高级配置。让我们看看其中的一些。

3.3. Oracle Universal Connection Pooling

3.3.Oracle通用连接池

If we want to use advanced configurations, we can declare the UCP datasource and set the remaining properties in the application.properties file. As of version 21.1.0.0 of UCP, this is the easiest way of doing it.

如果我们想使用高级配置，我们可以在application.properties文件中声明UCP数据源并设置其余属性。从UCP的21.1.0.0版本来看，这是最简单的方法。

Oracle Universal Connection Pool (UCP) for JDBC provides a full-featured implementation for caching JDBC connections. It reuses the connections instead of creating new ones. It also gives us a set of properties for customizing pool behavior.

Oracle Universal Connection Pool (UCP) for JDBC提供了一个全功能的实现，用于缓存JDBC连接。它重用连接而不是创建新的连接。它还为我们提供了一套用于定制池行为的属性。

If we want to use UCP, we need to add the following Maven dependencies:

如果我们想使用UCP，我们需要添加以下Maven依赖项。

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.ha</groupId>
    <artifactId>ons</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ucp</artifactId>
</dependency>

Now we only have to add our configuration into the application.properties file:

现在我们只需要把我们的配置加入到application.properties文件中。

# UCP settings
spring.datasource.type=oracle.oracleucp.jdbc.UCPDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource 
spring.datasource.oracleucp.sql-for-validate-connection=select * from dual 
spring.datasource.oracleucp.connection-pool-name=UcpPoolBooks 
spring.datasource.oracleucp.initial-pool-size=5 
spring.datasource.oracleucp.min-pool-size=5 
spring.datasource.oracleucp.max-pool-size=10

In the above example, we’ve customized some pool properties:

在上面的例子中，我们定制了一些池的属性。

  • spring.datasource.oracleucp.initial-pool-size specifies the number of available connections created after the pool is initiated
  • spring.datasource.oracleucp.min-pool-size specifies the minimum number of available and borrowed connections that our pool is maintaining, and
  • spring.datasource.oracleucp.max-pool-size specifies the maximum number of available and borrowed connections that our pool is maintaining

If we need to add more configuration properties, we should check the UCPDataSource JavaDoc or the developer’s guide.

如果我们需要添加更多的配置属性，我们应该查看UCPDataSource JavaDoc或开发者指南。

4. Older Oracle Versions

4. 旧的Oracle版本

For versions prior to 11.2, like Oracle 9i or 10g, we should create an OracleDataSource instead of using Oracle’s Universal Connection Pooling.

对于11.2之前的版本，如Oracle 9i或10g，我们应该创建一个OracleDataSource而不是使用Oracle的通用连接池。

In our OracleDataSource instance, we turn on connection caching via setConnectionCachingEnabled:

在我们的OracleDataSource实例中，我们通过setConnectionCachingEnabled打开了连接缓存。

@Configuration
@Profile("oracle")
public class OracleConfiguration {
    @Bean
    public DataSource dataSource() throws SQLException {
        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser("books");
        dataSource.setPassword("books");
        dataSource.setURL("jdbc:oracle:thin:@//localhost:11521/ORCLPDB1");
        dataSource.setFastConnectionFailoverEnabled(true);
        dataSource.setImplicitCachingEnabled(true);
        dataSource.setConnectionCachingEnabled(true);
        return dataSource;
    }
}

In the above example, we were creating the OracleDataSource for connection pooling and configured some parameters. We can check all the configurable parameters on the OracleDataSource JavaDoc.

在上面的例子中，我们正在创建用于连接池的OracleDataSource，并配置了一些参数。我们可以在OracleDataSource JavaDoc上查看所有的可配置参数。

5. Conclusion

5.总结

Nowadays, configuring Oracle database connection pooling using Spring is a piece of cake.

现在，使用Spring配置Oracle数据库的连接池是小菜一碟。

We’ve seen how to do it just using autoconfiguration and programmatically. Even though Spring recommends the use of HikariCP, other options are available. We should be careful and choose the right implementation for our current needs.

我们已经看到了如何仅仅使用自动配置和编程的方式来做到这一点。尽管Spring推荐使用HikariCP，但也有其他选择。我们应该小心谨慎，选择适合我们当前需求的实现。

And, as always, the full example can be found over on GitHub.

而且，像往常一样，完整的例子可以在GitHub上找到。