Announcement Announcement Module
No announcement yet.
Override JdbcDaoImpl trouble Page Title Module
Move Remove Collapse
This topic is closed
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Override JdbcDaoImpl trouble

    Dear All,

    I am authentication against Database. Because my database have not

    "enabled" column, and I use "activated" column instead of "enabled" column

    Now, I am override the "net.sf.acegisecurity.providers.dao.jdbc.JdbcDaoIm pl" class.

    but it still can't access my database. Application server console show some error messages

    like "error code [17059]; Fail to convert to internal representation;".

    How can i customize my sql statement? thanks any advise...

    ================================================== ===================

    application server console snippet information

    DEBUG: 16:56:04: net.sf.acegisecurity.providers.ProviderManager: Authentication
    attempt using net.sf.acegisecurity.providers.dao.DaoAuthenticationProvider
    DEBUG: 16:56:05: net.sf.acegisecurity.ui.AbstractProcessingFilter: Updated Conte
    xtHolder to contain null Authentication
    DEBUG: 16:56:05: net.sf.acegisecurity.ui.AbstractProcessingFilter: Authenticatio
    n request failed: net.sf.acegisecurity.AuthenticationServiceException: PreparedS
    tatementCallback; uncategorized SQLException for SQL [select user_id, password,
    activated from p_user where user_id=?]; SQL state [null]; error code [17059]; 
    Fail to convert to internal representation; nested exception is 
    java.sql.SQLException: Fail to convert to internal representation;
     nested exception is org.springframework.jdbc.UncategorizedSQLException: Prepare
    dStatementCallback; uncategorized SQLException for SQL [select user_id, password
    , activated from p_user where user_id=?]; SQL state [null]; error code [17059];
    Fail to convert to internal representation  ; nested exception is 
    java.sql.SQLException: Fail to convert to internal representation
    DEBUG: 16:56:05: net.sf.acegisecurity.context.HttpSessionContextIntegrationFilte
    r: Context stored to HttpSession: '
    [email protected]: Null authentication'
    as follows, my custom
    public class JdbcDaoImpl extends
    		net.sf.acegisecurity.providers.dao.jdbc.JdbcDaoImpl {
    	protected class UsersByUsernameMapping extends MappingSqlQuery{
    		public UsersByUsernameMapping(DataSource ds) {
    	        super(ds, getUsersByUsernameQuery());
    	       	declareParameter(new SqlParameter(Types.VARCHAR));
    	    protected Object mapRow(ResultSet rs, int rownum)
    	        throws SQLException {
    	        String username = rs.getString(1);
    	        String password = rs.getString(2);
    	        String activated = rs.getString(3);
    	        boolean enabled = activated.toLowerCase().equals("y")?true:false;
    	        UserDetails user = new User(username, password, enabled, true,
    	                true, true,
    	                new GrantedAuthority[] {new GrantedAuthorityImpl("HOLDER")});
    	        return user;

    snippet config file

    	<bean id="jdbcDaoImpl" class="">
           <property name="dataSource"><ref bean="dataSource"/></property>
           <property name="usersByUsernameQuery">
    	       <value>select user_id, password, activated from p_user where user_id=?</value>
    	  <property name="authoritiesByUsernameQuery">
               <value>SELECT p.user_id, r.role_name FROM role as r, p_user as p WHERE  r.role_id = p.role_id AND p.user_id = ?</value>

  • #2
    I had the same problem.
    Actually it's useless to the "inject" a authoritiesByUsernameQuery jdbcDAoImpl because unfortunatly when you inject SQL code using authoritiesByUsernameQuery it's just ignored.
    JdbcDAoImpl uses MappingSqlQuery to execute the method and at the time you inject your SQL it's not regenerating the MappingSqlQuery with your new SQL code it keeps the old one.

    I'm pretty sure I'm not clear...somebody help me explain this
    The solution (actually shoud be better ones outside in the wild) is to subclass jdbcDAOImpl and inject complete MappingSqlQuery instead of inject SQL.

    Hope it helps.


    • #3
      in jdbcDAOImpl there is a method you have to override in a subclass :

      * Extension point to allow other MappingSqlQuery objects to be substituted
      * in a subclass
      protected void initMappingSqlQueries() {
      setUsersByUsernameMapping(new UsersByUsernameMapping(getDataSource()));
      setAuthoritiesByUsernameMapping(new AuthoritiesByUsernameMapping(

      that's why the system is ignoring your SQL.


      • #4
        thanks your reply!

        i am modified the subclass of JdbcDaoImpl class

        but occur the same error message

        "error code [17059]; Fail to convert to internal representation;"

        public class JdbcDaoImpl extends
        		net.sf.acegisecurity.providers.dao.jdbc.JdbcDaoImpl &#123;
           protected void initMappingSqlQueries&#40;&#41; &#123;
                setUsersByUsernameMapping&#40;new UsersByUsernameMapping&#40;getDataSource&#40;&#41;&#41;&#41;;
                setAuthoritiesByUsernameMapping&#40;new AuthoritiesByUsernameMapping&#40;getDataSource&#40;&#41;&#41;&#41;;
        	protected class UsersByUsernameMapping extends MappingSqlQuery&#123;
        		public UsersByUsernameMapping&#40;DataSource ds&#41; &#123;
        	        super&#40;ds, getUsersByUsernameQuery&#40;&#41;&#41;;
        	       	declareParameter&#40;new SqlParameter&#40;"user_id", Types.VARCHAR&#41;&#41;;
        	    protected Object mapRow&#40;ResultSet rs, int rownum&#41;
        	        throws SQLException &#123;
        	        String username = rs.getString&#40;1&#41;;
        	        String password = rs.getString&#40;2&#41;;
        	        String activated = rs.getString&#40;3&#41;;
        	        boolean enabled = activated.toLowerCase&#40;&#41;.equals&#40;"y"&#41;?true&#58;false;
        	        UserDetails user = new User&#40;username, password, enabled, true,
        	                true, true,
        	                new GrantedAuthority&#91;&#93; &#123;new GrantedAuthorityImpl&#40;"HOLDER"&#41;&#125;&#41;;
        	        return user;

        I don't want to use default sql statment.
        Because Oracle 9i does not support data type "boolean". so ... i must be override default statement.
        who can teach me how to correct override JdbcDaoImpl. :roll:


        • #5

          Another alternative you may explore is to write your own passwordAuthenticationDao

           <!-- Authenticate via SWA DAO -->
              <bean id="passwordAuthenticationDao"
                  <property name="sessionFactory"><ref bean="mySessionFactory"/></property>
              <!-- Simple Username/Password authentication -->
              <bean id="authenticationProvider"
                <property name="passwordAuthenticationDao">
                  <ref local="passwordAuthenticationDao"/>
              <!-- Control access/authorization via Acegi class, stored in HTTP Session
              <bean id="authenticationManager"
                <property name="providers">
                    <ref local="authenticationProvider"/>
          You just need to write a class that verifies the login/password yourself, which implements:

          PasswordAuthenticationDao.loadUserByUsernameAndPas sword(String username, String password)

          In the xml above that would be inside AcegiDAOImpl, which returns a UserDetails with your roles etc. This class is often customized to have more fields.