Spring Boot JDBC

What is HikariCP?

It is a default connection pool in spring boot 2. It has enterprise-ready features and performs better. HikariCP is a JDBC DataSource implementation that includes a connection pooling technique.

• The Spring Boot automatically configures HikariCP if it is present in the classpath.
• If HickariCP is not present in the classpath, Spring Boot looks for the Tomcat JDBC Connection Pool.
• As the JDBC connection pool, Spring Boot chooses Apache Commons DBCP2 if both the above options are not available.

How do we connect an application to JDBC with spring boot?

Now, we're building an application that interacts with a Mysql database. Here are the instructions for creating and configuring JDBC with Spring Boot below-

Create a database

create database springbootdb  

Create a table into MySQL

create table user(id int UNSIGNED primary key not null auto_increment, name varchar(100), email varchar(100));

Creating a Spring Boot Project

Enter the project name and other project-related information.

Provide dependencies

Then we will create the following files in our spring boot project-

We will configure the database into the application.properties file.

• application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/springbootdb  
spring.datasource.username=root  
spring.datasource.password=mysql  
spring.jpa.hibernate.ddl-auto=create-drop  

• SpringBootJdbcApplication.java

package com.example.springbootjdbc;


import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication
public class SpringBootJdbcApplication {


   public static void main(String[] args) {
       SpringApplication.run(SpringBootJdbcApplication.class, args);
   }


}

You can also try this code with Online Java Compiler

Run Code

For handling HTTP requests, create a controller SpringBootJdbcController.java

package com.example.springbootjdbc;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class SpringBootJdbcController {
   @Autowired
   JdbcTemplate jdbc;
   @RequestMapping("/insert")
   public String index(){
       jdbc.execute("insert into user(name,email)values('Coding Ninjas','codingNinja@ninja.com')");
       return"data inserted Successfully";
   }
}

You can also try this code with Online Java Compiler

Run Code

Run the application 

As a Java application, we will run the SpringBootJdbcApplication.java file.

Upon opening our web browser or going to the following link, localhost:8080/insert, we will receive the following output.

On clicking on this link, it will ensure that the data is inserted successfully. We will check if the output is correct by checking the MySQL table.

Congratulations, the application is working correctly, and now we can try other database operations too.

Advantages of spring boot JDBC over Spring JDBC

Difference between JDBC and hibernate

Frequently Asked Questions

1. What are the tasks performed by connection pooling?

• It improves data access performance and minimizes the number of database connections an application requires.
• It Allocates a new connection.
• It enhances the performance of an application.
• It closes the connections.

2. How do we change the dependencies to use tomcat-JDBC instead of HickariCP?

In the pom.xml file, we change to remove the HikariCP dependency and add the tomcat-JDBC dependency as seen 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 solution described above allows us to use Tomcat's connection pool without directly constructing a @Configuration class or declaring a DataSource bean.

3. How to specify a connection pooling datasource if we want to skip the connection pool scanning algorithm that Spring Boot uses?

By adding in the application.properties file the property spring.datasource. The type we can specify is the connection pooling datasource.

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

4. Name some properties to optimise tomcat performance and suit some specific requirements.

Here are some properties which we will add in application.properties file.

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

5. How do we connect our application to the MySQL database?

In the application classpath we use JDBC driver-

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

After adding classpath, we add some datasource properties to the application.properties file which are -

• If you are using the Mysql database

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

• If you are using the Oracle database

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

Key Takeaways

In this blog, we learned about Spring Boot JDBC. Don't come to a halt here. Check out our Top 8 Java Projects on GitHub | Coding Ninjas Blog.