SpringBoot JDBC

Spring Boot JDBC provides launchers and libraries to connect the application with JDBC connections.

In Spring Boot JDBC, Beans related to the database (such as DataSource, JdbcTemplate and NamedParameterJdbcTemplate ) will be automatically configured and created during startup. If we want to use them, we can automatically wire these classes. For example:

@Autowired
JdbcTemplate　jdbcTemplate;
@Autowired
private　NamedParameterJdbcTemplate　jdbcTemplate;

in application.properties file, we configure DataSource and connection pool. Spring Boot defaults to tomcat pool.

JDBC connection pool

JDBC connection poolis a management multiplemechanism for database connection requests. In other words, it promotes connection reuse, that is, the memory cache of database connections, called connection pool. The connection pool module maintains it as a layer above any standard JDBC driver product.

It can improve the speed of data access and reduce the number of database connections in the application. It can also improve the performance of the application. The connection pool performs the following tasks:

manage available connections allocate a new connection close connection

In the figure above, there are clients, one connection pool(with four available connections) and 1data source.

In the first figure, three clients connected to different connections, and the connections are available. In the second figure, the client3The connection has been disconnected, and the connection is available.

When the client completes its work, it will release the connection, and the connection will be available for other clients.

HikariCP

SpringBoot 2The default connection pool in HikariCP It provides enterprise-ready features and better performance. HikariCP is a JDBC data source implementation that provides a connection pool mechanism.

If HikariCP exists in the classpath, Spring Boot will automatically configure it. if HikariCP is not found in the classpath. Tomcat JDBC connection pool. if it is in the classpath of Spring Boot. If the above two options are not available, Spring Boot will choose Apache Commons DBCP2 as the JDBC connection pool.

If we do not want to use the default connection pool, we can also manually configure the connection pool. Suppose we want to use the Tomcat JDBC connection pool instead of HikariCP. We will exclude HikariCP dependency, and add tomcat-jdbc dependencies, as shown below.

<dependency>
<groupId>org.springframework.boot/<groupId>
<artifactId>spring-boot-starter-data-jpa/　artifactId　>
<exclusions>
<exclusion>
<groupId>com.zaxxer/<groupId>
<artifactId>HikariCP/　artifactId　>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.tomcat</<groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>9.0.10</version>
</dependency>
<dependency>
<groupId>com.h2database</<groupId>
<artifactId>h2</artifactId>
<version>1.4.9</version>
<socpe>runtime</scoope>
</dependency>

The above methods allow us to use the Tomcat connection pool without having to write @Configuration class and define it programmatically DataSource bean.

On the other hand, we can also skip the connection pool scanning algorithm used by Spring Boot. We can do this by adding the property spring.datasource.type to explicitly specify the connection pool data source.

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

We have established the Tomcat connection pool. Now, we will application.properties Add some properties to optimize its performance and meet certain specific requirements.

spring.datasource.tomcat.initial-size=20
spring.datasource.tomcat.max-wait=25000
spring.datasource.tomcat.max-active=70
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.min-idle=9
spring.datasource.tomcat.default-auto-commit=true

To connect to the MySQL database, we need to include the JDBC driver in the application's classpath:

<!--　MySQL JDBC driver　-->
<dependency>
<groupId>mysql</<groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

After that, in application.properties defined in the file datasoure properties.

If you are using MySQL, please use the following propertiesdatabase:

spring.datasource.url=jdbc:mysql://192.168.1.4:3306/test
spring.datasource.username=w3codebox
spring.datasource.password=password

If you are using Oracle database, please use the following properties:

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=system
spring.datasource.password=Password123

Why use Spring Boot JDBC?

The implementation of Spring JDBC and Spring Boot JDBC is the same. Compared with Spring JDBC, Spring Boot JBDC has the following advantages:

JDBC vs. Hibernate

In the next part, we will learn about the connectivity of MySQL in Spring Boot applications.