Spring Boot JDBC Examples

Back to: Spring Boot Tutorials

Spring Boot JDBC with Examples

In this article, I am going to discuss Spring Boot JDBC with Examples. Please read our previous article where we discussed Spring Boot JPA with Examples.

What is JDBC?

JDBC (Java Database Connectivity) is a technology that allows Java applications to connect to and interact with a database. It provides a standard API for accessing relational databases.

Spring Boot JDBC provides starters and libraries to make it easy for an application to connect to a database using JDBC. Database-related beans such as DataSource, JdbcTemplate, and NamedParameterJdbcTemplate are automatically configured using @Autowired annotation. This makes it easy to use these beans in an application without having to manually configure them.

The DataSource bean provides a connection to the database. It is responsible for managing the connection and providing access to the database. The JdbcTemplate and NamedParameterJdbcTemplate beans provide convenient methods for executing SQL queries and updating the database.

Other properties, such as the database address, username, and password, are configured in the application.properties file. This file contains configuration information for the application and is used by Spring Boot to automatically configure various components. This is described using the following diagrams:

JDBC Connection Pooling

JDBC connection pooling is a technique that manages multiple requests for database connections. It does this by reusing connections and maintaining a cache of database connections called a connection pool. This pool is maintained by a connection pooling module that sits on top of any standard JDBC driver product. This mechanism helps to improve the efficiency and performance of database access.

What is HikariCP?

In Spring Boot, the default connection pool is HikariCP. This JDBC DataSource implementation provides a connection pooling mechanism with enterprise-grade features and improved performance. If HikariCP is present on the classpath, Spring Boot will automatically configure it. If it is not found, Spring Boot will look for the Tomcat JDBC Connection Pool and use it if it is present on the classpath. If neither of these options is available, Spring Boot will use Apache Commons DBCP2 as the JDBC connection pool.

It is possible to manually configure a connection pool to use the default one. To use the Tomcat JDBC connection pool instead of HikariCP, we exclude the HikariCP dependency and add the tomcat-jdbc dependency.

Spring Boot JDBC vs Spring JDBC

Spring Boot JDBC:

1. Only one dependency is required.
2. Automatically configures required beans.
3. Automatically registers beans.
4. Automatically executes database initialization scripts

Spring JDBC:

1. Multiple dependencies are required.
2. Need to create a bean either using XML or javaconfig.
3. The Template beans must be manually registered.
4. If any database initialization scripts are present, the configuration file needs to be modified

JDBC vs Hibernate

1. In JDBC, the programmer needs to handle creating and closing connections but in Hibernate, the run time system takes care of the same.
2. JDBC does not support lazy loading whereas hibernate does
3. It does not support associations (the connection between two separate classes) whereas hibernate does.

Spring Boot JDBC Example

So far, we learned about JDBC in theory. Now, let us learn how to implement it. For this exercise, we will use MySQL.

Step 1: Download, install, and set up MySQL. You may use MySQL Workbench or MySQL shell. Here we will use MySQL shell:

Step 2: Create a database using the CREATE DATABASE command:

Check that the database has been created:

Step 3: Set the newly created springbootdb database to the current database:

Step 4: Create a table called User:

Step 5: Create a new project using Spring Initializr in VS Code. Include the following dependencies:

• Spring Web
• Spring Data JDBC
• JDBC API
• MySQL Driver

Ensure that the dependencies are installed:

Step 6: Modify the application.properties file to connect to the database:

Line 1: Set the data source to the MySQL server.

Line 2: Set the username to log in to the MySQL server.

Line 3: Set the password to log in to the MySQL server.

Step 7: Create a class called SpringBootJdbcController.java in the src/main/java/com/dotnet/jdbc directory. This class is a controller class that will be used to handle HTTP requests.

Step 8: Modify the newly created SpringBootJdbcController.java as follows:

We have done the following changes:

• Imported the required packages.
• Added the @RestController annotation to the class. This marks the class as a REST controller.
• Added a function that inserts data into the database. This shall execute whenever a client is on the http://localhost:8080/insert webpage.

Step 9: Compile and execute the application. Ensure compilation is successful:

Step 10: Ensure that the User table is empty:

Step 11: Open the browser and enter the URL http://localhost:8080/insert into the address bar. This shows the following output:

Step 12: Check to see if the installation is successful:

Congratulations! You now know how to develop an application in Spring Boot using JDBC.

The Complete Example Code

JdbcApplication.java

package com.dotnet.jdbc;

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

@SpringBootApplication
public class JdbcApplication {

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

SpringBootJdbcController.java

package com.dotnet.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class SpringBootJdbcController
{
    @Autowired
    JdbcTemplate jdbc;

    @RequestMapping(value = "/insert")
    public String insert()
    {
        jdbc.execute("INSERT INTO User(name,email) VALUES ('User 1','user1@company.com')");
        return "Data Insertion Successful!";
    }
}

In the next article, I am going to discuss Spring Boot Caching. Here, in this article, I try to explain Spring Boot JDBC with Examples. I hope you enjoy this Spring Boot JDBC with Examples article.