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.
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"?> xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <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) Classpublic 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 // Classpublic class ConfigDataSource { @Bean public static DataSource source() { DataSourceBuilder<?> dSB = DataSourceBuilder.create(); dSB.driverClassName("com.mysql.jdbc.Driver"); // MySQL specific url with database name // 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@Datapublic 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:
- On adding the above dependency, the login page gets activated by default.
- The default username is – ‘user’
- 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 // Classpublic 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> <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> <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.