Moving Acegi's inMemoryDaoImpl to a jdbcDaoImpl

This was surprisingly easy, with only one thing that caught me up.

To use the standard Acegi implementation of the jdbcDaoImpl, you need to have a schema that will respond to the following SQL queries:

"SELECT username,password,enabled FROM users WHERE username = ?"
"SELECT username,authority FROM authorities WHERE username = ?"

Because our schema doesn't have a "users" or "authorities" table, you need to overwrite the properties in the jdbcDaoImpl class that hold those 2 SQL statements.

But I get ahead of myself...

First of all, change the application-context.xml file that is responsible for providing the AuthenticationManager bean to use the jdbcDaoImpl instead of the inMemoryDaoImpl.

Before

[bean id="daoAuthenticationProvider" 
      class="org.acegisecurity.providers.dao.DaoAuthenticationProvider"]
    [property name="userDetailsService"][ref local="inMemoryDaoImpl"/][/property]
[/bean]

After

[bean id="daoAuthenticationProvider" 
      class="org.acegisecurity.providers.dao.DaoAuthenticationProvider"]
    [property name="userDetailsService"][ref local="jdbcDaoImpl"/][/property]
[/bean]

Now define the jdbcDaoImpl like the Acegi documentation spells out:

[bean id="jdbcDaoImpl" 
      class="org.acegisecurity.userdetails.jdbc.JdbcDaoImpl"]
    [property name="dataSource"][ref bean="EDISDataSource"/][/property]
[/bean]

Note that in that dataSource property I'm using the EDISDataSource which on my project is defined like:
[bean id="EDISDataSource" class="org.apache.commons.dbcp.BasicDataSource"....

The important thing is that the bean implement the DataSource interface.

Now, because my schema doesn't look like the schema Acegi wants, I had to override the two parameters within the JdbcDaoImpl.java class that hold the SQL queries that the implementation uses to fetch credentials by username and authorities by username.

To wit; add the following properties to the jdbcDaoImpl bean definition:

    [property name="usersByUsernameQuery"]
        [value]
        select USER_NAME as username, 
               PASSWORD,
               1 as ENABLED 
          from CBISOWNER.CBIS_USER
         where USER_NAME=?  
        [/value]
    [/property]
    [property name="authoritiesByUsernameQuery"]
        [value]
        select USER_NAME as username,
               ROLE_NAME as authority 
          from CBISOWNER.CBIS_USER,
               CBISOWNER.CBIS_ROLE,
               CBISOWNER.USER_ROLE 
         where USER_ROLE.USER_ID=CBIS_USER.USER_ID 
           and USER_ROLE.ROLE_ID=CBIS_ROLE.ROLE_ID 
           and CBIS_USER.USER_NAME = ?
        [/value]
    [/property]

Obviously, your implementation of this facade will depend on whatever your schema looks like, but you get the picture.

One thing to note... in some of the javadocs within the JdbcDaoImpl, there's a reference to adding 'true' as ENABLED. That does not work. It causes a "Fail to convert to internal representation" stack trace. So I found on the forum.springframework.org site an example where someone used "1 as ENABLED" and that works like a champ.