How to manage database connection pool in spring jpa?

Question

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:

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?

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.

score 20 · Accepted Answer · answered Jul 30 '16 at 10:40

20

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.

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).

Some more doc here

answered Jul 30 '16 at 10:40

guido

18,864
6
70
95

2

the problem is that I am using jpa repository name convention to read/write database. How can I get sqlconnection instance? – Joey Yi Zhao Jul 30 '16 at 11:17
1

Did you get an answer to this question? – Kay Sep 13 '18 at 22:17

score 14 · Answer 2 · answered Jul 30 '16 at 10:57

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

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

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
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 ;
}

where can I find all these properties explanation? I only found declaration but no detail explain. — Joey Yi Zhao, Aug 01 '16 at 03:39
You have to dig in the API https://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/DataSource.html — Evgeni Dimitrov, Aug 01 '16 at 05:30
@Evgeni Hi. I have been trying to find a connection pooling mechansim for my mongoDB access. There are no straight forward implementations that I know f=of and I cannot find a sample to do that. Currently I used Mongorepositories but I am afraid that in prod that is not a right way to handle? Could you let me know of any ? — vijayakumarpsg587, Dec 04 '18 at 11:23
@Joey587 I'm not an Mongo expert and ideally you should ask a separate question about this. Nevertheless take a look at this: https://stackoverflow.com/questions/50698222/connection-pooling-in-spring-boot-and-mongo-db — Evgeni Dimitrov, Dec 04 '18 at 12:07
@EvgeniDimitrov Thank you. I did look at it before but it did not say anything about the datasource. I will post a seperate question though. Thank you — vijayakumarpsg587, Dec 04 '18 at 13:36

How to manage database connection pool in spring jpa?

2 Answers2

Linked