In this article, we will learn how to use JDBC and Spring Security to implement user authentication.

We will use an embedded H2 database to store the user credentials and authenticate the username and password entered in the login form against the available user records in the H2 database.

Version details

  • Java 8
  • Spring boot version 2.4.0
  • Maven build tool.
  • H2 in-memory database.
  • Spring security.

Create a application

Create a Spring boot application with the required dependencies.

Add the below maven dependencies to the application’s pom.xml file.

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<scope>runtime</scope>
</dependency>

We have added security, web, and JDBC spring boot starter dependencies to our application.

We have also added the H2 dependency that provides the required in-memory H2 database.

Setup the database configurations

We need to set up the required tables to store the user details in the H2 database.

We create the required tables for the Spring Security and insert a few user details during the application startup.

Creating the tables and inserting some users

Create a schema.sql file under the src/main/resources/ directory of the application and add the below DDL scripts.

The script will create the Users and Authorities tables in the H2 database that contains user information.

CREATE TABLE IF NOT EXISTS USERS (
 `id` INT NOT NULL AUTO_INCREMENT,
 `username` VARCHAR(45) NOT NULL,
 `password` VARCHAR(45) NOT NULL,
 `enabled` INT NOT NULL,
 PRIMARY KEY(`id`));
 
 CREATE TABLE IF NOT EXISTS AUTHORITIES (
  `id` INT NOt NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `authority` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`id`));

Create a data.sql file under /src/main/resources directory and add the below content.

INSERT INTO authorities VALUES(NULL, 'arun', 'read');
INSERT INTO users VALUES(NULL, 'arun', '12345', '1');

INSERT INTO authorities VALUES(NULL, 'sara', 'read');
INSERT INTO users VALUES(NULL, 'sara', 'abcd', '1');

INSERT INTO authorities VALUES(NULL, 'john', 'read');
INSERT INTO users VALUES(NULL, 'john', '1111', '1');

We are adding some user and authority records and adding them to the created tables.

Add connection configurations

Add the below configuration properties to the application.properties under the directory src/main/resources directory.

spring.datasource.url=jdbc:h2:~/spring;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.data-username=sa
spring.datasource.data-password=
spring.datasource.initialization-mode=always

Setting up security configurations

Create a Spring configuration class called AuthConfig.java, as shown below.

@Configuration
public class AuthConfig {

	@Bean
	public PasswordEncoder passwordEncoder() {
		return NoOpPasswordEncoder.getInstance();
	}

	@Bean
	public UserDetailsService userDetailsService(DataSource dataSource) {
		String userByUserNameQuery = "SELECT username, password, enabled from USERS where username =?";
		String authoritiesByUserQuery = "SELECT username, authority from AUTHORITIES where username =?";

		var userDetailsManager = new JdbcUserDetailsManager(dataSource);
		userDetailsManager.setUsersByUsernameQuery(userByUserNameQuery);
		userDetailsManager.setAuthoritiesByUsernameQuery(authoritiesByUserQuery);
		return userDetailsManager;
	}
}

We have defined a UserDetailsService bean. We have created an instance of the JdbcUserDetailsManager provided by the spring security and initialized it with the data source.

We have customized the select user and authorities queries passing our customized SQL queries.

The above implementation is a simple example that shows how simple it is to customize the configuration provided by Spring security.

We have also defined a password encoder bean, which is necessary as we have customized the UserDetailsService configuration(Do not use NoOpPasswordEncoder encoder in the production environment).

Add a controller

Create a HelloController.class and define a simple REST endpoint that returns a “hello” message.

@RestController
public class HelloController {
	
	@GetMapping("/")
	public String getMessage() {
		return "Hello!!";
	}
}

Testing the application

Run the Spring boot application. By default, the Tomcat server runs on http://localhost:8080

Spring security displays a login page, as shown below.

spring security jdbc example

Input the user credentials that we inserted in the tables and click on the Login button.

spring security jdbc example

Conclusion

In this post, we learned how to use spring security with JDBC and implement simple user authentication.

We also learned how Spring security provides built-in support for JDBC basic user authentication and how easy it is to customize the authentication configuration.

The example code is available on GitHub.

You may also be interested in