Spring Security JdbcDaoImpl Example

By Arvind Rai, December 11, 2019

The Spring Security JdbcDaoImpl is the implementation class of UserDetailsService interface which is the core interface to load user-specific data. The JdbcDaoImpl retrieves the user details such as username, password, enabled flag, and authorities from a database using JDBC queries. To use JdbcDaoImpl in our application, we need to create JdbcDaoImpl bean and configure datasource. By default JdbcDaoImpl uses database tables with name users and authorities. We can also configure custom database tables to JdbcDaoImpl bean.
Here on this page we will create Spring Security examples with JdbcDaoImpl using JavaConfig as well as XML configuration. We will use HSQLDB as database. We will discuss here using default database schema as well as custom schema to configure JdbcDaoImpl in our application.

Technologies Used
JdbcDaoImpl with Default Schema
JdbcDaoImpl with Custom Schema
HSQLDB Database Schema
Maven Dependencies
JdbcDaoImpl Example with JavaConfig
JdbcDaoImpl Example with XML Configuration
Output
References
Download Source Code

Technologies Used

Find the technologies being used in our example.
1. Java 11
2. Spring 5.2.1.RELEASE
3. Spring Boot 2.2.1.RELEASE
4. HSQLDB 2.5.0
5. Tomcat 9
6. Maven 3.5.2

JdbcDaoImpl with Default Schema

Spring Security assumes two tables users and authorities as default schema with following columns.
1. users: username, password, enabled
2. authorities: username, authority

To configure JdbcDaoImpl, create a bean in security JavaConfig as following.

@Bean
public JdbcDaoImpl userDetailsService() {
	JdbcDaoImpl jdbcDaoImpl = new JdbcDaoImpl();
	jdbcDaoImpl.setDataSource(dataSource());
	return jdbcDaoImpl;
}

In case we are using XML configuration, we will configure JdbcDaoImpl as following.

<beans:bean id="userDetailsService" class="org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl">
	<beans:property name="dataSource" ref="dataSource" />
</beans:bean>

To know the SQL queries run by JdbcDaoImpl, we can access its following constants.
1.

DEF_USERS_BY_USERNAME_QUERY

Gives SQL query that fetches user details by username.
2.

DEF_AUTHORITIES_BY_USERNAME_QUERY

Gives SQL query that fetches user authorities by username.
3.

DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY

Gives SQL query that fetches group authorities by username.

JdbcDaoImpl with Custom Schema

We can use JdbcDaoImpl with custom database schema. It provides following methods to configure our custom SQL queries.
1.

setUsersByUsernameQuery(String queryString)

Sets the SQL query to fetch user details by username.
2.

setAuthoritiesByUsernameQuery(String queryString)

Sets the SQL query to fetch authorities by username.
3.

setGroupAuthoritiesByUsernameQuery(String queryString)

Sets the SQL query to fetch group authorities by username.

Find the JdbcDaoImpl configuration code in JavaConfig using custom SQL query.

@Bean
public JdbcDaoImpl userDetailsService() {
	JdbcDaoImpl jdbcDaoImpl = new JdbcDaoImpl();
	jdbcDaoImpl.setDataSource(dataSource());
	jdbcDaoImpl.setUsersByUsernameQuery("select username,password,enabled from comp_users where username = ?");
	jdbcDaoImpl.setAuthoritiesByUsernameQuery("select username,authority from comp_authorities where username = ?");
	return jdbcDaoImpl;
}

Find the XML configuration for JdbcDaoImpl with custom SQL query.

<beans:bean id="userDetailsService" class="org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl">
	<beans:property name="dataSource" ref="dataSource" />
	<beans:property name="usersByUsernameQuery" value="select username,password,enabled from comp_users where username = ?" />
	<beans:property name="authoritiesByUsernameQuery" value="select username,authority from comp_authorities where username = ?" />
</beans:bean>

HSQLDB Database Schema

We are using HSQLDB database in our JdbcDaoImpl demo application. Create the following tables and insert rows.

CREATE TABLE comp_users (
	username VARCHAR(50) NOT NULL,
	password VARCHAR(300) NOT NULL,
	enabled TINYINT NOT NULL,
	PRIMARY KEY (username)
);

INSERT INTO comp_users (username, password, enabled) VALUES
	('krishna', '$2a$10$nnu2.EBSnJUQZmOv5hbD8.3C8dlifeLi26AWpoKN31FqjNXrijQMq', 1),
	('surya', '$2a$10$DPzHnInANVY1utbuRfe0eOojtE02k23TGB5Q0L6mIHOBJQhKU7DTi', 1);

CREATE TABLE comp_authorities (
  username VARCHAR(50) NOT NULL,
  authority VARCHAR(50) NOT NULL,
  CONSTRAINT fk_authorities_users FOREIGN KEY (username) REFERENCES comp_users (username)
); 

INSERT INTO comp_authorities (username, authority) VALUES
	('krishna', 'ROLE_ADMIN'),
	('surya', 'ROLE_USER');

We have created two users with credentials krishna/k123 and surya/s123 in our application.

Maven Dependencies

pom.xml

<parent>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-parent</artifactId>
	<version>2.2.1.RELEASE</version>
	<relativePath />
</parent>
<properties>
	<context.path>spring-app</context.path>
	<java.version>11</java.version>
</properties>
<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-security</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-jdbc</artifactId>
	</dependency>
	<dependency>
		<groupId>org.hsqldb</groupId>
		<artifactId>hsqldb</artifactId>
		<version>2.5.0</version>
	</dependency>
	<dependency>
		<groupId>jstl</groupId>
		<artifactId>jstl</artifactId>
		<version>1.2</version>
	</dependency>
</dependencies>

JdbcDaoImpl Example with JavaConfig

SecurityConfig.java

package com.concretepage.config;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
	@Override
	protected void configure(HttpSecurity http) throws Exception {
		http.authorizeRequests()
		.antMatchers("/admin/**").access("hasRole('ADMIN')")
		.antMatchers("/user/**").access("hasAnyRole('USER', 'ADMIN')")
		.and().formLogin()  //login configuration
                .loginPage("/customLogin.jsp")
                .loginProcessingUrl("/appLogin")
                .usernameParameter("username")
                .passwordParameter("password")
                .defaultSuccessUrl("/user")	
		.and().logout()  //logout configuration
		.logoutUrl("/appLogout") 
		.logoutSuccessUrl("/customLogin.jsp")
		.and().exceptionHandling() //exception handling configuration
		.accessDeniedPage("/error");
	} 	

	@Override
	protected void configure(AuthenticationManagerBuilder auth) throws Exception {
		auth.userDetailsService(userDetailsService()).passwordEncoder(passwordEncoder());
	}

	@Bean
	public JdbcDaoImpl userDetailsService() {
		JdbcDaoImpl jdbcDaoImpl = new JdbcDaoImpl();
		jdbcDaoImpl.setDataSource(dataSource());
		jdbcDaoImpl.setUsersByUsernameQuery("select username,password,enabled from comp_users where username = ?");
		jdbcDaoImpl.setAuthoritiesByUsernameQuery("select username,authority from comp_authorities where username = ?");
		return jdbcDaoImpl;
	}

	@Bean
	public PasswordEncoder passwordEncoder() {
		BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
		return passwordEncoder;
	}

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
		dataSource.setUrl("jdbc:hsqldb:hsql://localhost:9001");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}
}

AppConfig.java

package com.concretepage.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.view.InternalResourceViewResolver;

@Configuration
@ComponentScan("com.concretepage")
@EnableWebMvc
public class AppConfig {
	@Bean
	public InternalResourceViewResolver viewResolver() {
		InternalResourceViewResolver resolver = new InternalResourceViewResolver();
		resolver.setPrefix("/WEB-INF/secure/");
		resolver.setSuffix(".jsp");
		return resolver;
	}
}

AppController.java

package com.concretepage.controller;
import org.springframework.security.core.Authentication;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class AppController {
	@RequestMapping(value="/admin")
	public String adminInfo(ModelMap model, Authentication authentication) {
		model.addAttribute("name", authentication.getName());
 		return "info";
 	}
	@RequestMapping(value="/user")
	public String userInfo(ModelMap model, Authentication authentication) {
		model.addAttribute("name", authentication.getName());
 		return "info";
 	}	
	@RequestMapping(value="/error")
	public String error() {
 		return "access-denied";
 	}
}

SecurityInitializer.java

package com.concretepage.config;
import org.springframework.security.web.context.AbstractSecurityWebApplicationInitializer;
 
public class SecurityInitializer extends AbstractSecurityWebApplicationInitializer {
}

WebAppInitializer.java

package com.concretepage.config;
import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class WebAppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {
	@Override
	protected Class<?>[] getRootConfigClasses() {
		return new Class[] { AppConfig.class };
	}

	@Override
	protected Class<?>[] getServletConfigClasses() {
		return null;
	}

	@Override
	protected String[] getServletMappings() {
		return new String[] { "/" };
	}
}

customLogin.jsp

<html>
    <head>
        <title>Spring Security Login</title>
    </head>
    <body>
	<h3>Spring Security Login</h3>
	<font color="red">
		 ${SPRING_SECURITY_LAST_EXCEPTION.message}
	</font>
	<form action="<%=request.getContextPath()%>/appLogin" method="POST">
	   <table border='1' cellspacing='0' cellpadding='10'>
	   <tr><td>Username:</td> <td><input type="text" name="username"/></td></tr>
	   <tr><td>Password:</td> <td><input type="password" name="password"/></td></tr>
	   <tr>
		 <td colspan='2' align='center'><input type="submit" value="Login"/>
		  <input type="hidden" name="${_csrf.parameterName}" value="${_csrf.token}"/>			
		 </td>
	   </tr>
	   </table>
	</form>
    </body>
</html>

info.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
    <head>
        <title>Spring Security Example</title>
    </head>
    <body>
      <h3>Hello <c:out value="${name}"/></h3>
      <form action="<%=request.getContextPath()%>/appLogout" method="POST">
        <input type="submit" value="Logout"/>
        <input type="hidden" name="${_csrf.parameterName}" value="${_csrf.token}"/>		
      </form>      
    </body>
</html>

access-denied.jsp

<html>
    <head>
        <title>Spring Security</title>
    </head>
    <body>
      <h3>You are not authorized to access this page.</h3>
      <form action="<%=request.getContextPath()%>/appLogout" method="POST">
        <input type="submit" value="Logout"/>
        <input type="hidden" name="${_csrf.parameterName}" value="${_csrf.token}"/>		
      </form> 
    </body>
</html>

JdbcDaoImpl Example with XML Configuration

Here we will provide XML files of demo application using XML configuration. Java files and JSP files are same as given demo application using JavaConfig.
security-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/security"
	xmlns:beans="http://www.springframework.org/schema/beans" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans.xsd
	http://www.springframework.org/schema/security
	http://www.springframework.org/schema/security/spring-security.xsd">
	
	<http>
		<intercept-url  pattern="/admin/**" access="hasRole('ADMIN')" />
		<intercept-url  pattern="/user/**" access="hasAnyRole('USER', 'ADMIN')" />		
		<form-login 
		   login-page="/customLogin.jsp" 
		   login-processing-url="/appLogin"
		   username-parameter="username"
		   password-parameter="password"
		   default-target-url="/user"/>
		<logout 
		   logout-url="/appLogout" 
		   logout-success-url="/customLogin.jsp"/>  
		<access-denied-handler error-page="/error"/>
	</http>
	
	<beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<beans:property name="driverClassName" value="org.hsqldb.jdbcDriver" />
		<beans:property name="url" value="jdbc:hsqldb:hsql://localhost:9001" />
		<beans:property name="username" value="sa" />
		<beans:property name="password" value="" />
	</beans:bean>

	<beans:bean id="userDetailsService" class="org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl">
		<beans:property name="dataSource" ref="dataSource" />
		<beans:property name="usersByUsernameQuery" value="select username,password,enabled from comp_users where username = ?" />
		<beans:property name="authoritiesByUsernameQuery" value="select username,authority from comp_authorities where username = ?" />
	</beans:bean>
	
	<beans:bean name="bcryptEncoder" class="org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder"/>
		
	<authentication-manager>
		<authentication-provider user-service-ref="userDetailsService">
		    <password-encoder ref="bcryptEncoder"/>
		</authentication-provider>
	</authentication-manager>
				
</beans:beans>

app-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans     
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd">
        
	<context:component-scan base-package="com.concretepage" />
	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	  <property name="prefix" value="/WEB-INF/secure/"/>
	  <property name="suffix" value=".jsp"/> 
        </bean>
</beans>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="4.0"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee 
        http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd">

	<display-name>Spring Security Example</display-name>
	<servlet>
		<servlet-name>dispatcher</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/app-config.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>dispatcher</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>
	           /WEB-INF/security-config.xml
	        </param-value>
	</context-param>
	<filter>
		<filter-name>springSecurityFilterChain</filter-name>
		<filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
	</filter>
	<filter-mapping>
		<filter-name>springSecurityFilterChain</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>
</web-app>