Over a million developers have joined DZone.

Spring Boot With Spring Security and JDBC (Part 2)

DZone's Guide to

Spring Boot With Spring Security and JDBC (Part 2)

The second in this in depth series on getting familiar with two key Spring frameworks and their interaction with databases.

Free Resource

Finding a database that fits with a container-based deployment model can be frustrating. Learn what to look for in a Docker database

In a previous post, we implemented security based on the default table schemas that Spring Security issues requests.

Considering users and roles, application developers use a schema that fits their needs. Spring gives us the ability to specify the queries needed in order to retrieve information such as username, password and roles.

Our custom tables will be pretty different from the tables of the first example.

drop table if exists Custom_Users;
create table Custom_Users(id bigint auto_increment, username varchar(255), password varchar(255));
insert into Custom_Users(username,password) values('TestUser','TestPass');

drop table if exists Custom_Roles;
create table Custom_Roles(username varchar(255),authority  varchar(255), UNIQUE(username,authority));
insert into Custom_Roles(username,authority) values('TestUser','superadmin');

In order to use these tables with Spring Security, we must pass the queries that Spring Security will use in order to retrieve the security information needed.

To do so, we will create a security configuration that will set up the queries needed.

package com.gkatzioura.spring.security.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Profile;
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 javax.sql.DataSource;

 * Created by gkatzioura on 9/20/16.
public class CustomQuerySecurityConfig extends WebSecurityConfigurerAdapter {

    private DataSource dataSource;

    public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
                .usersByUsernameQuery("SELECT username,password,1 FROM Custom_Users where username=?")
                .authoritiesByUsernameQuery("SELECT username,authority FROM Custom_Roles where username=?");

    protected void configure(HttpSecurity http) throws Exception {



We use Spring profiles. Our spring profile would be “customquery,” therefore, the CustomQuerySecurityConfig would be bound to the “customquery” profile.

In order to run, for convenience, we have to change the default profile in our build.gradle file.

group 'com.gkatzioura'
version '1.0-SNAPSHOT'

buildscript {
    repositories {
    dependencies {

apply plugin: 'java'
apply plugin: 'idea'
apply plugin: 'spring-boot'

sourceCompatibility = 1.8

repositories {

dependencies {
    testCompile "junit:junit:4.11"

bootRun {
    systemProperty "spring.profiles.active", "customquery"

To run the application issue:

gradle bootRun

You can find the source code on GitHub.

When you're looking for a SQL database that can scale elastically, while still preserving ACID guarantees, you only have a few choices. Find out how these elastic SQL databases perform in thishead-to-head YCSB benchmark.

spring ,security ,spring boot ,spring security

Published at DZone with permission of Emmanouil Gkatziouras, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}