Configuring JdbcUserDetailsManager to use custom SQL queries

In order to use custom SQL queries for our non-standard schema, we'll simply update our userDetailsService() method to include new queries. This is quite similar to how we enabled support for GBAC, except instead of using the default SQL, we will use our modified SQL. Notice that we remove our old setGroupAuthoritiesByUsernameQuery() method call, since we will not be using it in this example, in order to keep things simple:

    //src/main/java/com/packtpub/springsecurity/configuration/SecurityConfig.java

    private static String CUSTOM_USERS_BY_USERNAME_QUERY = ""+
                               "select email, password, true " +
                              "from calendar_users where email = ?";
    private static String CUSTOM_AUTHORITIES_BY_USERNAME_QUERY = ""+
               "select cua.id, cua.authority " +
               "from calendar_users cu, calendar_user_authorities "+
               "cua where cu.email = ? "+
               "and cu.id = cua.calendar_user";
    @Override
    public void configure(AuthenticationManagerBuilder auth) throws Exception {
    auth
       .jdbcAuthentication()
       .dataSource(dataSource)
       .usersByUsernameQuery(USERS_BY_USERNAME_QUERY)
       .authoritiesByUsernameQuery(
         AUTHORITIES_BY_USERNAME_QUERY
       );
    }

This is the only configuration required to use Spring Security to read settings from an existing, non-default schema! Start up the application and ensure that everything is working properly.

Your code should now look like this: calendar04.03-calendar.

Keep in mind that the utilization of an existing schema commonly requires an extension of JdbcUserDetailsManager to support the changing of passwords, the renaming of user accounts, and other user-management functions.

If you are using JdbcUserDetailsManager to perform user-management tasks, then there are over 20 SQL queries utilized by the class that are accessible through the configuration. However, only the three covered are available through the namespace configuration. Please refer to the Javadoc or source code to review the defaults for the queries used by JdbcUserDetailsManager.