Spring Boot - JDBC

Spring Boot JDBC simplifies database interaction by providing ready-to-use configurations and reducing boilerplate code. It leverages auto-configuration to manage JDBC components, allowing developers to focus mainly on writing SQL queries and business logic.

• Uses auto-configuration to set up DataSource, JdbcTemplate, and other JDBC components automatically.
• Provides direct control over SQL queries, making it suitable for fine-tuned database operations.
• Spring Boot simplifies database access (e.g., JdbcTemplate) compared to manual setup in raw JDBC.
• Database connection and pooling are configured in application.properties / application.yml, while CRUD is handled using JdbcTemplate.

JDBC consists of two parts as depicted below:

• JDBC interfaces: java.sql/ javax.sql packages have classes/interfaces of JDBC API.
• JDBC drivers: JDBC Driver allows Java programs to interact with the database.

JDBC Connection Pooling

JDBC Connection Pooling is a technique that reuses database connections instead of creating a new one for every request, improving performance and reducing load on the database server.

• Maintains a pool of pre-created reusable connections
• Avoids creating a new connection for each client request
• Reduces overhead and improves application performance

👁 JDBC-Connection-Pool

How Connection Pooling Works

• A server administrator prepares a connection pool with a setoff connection and also a mediator object as a DataSource API to access the pool.
• An administrator stores DataSource object into JNDI (Java Naming Directory Interface).
• Application reads the DataSource from JNDI registry and asks for a connection from a pool.
• A DataSource object takes a connection from a pool and creates a proxy or logical connection to it and then sens the proxy connection to Java.
• When a Java program closes proxy connection, the DataSource will do real connection back to the pool.

To work with a database using Spring Boot, we need to add the following dependencies:

A. JDBC API

Database connectivity API specifies how the client connects and queries a database.

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

B. MySQL Driver

MySQL JDBC and R2DBC driver to work with the database.

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>

After this, we will add datasource properties in application.properties file:

For MySQL database:

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Project Structure

Implementation of an Application

1. pom.xml (Configuration File)

2. Main Class of Spring application (GfgApplication.java)

3. Configuration of DataSource (ConfigDataSource.java)

DataSourceBuilder<T> is a useful class to build a DataSource.

org.springframework.boot.jdbc.DataSourceBuilder<T>
public final class DataSourceBuilder<T extends DataSource> extends Object

Methods of DataSourceBuilder<T> Class

This builder supports the following pooling Datasource implementations.

Note: The first available pool implementation is used when no type has been explicitly set.

Example:

Note: Driver class name - 'com.mysql.jdbc.Driver' has been deprecated. It would not give error because the driver is automatically loaded and manual loading is not necessary. The new driver class name is 'com.mysql.cj.jdbc.Driver'.

D. User credentials to be stored in the database (UserDetails.java)

One can add the 'Lombok' library to skip Getter/Setter methods, construct No-Arguments constructor, the constructor for final fields, etc.

Maven-pom.xml dependency

Example:

E. Utility class for connecting and querying the database (JDBC.java)

Password encoding is a must for many security reasons. To use Spring Security, add the following dependency:

Maven - pom.xml

Pre-requisites are as follows: 

1. On adding the above dependency, the login page gets activated by default.
2. The default username is - 'user.'
3. The password is automatically generated by the Spring Security that gets displayed on the console after booting the application.

Note: Generated password becomes invalid for the next iteration/Running the application as the new password gets generated, but the username remains same.

• For encoding Spring offers many ways like the one - 'BCryptPasswordEncoder'.
• It is an implementation of PasswordEncoder which uses the BCrypt strong hashing algorithm.
• Version, Strength, and SecureRandom instances can be modified.

org.springframework.boot.jdbc.DataSourceBuilder<T>
public final class DataSourceBuilder<T extends DataSource> extends Object

Constructors of BCryptPasswordEncoder Class:

Methods of BCryptPasswordEncoder Class: 

Example:

F. Controller of Spring Application (JdbcController.java)

Templates (Thymeleaf)

A. template.html: Gets user data and binds it to UserDetails object.

Output:

B. Status.html

Display the message of JDBC operation.

Output in browser:

C. MySQL Database 

Output:

Note: Spring Boot offers many convenient ways of working with data, e.g. Spring Data JPA - which has default implementation of Hibernate. We can use them to make advantage of Java Persistence API (JPA) for object/relational mapping and to avoid cumbersome efforts.