Skip to content
Related Articles

Related Articles

Spring Boot – JDBC

Improve Article
Save Article
  • Difficulty Level : Medium
  • Last Updated : 24 Feb, 2022
Improve Article
Save Article

Today most applications need a database for various reasons like storing user details – Further also used for authentication, to store products, orders information, etc. In such a situation, while working with Java applications, we use JDBC API.  JDBC (Java Database Connectivity) is a standard API (Application Programming Interface) to make DBMS (Database Management System) accessible to Java programs.

JDBC consists of two parts as depicted in below table as follows:

Parts

Description

JDBC interfaces

java.sql / javax.sql packages has classes/interfaces of JDBC API.

JDBC drivers

JDBC Driver allows java programs to interact with the database.

Spring Boot offers many ways to work with databases (e.g – JdbcTemplate) without the cumbersome effort that JDBC needs. You can use raw JDBC to manually configure the workings.

Spring Boot -  JDBC

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.

Maven - pom.xml

<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.

Maven - pom.xml

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

Project Structure – Maven

Implementation: 

A. pom.xml (Configuration)

XML




<?xml version="1.0" encoding="UTF-8"?>
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>sia</groupId>
    <artifactId>GFG</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>GFG</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
  
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
          
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
  
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
  
</project>


B. Boot of Spring application (GfgApplication.java)

Java




// Java Program to Illustrate Boot of Spring Application
  
package gfg;
  
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
  
// Annotation
@SpringBootApplication
  
// Application(Main) Class
public class GfgApplication {
  
    // Main driver method
    public static void main(String[] args)
    {
  
        SpringApplication.run(GfgApplication.class, args);
    }
}


C. 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

Method

Description

create()

Creates a new instance of DataSourceBuilder.

driverClassName(String driverClassName)

Specifies the driver class name which is to be used for building the datasource.

 url(String url)

Specifies the URL which is to be used for building the datasource.

username(String username)

Specifies the username which is to be used for building the datasource.

password(String password)

Specifies the password which is to be used for building the datasource.

build()

Returns a newly built DataSource instance.

This builder supports the following pooling Datasource implementations.

Name

Description

Hikari

com.zaxxer.hikari.HikariDataSource

Tomcat JDBC Pool

org.apache.tomcat.jdbc.pool.DataSource

Apache DBCP2

org.apache.commons.dbcp2.BasicDataSource

Oracle UCP

oracle.ucp.jdbc.PoolDataSourceImpl

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

Console

Example:

Java




// Java Program Illustrating Configuration of
// DataSourceConfiguration of DataSource
  
package gfg;
  
import javax.sql.DataSource;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
  
// Annotation
@Configuration
  
// Class
public class ConfigDataSource {
  
    @Bean public static DataSource source()
    {
  
        DataSourceBuilder<?> dSB
            = DataSourceBuilder.create();
        dSB.driverClassName("com.mysql.jdbc.Driver");
  
        // MySQL specific url with database name
        dSB.url("jdbc:mysql://localhost:3306/userdetails");
  
        // MySQL username credential
        dSB.username("user");
  
        // MySQL password credential
        dSB.password("password");
  
        return dSB.build();
    }
}


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>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

Example:

Java




// Java Program Illustrating User Credentials to
// be Stored in the Database
  
package geek.details;
  
import lombok.Data;
  
// Annotation for Getter/Setter methods
@Data
public class UserDetails {
  
    String user;
    String userName;
    String password;
}


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

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-security</artifactId>
</dependency>
        
<dependency>
    <groupId>org.springframework.security</groupId>
    <artifactId>spring-security-test</artifactId>
    <scope>test</scope>
</dependency>

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.

Console

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

Default login page

  • 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.security.crypto.bcrypt.BCryptPasswordEncoder
- public class BCryptPasswordEncoder extends java.lang.Object implements PasswordEncoder

Constructors of BCryptPasswordEncoder Class 

Constructor

Description

BCryptPasswordEncoder( ) Default constructor.
BCryptPasswordEncoder​( int strength ) Strength is the log rounds to use between 4 to 31. Default is 10.
BCryptPasswordEncoder​( int strength, SecureRandom random ) The secure random instance to be used.
BCryptPasswordEncoder​( BCryptVersion version ) Version of BCrypt – 2a, 2b, 2y.
BCryptPasswordEncoder​( BCryptVersion version, int strength ) Bcrypt versions / log rounds.
BCryptPasswordEncoder​( BCryptVersion version, int strength, SecureRandom random ) Bcrypt versions / log rounds / Secure Random instance.
BCryptPasswordEncoder​( BCryptVersion version, SecureRandom random ) Bcrypt versions /  Secure Random instance.

Methods of BCryptPasswordEncoder Class 

Method

Description

encode​(CharSequence rawPassword)

Encodes a raw password provided with SHA-1 or greater hash combined with 8-Byte or greater randomly generated salt value.

 matches​(CharSequence rawPassword, String encodedPassword)

Verifies the stored encoded password matches the submitted encoded raw password. Returns true if matched otherwise false.  

upgradeEncoding​(String encodedPassword)

Returns true if the encoded password should be encoded again for better security, else false. The default implementation always returns false.

Example:

Java




// Java Program Illustrating Utility class for Connecting
// and Querying the Database
  
package gfg;
  
import geek.details.UserDetails;
import java.security.SecureRandom;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
  
// Annotation to provide logging feature
@Slf4j
  
// Class
public class JDBC {
  
    public int insert(UserDetails user)
    {
        DataSource dataSource = null;
        Connection connection = null;
        PreparedStatement prepStatement = null;
  
        int result = 0;
        try {
  
            // Get the configured datasourse
            dataSource = ConfigDataSource.source();
            // Attempt for connection to MySQL
            connection = dataSource.getConnection();
            prepStatement = connection.prepareStatement(
                "insert into user (user,username,password) values (?,?,?)");
            prepStatement.setString(1, user.getUser());
            prepStatement.setString(2, user.getUserName());
  
            BCryptPasswordEncoder bCryptPasswordEncoder
                = new BCryptPasswordEncoder(
                    10, new SecureRandom());
            String encodedPassword
                = bCryptPasswordEncoder.encode(
                    user.getPassword());
  
            prepStatement.setString(3, encodedPassword);
            result = prepStatement.executeUpdate();
        }
        catch (SQLException e) {
            log.getName();
        }
  
        return result;
    }
}


F. Controller of Spring Application (JdbcController.java)

Java




// Java Program to Illustrate Controller of Spring Application
  
package gfg;
  
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
  
import geek.details.UserDetails;
  
@Controller
@RequestMapping("/jdbc")
public class JdbcController {
      
    @GetMapping
    public String get(Model model) {
          
      // Add object to be bound by user provided details
        model.addAttribute("obj", new UserDetails()); 
        return "template";
    }
      
    @PostMapping
    public String post(@ModelAttribute("obj") UserDetails user, Model model) {
          
        JDBC SQL = new JDBC();
        int result = SQL.insert(user);
        if(result == 1)
            model.addAttribute("message", "Successful JDBC connection and execution of SQL statement");
        else
            model.addAttribute("message", "Query not submitted!");
        return "Status";
    }
}


Templates(Thymeleaf)

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

HTML




<!DOCTYPE html>
      xmlns:th="http://www.thymeleaf.org">
 <head>
 <title>GFG</title>
 </head>
 <body>
 <h1>Register Geek</h1>
   
 <form method="POST" th:action="@{/jdbc}" th:object="${obj}">
   
 <label for="user">User: </label><br/>
 <input type="text" th:field="*{user}"/><br/>
   
 <label for="username">Username: </label><br/>
 <input type="text" th:field="*{userName}"/><br/>
   
 <label for="password">Password: </label><br/>
 <input type="password" th:field="*{password}"/>
   
 <input type="submit" value="Register"/>
   
 </form>
 </body>
</html>


Output:

Filling the User details 

B. Status.html

Display the message of JDBC operation

HTML




<!DOCTYPE html>
      xmlns:th="http://www.thymeleaf.org">
 <head>
 <title>GFG</title>
 </head>
 <body>
 <h1>STATUS</h1>
 <h2 th:text="${message}">
       <span>message will print here</span>
 </h2
 </body>
</html>


Output

Displaying the message

C. MySQL Database 

Output:

Retrieved the stored User details

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.


My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!