如何在spring jpa中管理数据库连接池?

时间:2021-12-08 12:13:50

I am using spring-boot in my web application and use spring-jpa to read/write from/to my database. It works very well but I want to understand how to manage the database connections. Below is my properties configuration for database:

我在我的Web应用程序中使用spring-boot并使用spring-jpa来读取/写入我的数据库。它工作得很好,但我想了解如何管理数据库连接。以下是我的数据库属性配置:

spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8
spring.datasource.username=user
spring.datasource.password=pwd
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-active=500

I have set the maximum connections to 500. When a user makes a request on my spring application, a database connection will be opened for him. After finishing the request, will spring jpa close this connection? If not, when will it close the unused connections?

我已将最大连接数设置为500.当用户在我的spring应用程序上发出请求时,将为他打开数据库连接。完成请求后,春天jpa会关闭这个连接吗?如果没有,何时关闭未使用的连接?

I have read through the spring jpa reference document from http://docs.spring.io/spring-data/jpa/docs/current/reference/html/. But it doesn't mention anything about the connections.

我已经阅读了http://docs.spring.io/spring-data/jpa/docs/current/reference/html/上的spring jpa参考文档。但它没有提到关于连接的任何事情。

2 个解决方案

#1


12  

When using DB connection pooling, a call to sqlconnection.close() will not necessarily close the heavyweight connection to the database, instead most often will just release the connection as re-usable in the pool. That's why it is advisable to invoke the close() on connection as soon as possible when leveraging a client side connection pool.

使用数据库连接池时,对sqlconnection.close()的调用不一定会关闭与数据库的重量级连接,而是通常只会将连接释放为可在池中重复使用。这就是为什么建议在利用客户端连接池时尽快调用连接上的close()。

In your configuration, the pool will contain a maximum number of 500 connections ( it would be also good to configure maxIdle, minIdle, and minEvictableIdleTimeMillis to tune the number of ready-to-use connections and how often to release them when not used).

在您的配置中,池将包含最多500个连接(配置maxIdle,minIdle和minEvictableIdleTimeMillis以调整即用型连接的数量以及在不使用时释放它们的频率也是很好的)。

Some more doc here

这里有更多的文档

#2


11  

You have already found that you can configure this from application.properties You can find all the possible properties here.

您已经发现可以从application.properties配置它。您可以在此处找到所有可能的属性。

Notice that from Spring Boot 1.4 there are datasource properties for every datasource vendor that spring integrates with, out of the box. There is spring.datasource.dbcp.*,spring.datasource.tomcat.* and so on. See 1.4 docs

请注意,从Spring Boot 1.4开始,每个数据源供应商都有数据源属性,这些数据源供应商可以开箱即用地集成。有spring.datasource.dbcp。*,spring.datasource.tomcat。*等等。请参阅1.4文档

If that's not enought, and you need something very specific, you can declare the datasource bean yourself. Here is the example with Tomcat datasource:

如果没有,并且您需要非常具体的东西,您可以自己声明数据源bean。以下是Tomcat数据源的示例:

@Bean
public DataSource dataSource(){
     PoolProperties p = new PoolProperties();
          p.setUrl("jdbc:mysql://localhost:3306/mysql");
          p.setDriverClassName("com.mysql.jdbc.Driver");
          p.setUsername("root");
          p.setPassword("password");
          p.setJmxEnabled(true);
          p.setTestWhileIdle(false);
          p.setTestOnBorrow(true);
          p.setValidationQuery("SELECT 1");
          p.setTestOnReturn(false);
          p.setValidationInterval(30000);
          p.setTimeBetweenEvictionRunsMillis(30000);
          p.setMaxActive(100);
          p.setInitialSize(10);
          p.setMaxWait(10000);
          p.setRemoveAbandonedTimeout(60);
          p.setMinEvictableIdleTimeMillis(30000);
          p.setMinIdle(10);
          p.setLogAbandoned(true);
          p.setRemoveAbandoned(true);
          p.setJdbcInterceptors(
            "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
            "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
          DataSource datasource = new DataSource();
          datasource.setPoolProperties(p);
          return datasource ;
}

#1


12  

When using DB connection pooling, a call to sqlconnection.close() will not necessarily close the heavyweight connection to the database, instead most often will just release the connection as re-usable in the pool. That's why it is advisable to invoke the close() on connection as soon as possible when leveraging a client side connection pool.

使用数据库连接池时,对sqlconnection.close()的调用不一定会关闭与数据库的重量级连接,而是通常只会将连接释放为可在池中重复使用。这就是为什么建议在利用客户端连接池时尽快调用连接上的close()。

In your configuration, the pool will contain a maximum number of 500 connections ( it would be also good to configure maxIdle, minIdle, and minEvictableIdleTimeMillis to tune the number of ready-to-use connections and how often to release them when not used).

在您的配置中,池将包含最多500个连接(配置maxIdle,minIdle和minEvictableIdleTimeMillis以调整即用型连接的数量以及在不使用时释放它们的频率也是很好的)。

Some more doc here

这里有更多的文档

#2


11  

You have already found that you can configure this from application.properties You can find all the possible properties here.

您已经发现可以从application.properties配置它。您可以在此处找到所有可能的属性。

Notice that from Spring Boot 1.4 there are datasource properties for every datasource vendor that spring integrates with, out of the box. There is spring.datasource.dbcp.*,spring.datasource.tomcat.* and so on. See 1.4 docs

请注意,从Spring Boot 1.4开始,每个数据源供应商都有数据源属性,这些数据源供应商可以开箱即用地集成。有spring.datasource.dbcp。*,spring.datasource.tomcat。*等等。请参阅1.4文档

If that's not enought, and you need something very specific, you can declare the datasource bean yourself. Here is the example with Tomcat datasource:

如果没有,并且您需要非常具体的东西,您可以自己声明数据源bean。以下是Tomcat数据源的示例:

@Bean
public DataSource dataSource(){
     PoolProperties p = new PoolProperties();
          p.setUrl("jdbc:mysql://localhost:3306/mysql");
          p.setDriverClassName("com.mysql.jdbc.Driver");
          p.setUsername("root");
          p.setPassword("password");
          p.setJmxEnabled(true);
          p.setTestWhileIdle(false);
          p.setTestOnBorrow(true);
          p.setValidationQuery("SELECT 1");
          p.setTestOnReturn(false);
          p.setValidationInterval(30000);
          p.setTimeBetweenEvictionRunsMillis(30000);
          p.setMaxActive(100);
          p.setInitialSize(10);
          p.setMaxWait(10000);
          p.setRemoveAbandonedTimeout(60);
          p.setMinEvictableIdleTimeMillis(30000);
          p.setMinIdle(10);
          p.setLogAbandoned(true);
          p.setRemoveAbandoned(true);
          p.setJdbcInterceptors(
            "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
            "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
          DataSource datasource = new DataSource();
          datasource.setPoolProperties(p);
          return datasource ;
}