I was developing an application using Spring JPA and Spring JDBC, and I was wondering if there was a difference in the timing of creating DB connection pooling. JPA seems to secure the connection first because an exception will occur if the connection information is incorrect or the DB server cannot be accessed when the application is started. On the other hand, JDBC will start the app even if it cannot connect to the DB. I get an exception when accessing the DB and notice the problem. I'm just convinced that the connection pool is created when the app starts, so I wasn't convinced by the behavior of JDBC. Actually, I was worried that the connection pool was not created by JDBC due to improper settings, so I decided to check it.
At the next timing, check the number of DB connections to determine if a connection pool has been created.
Since I used postgreSQL for DB, check the number of connections with the following command.
sample=# select count(*) from pg_stat_activity;
The number of connections before and after launching the app is the same, and after accessing the DB, the number of connections has increased by 10. My belief that "connection pools are created when the app starts" was wrong.
--Before launching the app
sample=# select count(*) from pg_stat_activity;
count
-------
2
(1 row)
--After launching the app
sample=# select count(*) from pg_stat_activity;
count
-------
2
(1 row)
--After DB access
sample=# select count(*) from pg_stat_activity;
count
-------
12
(1 row)
Create a web app using Spring Initializer. In Controller, JDBC is referencing the DB.
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
</dependency>
<dependency>
<groupId>nz.net.ultraq.thymeleaf</groupId>
<artifactId>thymeleaf-layout-dialect</artifactId>
</dependency>
<dependency>
<groupId>org.thymeleaf.extras</groupId>
<artifactId>thymeleaf-extras-java8time</artifactId>
</dependency>
</dependencies>
application.properties
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/sample
spring.datasource.username=admin
spring.datasource.password=admin
spring.datasource.sql-script-encoding=UTF-8
package com.example.jdbc.entity;
import lombok.Data;
@Data
public class Users {
private String username;
private String encodedPassword;
private Boolean systemUserFlag;
private Boolean adminUserFlag;
}
package com.example.jdbc.repository;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;
import com.example.jdbc.entity.Users;
@Component
public class UsersRepository {
private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
public UsersRepository(DataSource dataSource) {
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public List<Users> searchAll() {
List<String> sqlLines = new ArrayList<>();
sqlLines.add("SELECT username, encoded_password, system_user_flag, admin_user_flag FROM universe.users");
String sql = String.join(" ", sqlLines);
MapSqlParameterSource paramMap = new MapSqlParameterSource();
namedParameterJdbcTemplate.queryForRowSet(sql, paramMap);
SqlRowSet ret = namedParameterJdbcTemplate.queryForRowSet(sql, paramMap);
List<Users> list = new ArrayList<>();
while (ret.next()) {
Users users = new Users();
users.setUsername(ret.getString("username"));
users.setEncodedPassword(ret.getString("encoded_password"));
users.setSystemUserFlag(ret.getBoolean("system_user_flag"));
users.setAdminUserFlag(ret.getBoolean("admin_user_flag"));
list.add(users);
}
return list;
}
}
package com.example.jdbc.controller;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import com.example.jdbc.entity.Users;
import com.example.jdbc.repository.UsersRepository;
@Controller
@RequestMapping("sample")
public class SampleController {
private Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
private UsersRepository userRepository;
@RequestMapping(value = "")
public ModelAndView index(@RequestParam String name, ModelAndView mov) {
List<Users> usersList = userRepository.searchAll();
logger.info(usersList.toString());
mov.addObject("name", name);
mov.setViewName("/sample/index");
return mov;
}
}
src/main/resources/templates/sample/index.html
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Sample</title>
</head>
<body>
<div th:text="${'Hello,' + name}"></div>
</body>
</html>
```
References
+ [Check the current number of connections with Postgres](http://adempiere-devnote-jpn.blogspot.com/2017/07/postgres-checkconnections.html)