Announcement Announcement Module
Collapse
No announcement yet.
Can i use Spring JDBC? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Can i use Spring JDBC?

    Hi all,

    i am utilizing a number of built-in features that come with the Oracle JDBC drivers. Unfortunately, its not obvious to me how i can then use Spring JDBC and the StoredProcedure class in particular.

    The features that i am using are OCI Connection Pooling and Proxy Authentication. OCI Connection pooling is relatively straight forward as it can be configured like a java bean (haven't done it yet though!).

    Using Proxy AUthentication is a bit trickier as you need to use a different method to get a connection. Here's an example:
    Code:
    Properties userNameProp = new Properties();
    userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME,
                    username);
    userNameProp.setProperty(OracleOCIConnectionPool.PROXY_PASSWORD,
                "password01");
    Connection conn = dataSource.getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME,
                        userNameProp);
                connectionStop = System.currentTimeMillis() - connectionStart;
    conn.close();
    Any ideas how i can still use Spring JDBC?

    Thanks

    Rakesh

  • #2
    You could create a DelegatingDataSource to handle it for you, using a ThreadLocal to pass the username/password to the proper call. You just pass your Oracle datasource to the OracleWrapper. Although I'm missing a cast in there somewhere to use the Oracle specific stuff. But it's basically what you could do.

    Code:
    public class OracleWrapper extends DelegatingDataSource {
      static ThreadLocal credentials = new ThreadLocal();
      private static class UserNamePassWord {
        String username, password;
    
        public UserNamePassWord(String username, String password) {
          this.username = username;
          this.password = password;
        }
      }
      public OracleWrapper(DataSource targetDataSource) {
        setTargetDataSource(targetDataSource);
      }
    
      public Connection getConnection() {
        UserNamePassWord up = (UserNamePassWord) credentials.get();
        if (up == null) {
          throw new IllegalArgumentException("Missing credentials");
        }
        Properties userNameProp = new Properties();
        userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME, up.username);
        userNameProp.setProperty(OracleOCIConnectionPool.PROXY_PASSWORD, up.password);
        return getTargetDataSource().getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME, userNameProp);
      }
      
      public static void setCredentials(String username, String password) {
        if (username == null) {
          credentials.set(null);
        } else {
          credentials.set(new UserNamePassWord(username, password));
        }
      }
    }
    To call the code you'd:

    Code:
    OracleWrapper.setCredentials(username, password);
    try {
      // JDBC code goes here.
    } finally {
      OracleWrapper.setCredentials(null, null);
    }

    Comment


    • #3
      Hmmmmm,

      could i pass the credentials into the constructor instead or set some properties?

      Also, how would i use it with the StoredProcedure class?

      Thanks

      Rakesh

      Comment


      • #4
        You most certainly could pass the credentials as part of the constructor. I was assuming that the username/password was different on each call.

        Here is how you would fit it together:

        DAO interface:
        Code:
        public interface MyServiceDao {
          public MyDomainObject getMyDomainObject(String key);
        }
        DAO implementation (complete with stored procedure):
        Code:
        public class MyServiceDaoJdbc extends JdbcDaoSupport implements MyServiceDao {
          MyStoredProcedure myStoredProcedure;
          protected void initDao() {
            super.initDao();
            myStoredProcedure = new MyStoredProcedure(getJdbcTemplate());
          }
        
          private static class getMyDomainObjectProcextends StoredProcedure {
            public getMyDomainObjectProc(JdbcTemplate jdbcTemplate) {
              super(jdbcTemplate, "getMyDomainObjectProc");
              declareParameter(new SqlReturnResultSet("result", new RowMapper() {
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                  MyDomainObject object = new MyDomainObject();
                  // Construct your domain object from the ResultSet
                  return object;
                }
              });
              declareParameter(new SqlParameter("key", Types.VARCHAR);
              compile();
              
            }
            public MyDomainObject execute(String key) {
               Map inMap = new HashMap();
               inMap.put("key", key);
               Map outMap = execute(inMap);
               return (MyDomainObject)outMap.get("result");
            }
          }
         
          public MyDomainObject getMyDomainObject(String key) {
            return getMyDomainObject.execute(key);
          }
        }
        Here is how you would wire it up (assuming username/password specified in the constructor). I've assumed the username and password are specified in external property file filled in by a PropertyPlaceholderConfigurer.

        Code:
        <bean id="myDomainObjectDao" class="MyServiceDaoJdbc">
          <property name="dataSource">
            <bean class="OracleWrapper>
              <constructor-arg type="java.util.String" value="${auth.username}"/>
              <constructor-arg type="java.util.String" value="${auth.password}"/>
              <constructor-arg type="java.sql.DataSource">
                <!-- Definition of a bean which represents your real datasource goes here -->
              </constructor-arg>
            </bean>
          </property>
        </bean>
        Either you could extract the DAO from the context directly or inject it as a property in a manager object which takes a MyServiceDao interface.

        Comment


        • #5
          Question about threadlocals

          Hi Bill,

          in order to get this to work i had to go back to first principles. The advanced configuration of the datasource was getting in the way.

          I also did everyhting programmatically, ie no application context, so that wiring up issues didn't get in the way either.

          So i set up an Oracle data source like this:

          OracleDataSource ods = new OracleDataSource();
          ods.setURL("jdbc:oracle:oci:@" + tnsAlias);

          then pass it into my Oracle wrapper class:
          OracleDataSourceWrapper odsw = new OracleDataSourceWrapper(ods);

          securityDaoImpl = new SecurityDaoImpl();
          securityDaoImpl.setDataSource(odsw);

          My OracleDataSourceWrapper is a simplified version of yours:

          public class OracleDataSourceWrapper extends DelegatingDataSource {
          final static Logger _logger = Logger.getLogger(OracleDataSourceWrapper.class);
          static ThreadLocal credentials = new ThreadLocal();

          public OracleDataSourceWrapper(DataSource targetDataSource) {
          setTargetDataSource(targetDataSource);
          }

          public Connection getConnection() throws SQLException {
          UserNamePassWord up = (UserNamePassWord) credentials.get();

          if (up == null) {
          throw new IllegalArgumentException("Missing credentials");
          }

          Connection conn = ((OracleDataSource) getTargetDataSource()).getConnection(up.username,
          up.password);

          return conn;

          }

          public static void setCredentials(String username, String password) {
          if (username == null) {
          credentials.set(null);
          } else {
          credentials.set(new UserNamePassWord(username, password));
          }
          }

          private static class UserNamePassWord {
          String username;
          String password;

          public UserNamePassWord(String username, String password) {
          this.username = username;
          this.password = password;
          }
          }
          }

          and when i want to use it in my dao it looks like this:

          public class SecurityDaoImpl implements SecurityDao {
          final static Logger _logger = Logger.getLogger(SecurityDaoImpl.class);
          private OracleDataSourceWrapper dataSource;

          public void setDataSource(OracleDataSourceWrapper dataSource) {
          this.dataSource = dataSource;
          }
          ...
          ...
          ...
          public boolean authenticate(String arg01, String arg02) {
          OracleDataSourceWrapper.setCredentials("db_data_ac cess_layer", "db_data_access_layer");

          AuthenticateSP sp = new AuthenticateSP(dataSource);
          //OracleDataSourceWrapper.setCredentials(null, null);

          return sp.execute(arg01);
          }

          for the moment, I am hardcoding the username/password. The stored proc is left out because it does not add anything.

          At present, it works but I have some questions:

          1. Your original example included a OracleWrapper.setCredentials(null,null); When i try and do this I get the IllegalArgumentException (see commented out line above). Does not setting them back to null afterwards mean there might be threading issues?

          2. I do not really understand ThreadLocals so am i correct to set the credentials statically like i do above?

          Thanks,

          Rakesh

          Comment


          • #6
            1. Your original example included a OracleWrapper.setCredentials(null,null); When i try and do this I get the IllegalArgumentException (see commented out line above). Does not setting them back to null afterwards mean there might be threading issues?

            2. I do not really understand ThreadLocals so am i correct to set the credentials statically like i do above?
            1) Oh, looks like I forgot about that detail. The setCredentials(null, null) is suppose to clear the ThreadLocal. My understanding of ThreadLocals is if its not garbage collected or is not null (and being a static class field, it won't be garbage collected), the thread that its bound to won't terminate. So to fix my bug, you can either comment out the IllegalArgument exception as you've done or add a clearCredentials method.

            2) Setting it statically won't be a problem. The reason why I set them statically instead of on an instance of Oracle wrapper is because at the point you call the DAO you don't have the Oracle wrapper itself. The ThreadLocal guarentees is that each thread will have a different credentials object for the life of that operation.

            You should wrap your call to your DAO in a try/finally block with the setCredentials(null, null) in the finally block so the credentials get cleared in all cases.

            Because of the way I've designed it you don't have to create a new copy of AuthenticateSP on each invocation. You can initalize that in setDataSource or add a constructor which takes a DataSource and initialize it there.

            Comment


            • #7
              Hi Bill,

              seeing alot of comments on the web about not using ThreadLocals if possible. Seems they can cause issues if used incorrectly.

              Is it possible to redo the example without ThreadLocals?

              Cheers

              Rakesh

              Comment


              • #8
                Hi Bill,

                seems i had a bug:

                public boolean authenticate(String arg01, String arg02) {
                OracleDataSourceWrapper.setCredentials(arg01, arg02);

                AuthenticateSP sp = new AuthenticateSP(dataSource);

                OracleDataSourceWrapper.setCredentials(null, null);

                return sp.execute(arg01);;
                }

                i was resetting the ThreadLocal state before i had finished using the datasource!!! I changed it to be like this and i get no errors:

                public boolean authenticate(String arg01, String arg02) {
                OracleDataSourceWrapper.setCredentials(arg01, arg02);

                AuthenticateSP sp = new AuthenticateSP(dataSource);

                boolean result = sp.execute(arg01);
                OracleDataSourceWrapper.setCredentials(null, null);

                return result;
                }

                I didn't understand your comment about not having to instatantiate a new AuthenticateSP each time. Could you elaborate? I did notice that your earlier code contianed classes I am unfamiliar with. For the record here's my stored proc:

                // inner classes wrapping stored proc calls
                private class AuthenticateSP extends StoredProcedure {
                public AuthenticateSP(DataSource dataSource) {
                super(dataSource,
                "db_data_access_layer.security_admin_pkg.authentic ate");
                setFunction(true);
                declareParameter(new SqlOutParameter("result", Types.INTEGER));
                declareParameter(new SqlParameter("username", Types.VARCHAR));
                compile();
                }

                public boolean execute(String username) {
                Map inParams = new HashMap(1);
                inParams.put("username", username);

                Map outParams = execute(inParams);

                int result = ((Integer) outParams.get("result")).intValue();

                return GlobalUtils.int_to_bool(result);
                }

                Cheers

                Rakesh

                Comment


                • #9
                  You could consider creating the Connection yourself and using a SingleConnectionDataSource. The problem with that is you'd have to create your DAO everytime to make sure its threadsafe.

                  Code:
                  Connection con = // Create Connection
                  DataSource ds = new SingleConnectionDataSource(con);
                  securityDaoImpl = new SecurityDaoImpl();
                  securityDaoImpl.setDataSource(ds);
                  securityDaoImpl.authenticate(uid); // password not necessary, since connection already used it.
                  Doesn't seem very clean, and you end up creating a DAO for each invocation.

                  Because Spring uses callbacks so heavily ThreadLocal is the only way I can think of to pass information like this down so many layers. It may be that your DAO doesn't use JdbcTemplate, you'll have to roll your own.

                  Comment


                  • #10
                    I didn't understand your comment about not having to instatantiate a new AuthenticateSP each time. Could you elaborate?
                    In your authenticate method:
                    Code:
                    public boolean authenticate(String arg01, String arg02) {
                    OracleDataSourceWrapper.setCredentials(arg01, arg02);
                    
                    AuthenticateSP sp = new AuthenticateSP(dataSource);
                    
                    OracleDataSourceWrapper.setCredentials(null, null);
                    
                    return sp.execute(arg01);;
                    }
                    You are creating a new AuthenticateSP each time you make a call. You can just created that once in the setDataSource method:

                    Code:
                    private AuthenticateSP sp;
                    
                    public void setDataSource(DataSource dataSource) {
                      this.dataSource = dataSource;
                      sp = new AuthenticateSP(dataSource);
                    }
                    You can do this because the StoredProcedure object and the OracleWrapperDataSource are both thread safe. So you can use it for each invocation.

                    Comment


                    • #11
                      I just discovered that the whole authentication stuff we've been talking about already exists in Spring using UserCredentialsDataSourceAdapter. Here is how I would suggest reworking your example:

                      Here is how you would wire up your DAO without using Spring:

                      Code:
                      DataSource ds = // Your normal oracle datasource
                      SecurityDaoImpl dao = new SecurityDaoImpl();
                      UserCredentialsDataSourceAdapter adapter = new UserCredentialsDataSourceAdapter();
                      // If you wanted to set default username/password here you can.  A non-null username in your DAO call will override this
                      adapter.setUserName(defaultUserName);
                      adapter.setPassword(defaultPassword);
                      adapter.setTargetDataSource(new OracleDataSourceWrapper(ds));
                      dao.setDataSource(adapter);
                      dao.afterPropertiesSet(); // This makes sure your initDao gets called.  Its part of JdbcDaoSupport.
                      Here is your OracleDataSourceWrapper. Note that this version throws an exception if getConnection is called without username/password because for this datasource you require username/password. Also note that it doesn't care where the username/password comes from.
                      Code:
                      public class OracleDataSourceWrapper extends DelegatingDataSource {
                          public OracleDataSourceWrapper(DataSource dataSource) {
                              if (dataSource instanceof OracleDataSource) {
                                setTargetDataSource(dataSource);
                              } else {
                                throw new IllegalArgumentException("dataSource needs to be of type OracleDataSource");
                          }
                          public Connection getConnection() throws SQLException {
                              throw new IllegalArgumentException("Connection requires username, password");
                          }
                      
                          public Connection getConnection(String username, String password) throws SQLException {
                              Properties userNameProp = new Properties();
                              userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME, username);
                              userNameProp.setProperty(OracleOCIConnectionPool.PROXY_PASSWORD, password);
                              return ((OracleDataSource)getTargetDataSource()).getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME, userNameProp);
                          }
                      }
                      Here is your reworked DAO. I used JdbcDaoSupport because it has standard ways of initializing your StoredProcedure object once.
                      Code:
                          public class SecurityDaoImpl extends JdbcDaoSupport implements SecurityDao {
                              private AuthenticateSP authenticateSP;
                      
                              /* This call sets the username/password on the UserCredentialsDataSourceAdapter 
                                  if username is passed in, but only if that's the datasource for this
                                  DAO.  That way instead of a ClassCastException you'll get a more
                                  meaningful exception later on like authentication failing.
                              */
                              public boolean authenticate(String username, String password) {
                                  if (username != null && getDataSource() instanceof UserCredentialsDataSourceAdapter) {
                                      UserCredentialsDataSourceAdapter adapter = (UserCredentialsDataSourceAdapter)getDataSource();
                                      adapter.setCredentialsForCurrentThread(username, password);
                                  }
                                  // I use a try/finally block to make sure the credentials get cleared 
                                  // properly.  The reason behind it is related to ThreadLocals, but all 
                                  // you need to know is that its a proper cleanup of resources.
                                  try {
                                      return authenticateSP.execute(username);
                                  } finally {
                                      if (username != null && getDataSource() instanceof UserCredentialsDataSourceAdapter) {
                                          UserCredentialsDataSourceAdapter adapter = (UserCredentialsDataSourceAdapter)getDataSource();
                                          adapter.removeCredentialsFromCurrentThread();
                                      }
                                  }
                              }
                              protected void initDao() {
                                  authenticateSP = new AuthenticateSP(getJdbcTemplate());
                              }
                       // inner classes wrapping stored proc calls
                               private class AuthenticateSP extends StoredProcedure {
                                   public AuthenticateSP(JdbcTemplate jdbcTemplate) {
                                       super(jdbcTemplate, "db_data_access_layer.security_admin_pkg.authentic ate");
                                       setFunction(true);
                                       declareParameter(new SqlOutParameter("result", Types.INTEGER));
                                       declareParameter(new SqlParameter("username", Types.VARCHAR));
                                       compile();
                                   }
                      
                                   public boolean execute(String username) {
                                       Map inParams = new HashMap(1);
                                       inParams.put("username", username);
                      
                                       Map outParams = execute(inParams);
                      
                                       int result = ((Integer) outParams.get("result")).intValue();
                      
                                       return GlobalUtils.int_to_bool(result);
                                   }
                               }
                          }
                      Even though it uses a little bit of casting, I think this solution fits Spring very well. And in case you are wondering it does use ThreadLocals behind the scenes. But only inside Spring classes which are properly unit tested and part of the framework.
                      Last edited by wpoitras; Feb 16th, 2006, 11:34 AM.

                      Comment


                      • #12
                        Hi Bill,

                        thats interesting indeed. It seems the UserCredentialsDataSourceAdapter is the appropriate class to use in this case.

                        I'm glad the ThreadLocal has gone from the code. I thinks its only fair to tell you Bill that I probably will not be passing in the username and password to each method call. My plan was (is) to use Acegi and get the credentials from that in the method. i think it uses thread locals to hold the credentials.

                        I wanted to get the DAO working first and then try and get Acegi working to supply the credentials.

                        If you're interested, here's one of my completed DAOs:

                        Code:
                        import oracle.jdbc.OracleTypes;
                        
                        import org.apache.log4j.Logger;
                        
                        import org.springframework.dao.DataAccessException;
                        
                        import org.springframework.jdbc.core.ResultSetExtractor;
                        import org.springframework.jdbc.core.SqlOutParameter;
                        import org.springframework.jdbc.core.SqlParameter;
                        import org.springframework.jdbc.object.StoredProcedure;
                        
                        import java.sql.ResultSet;
                        import java.sql.SQLException;
                        import java.sql.Types;
                        
                        import java.util.HashMap;
                        import java.util.LinkedHashMap;
                        import java.util.Map;
                        
                        import javax.sql.DataSource;
                        
                        
                        public class ReferenceDataDaoImpl implements ReferenceDataDao {
                            final static Logger _logger = Logger.getLogger(ReferenceDataDaoImpl.class);
                            private static final String CLIENT_TYPES = "CLIENT_TYPES";
                            private static final String USAGE_PURPOSE = "USAGE_PURPOSE";
                            private OracleDataSourceWrapper dataSource;
                            private Get2ColumnRefData sp;
                        
                            public void setDataSource(OracleDataSourceWrapper dataSource) {
                                this.dataSource = dataSource;
                                sp = new Get2ColumnRefData(dataSource);
                            }
                        
                            public Map getClientTypes() {
                                _logger.debug("Entering method ReferenceDataDaoImpl.getClientTypes");
                                OracleDataSourceWrapper.setCredentials("db_data_access_layer",
                                    "db_data_access_layer");
                        
                                Map map = sp.execute(CLIENT_TYPES);
                                OracleDataSourceWrapper.setCredentials(null, null);
                        
                                return map;
                            }
                        
                            public Map getUsagePurposeTypes() {
                                _logger.debug("Entering method ReferenceDataDaoImpl.getUsagePurposeTypes");
                                OracleDataSourceWrapper.setCredentials("db_data_access_layer",
                                    "db_data_access_layer");
                        
                                Map map = sp.execute(USAGE_PURPOSE);
                                OracleDataSourceWrapper.setCredentials(null, null);
                        
                                return map;
                            }
                        
                            private class Get2ColumnRefData extends StoredProcedure {
                                public Get2ColumnRefData(DataSource ds) {
                                    setDataSource(ds);
                                    setSql("reference_data_pkg.f_get2columnrefdata");
                                    setFunction(true);
                        
                                    declareParameter(new SqlOutParameter("cursor", OracleTypes.CURSOR,
                                            new ResultSetExtractor() {
                                            public Object extractData(ResultSet rs)
                                                throws SQLException, DataAccessException {
                                                logger.debug("Entering method .extractData");
                        
                                                Map resultsMap = new LinkedHashMap();
                        
                                                while (rs.next()) {
                                                    resultsMap.put(rs.getString(1), rs.getString(2));
                                                }
                        
                                                logger.debug("Exiting method .extractData");
                        
                                                return resultsMap;
                                            }
                                        }));
                                    declareParameter(new SqlParameter("p_table", Types.VARCHAR));
                        
                                    compile();
                                }
                        
                                public Map execute(String table) {
                                    Map in = new HashMap();
                                    in.put("p_table", table);
                        
                                    Map results = execute(in);
                        
                                    return (Map) results.get("cursor");
                                }
                            }
                        }
                        I'll have a go at adding in your changes.

                        Let me know if you can see any further improvements.

                        Thanks

                        Rakesh

                        Comment


                        • #13
                          Hi Bill,

                          i started to change my code to your version but ran into a bit of difficulty with the xml configuration.

                          You have the following line:

                          Code:
                          dao.afterPropertiesSet();
                          I am not sure how to do that in the xml file. Here's my current adapter class:

                          Code:
                          <bean id="referenceDataDaoImpl"
                          		class="com.amex.ifst.data.ReferenceDataDaoImpl">
                          		<property name="dataSource">
                          			<ref local="oracleDataSourceWrapperOCI" />
                          		</property>
                          	</bean>
                          
                          	<bean id="adapter"
                          		class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
                          		<property name="targetDataSource">
                          			<ref bean="oracleDataSourceWrapperOCI" />
                          		</property>
                          		<property name="username">
                          			<value>app_user</value>
                          		</property>
                          		<property name="password">
                          			<value>app_user</value>
                          		</property>
                          	</bean>
                          Or have i misunderstood?

                          Thanks

                          R

                          Comment


                          • #14
                            Hi Bill,

                            just to let you know the code 'seems' to work after i made your changes. I still haven't got the call to dao.afterPropertiesSet().

                            Is that ok? Would you like me to post my complete code?

                            Cheers

                            Rakesh

                            Comment


                            • #15
                              afterPropertiesSet is automatically called for each bean in the XML file. That's because its part of the InitializingBean interface, which Spring looks for.

                              If you have a class which doesn't implement InitializingBean (which should be the case for non-Spring subclasses) but you need to call initialization after all properties are set, you can use the init-method attribute in the XML file. See the reference manual for more details:

                              http://static.springframework.org/sp...tory-lifecycle

                              Comment

                              Working...
                              X