Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is JDBC connection pooling?
3.
What is HikariCP?
4.
How do we connect an application to JDBC with spring boot?
4.1.
Create a database
4.2.
Create a table into MySQL
4.3.
Creating a Spring Boot Project
4.4.
Enter the project name and other project-related information.
4.5.
Provide dependencies
4.6.
Run the application 
5.
Advantages of spring boot JDBC over Spring JDBC
6.
Difference between JDBC and hibernate
7.
Frequently Asked Questions
8.
Key Takeaways
Last Updated: Mar 27, 2024

Spring Boot JDBC

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

JDBC ( Java Database Connectivity ) is a programming interface for the Java programming language that outlines how a client can access a database. It is a Java-based data access technology used to link Java databases.

For connecting the application with JDBC spring boot, JDBC provides us with starters and libraries. Database-related beans such as DataSource, JdbcTemplate, and NamedParameterJdbcTemplate are auto-configured and produced during the Spring Boot JDBC startup. and if we want to use it, we can autowire following classes-

@Autowired  
JdbcTemplate jdbcTemplate;  
@Autowired  
private NamedParameterJdbcTemplate jdbcTemplate;  

We will configure DataSource and connection pooling present in the application.properties. By default, spring boot chooses tomcat pooling.

What is JDBC connection pooling?

Multiple database connection requests are managed by a mechanism called JDBC connection pooling. In other words, it promotes connection reuse by storing database connections in a memory cache known as a connection pool. It is maintained as a layer on top of any normal JDBC driver product by a connection pooling module.

Source - 1 Introduction to UCP 

There are three clients in the above figure with a connection pool and a datasource. Three customers are linked with different connections in the first figure, and a connection is accessible. Client 3 has disconnected in the second figure, although the connection is still active. When a client completes his task, the connection is released, and that connection is made accessible to other clients.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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


}

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

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

Spring Boot JDBC

Spring JDBC

Spring-boot-starter-JDBC dependency is there, which is required. Multiple dependencies like spring-JDBC and spring-context need to be configured in Spring JDBC.
Datasource bean is automatically configured if not maintained explicitly, and we can set property called spring.datasource.initialize to false if we don't want to use beans. Creating a database bean in spring JDBC is necessary either by using XML or javaconfig.
Spring Boot automatically registers beans, so we do not need to register template beans. Some template beans have to be registered, such as PlatformTransactionManager, JDBCTemplate, NamedParameterJdbcTemplate.
Any DB initialization scripts that are placed in the.sql file are run automatically. If any database initialization scripts, such as table dropping or creation are created in the SQL file, this information must be explicitly included in the settings.

 

Difference between JDBC and hibernate

JDBC

Hibernate

This is a technology. It is an ORM framework.
Here the user itself is responsible for closing and creating connections. Here the run time system is responsible for closing and creating connections.
JDBC does not support lazy loading. The lazy loading, which offers good performance, is supported by hibernate.
The connection between two separate classes, also called association, is not supported by JDBC. Association is supported by 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. You can also consider our Spring Boot Course to give your career an edge over others.

Live masterclass