How do I get dynamic DataSources with iBATIS and Spring?

 

This idea is based Spring's AbstractRoutingDataSource class, but operates with SqlMapClients instead to avoid caching issues in iBATIS. Since
SqlMapClient doesn't segment its cache based on DataSources, a single SqlMapClient will merge all the results from multiple DataSources into one cache.

Below are two classes, RoutableSqlMapClient and VendorContextHolder. RoutableSqlMapClient is a class that implements the ExtendedSqlMapClient
interface but delegates all calls to other SqlMapClients. It does the delegation by using VendorContextHolder, which holds a static ThreadLocal variable, which for my needs is represents a VendorTypes enum. This ThreadLocal variable is a key into a Map of target delegate SqlMapClient objects. Each consumer calling into this code will pass along a vendor, which is used to set the ThreadLocal variable in VendorContextHolder.

Here is how the relevant Spring xml config looks:

 

<!-- 
    Pulls the VendorOne DataSource from JNDI and sets up the 
    SqlMapClient for VendorOne, the other vendors look the same. 
     
    I also use Spring's 2.x AOP declarative transaction management 
    around each vendor's DataSource, but I'm leaving that out for this 
    example. 
     
    Also, in my case, all my SqlMapClients use the same sqlmap config. 
    --> 
    <bean id="vendorOneDataSource" class="org.springframework.jndi.JndiObjectFactoryBean"> 
    <property name="jndiName"> 
    <value>java:/comp/env/jdbc/VendorOne</value> 
    </property> 
    </bean> 
    <bean id="vendorOneSqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> 
    <property name="configLocation" value="classpath:sqlmap-config.xml"/> 
    <property name="dataSource" ref="vendorOneDataSource"/> 
    </bean> 
     
    <!-- 
    Sets up the RoutingSqlMapClient with a Map of vendor specific 
    SqlMapClient beans and it keyed by the VendorTypes enum. 
    --> 
    <bean id="sqlMapClient" class="com.localmatters.bo.core.util.RoutingSqlMapClient"> 
    <property name="targetSqlMapClients"> 
    <map key-type="com.localmatters.bo.core.commons.VendorTypes"> 
    <entry key="VendorOne" value-ref="vendorOneSqlMapClient"/> 
    <entry key="VendorTwo" value-ref="vendorTwoSqlMapClient"/> 
    <entry key="VendorThree" value-ref="vendorThreeSqlMapClient"/> 
    </map> 
    </property> 
    </bean> 
     
    <!-- 
    Creates an Abator-generated DAO bean and sets the RoutableSqlMap. 
    --> 
    <bean id="userDAO" class="com.localmatters.bo.core.nontology.dao.UserDAOImpl"> 
    <property name="sqlMapClient" ref="sqlMapClient"/> 
    </bean> 

The consumers of this API use a Service Locator/Manager that wraps Spring. When a thread wants a DAO, the code looks like this:

public UserDAO getUserDAO(VendorTypes vendor) { 
    VendorContextHolder.setVendorType(vendor); 
    return (UserDAO) context.getBean("userDAO"); 
    } 

It is entirely possible to not have a Service Locater/Manager and have the consumer set the VendorContextHolder directly. The Service Locator/Manager exists in this example because it's a common pattern.

At this point, the DAO just calls the ExtendedSqlMapClient methods as normal, but they get routed by RoutableSqlMapClient to a vendor specific SqlMapClient, complete with its own DataSource, TransactionManager, cache, etc.

There is a limitation with this approach that should be evaluated before use. As you can see, this scheme sets a ThreadLocal variable that determines the DataSource to use for the remainder of that Thread's execution. So in my case, if the Thread needs a different vendor, it
must make that call to the Service Locator with the different vendor. But this means a Thread can effectively only hit one DataSource at a
time, it is not possible to intermix access to multiple DataSources from the same Thread.

For example, this won't work:

UserDAO vendorOneDAO = 
    ServiceLocator.getUserDAO(VendorTypes.VendorOne); 
    UserDAO vendorTwoDAO = 
    ServiceLocator.getUserDAO(VendorTYpes.VendorTwo); 
     
    vendorOneDAO.update(vendorOneUser); 
    vendorTwoDAO.update(vendorTwoUser); 

Because the DataSource is determined on a per-Thread basis, the second call to getUserDAO will set the vendor to VendorTwo. Plus there's
really only one UserDAO in the Spring Context. Regardless, the first call to update(vendorOneUser) will actually hit the VendorTwo
DataSource. And really, if you're using Spring correctly, you probably aren't using a Service Locator/Manager, so it may not matter. In that
case, you probably have a single DAO pointer and would use it something like this:

VendorContextHolder.set(VendorTypes.VendorOne); 
    vendorDAO.update(vendorOneUser); 
    VendorContextHolder.set(VendorTypes.VendorTwo); 
    vendorDAO.update(vendorTwoUser); 

Which is not ideal because your consumer code has to know about the VendorContextHolder, but it works.

I've also attached a servlet Filter that looks for a 'vendor' parameter
in the request URL and sets the VendorContextHolder accordingly. I use
this for a fat client that talks to a remoting service via a webapp, but
it should work for all-HTML webapps too.

 

package com.localmatters.bo.core.util;  
      
    import java.sql.Connection;  
    import java.sql.SQLException;  
    import java.util.List;  
    import java.util.Map;  
      
    import javax.sql.DataSource;  
      
    import com.ibatis.common.util.PaginatedList;  
    import com.ibatis.sqlmap.client.SqlMapClient;  
    import com.ibatis.sqlmap.client.SqlMapSession;  
    import com.ibatis.sqlmap.client.event.RowHandler;  
    import com.ibatis.sqlmap.engine.execution.SqlExecutor;  
    import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;  
    import com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate;  
    import com.ibatis.sqlmap.engine.mapping.statement.MappedStatement;  
    import com.localmatters.bo.core.commons.VendorTypes;  
      
    public class RoutingSqlMapClient implements ExtendedSqlMapClient {  
      
    private Map<VendorTypes, ExtendedSqlMapClient> targetSqlMapClients;  
      
    public void flushDataCache() {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).flushDataCache();  
    }  
      
    public void flushDataCache(String cacheId) {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).flushDataCache(cacheId);  
    }  
      
    public SqlMapSession getSession() {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).getSession();  
    }  
      
    public SqlMapSession openSession() {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).openSession();  
    }  
      
    public SqlMapSession openSession(Connection conn) {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).openSession(conn);  
    }  
      
    public int delete(String id, Object parameterObject) throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).delete(id, parameterObject);  
    }  
      
    public int executeBatch() throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).executeBatch();  
    }  
      
    public Object insert(String id, Object parameterObject) throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).insert(id, parameterObject);  
    }  
      
    public List queryForList(String id, Object parameterObject)  
    throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).queryForList(id, parameterObject);  
    }  
      
    public List queryForList(String id, Object parameterObject, int skip,  
    int max) throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).queryForList(id, parameterObject, skip, max);  
    }  
      
    public Map queryForMap(String id, Object parameterObject, String keyProp)  
    throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).queryForMap(id, parameterObject, keyProp);  
    }  
      
    public Map queryForMap(String id, Object parameterObject, String keyProp,  
    String valueProp) throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).queryForMap(id, parameterObject, keyProp, valueProp);  
    }  
      
    public Object queryForObject(String id, Object parameterObject)  
    throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).queryForObject(id, parameterObject);  
    }  
      
    public Object queryForObject(String id, Object parameterObject,  
    Object resultObject) throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).queryForObject(id, parameterObject, resultObject);  
    }  
      
    public PaginatedList queryForPaginatedList(String id,  
    Object parameterObject, int pageSize) throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).queryForPaginatedList(id, parameterObject, pageSize);  
    }  
      
    public void queryWithRowHandler(String id, Object parameterObject,  
    RowHandler rowHandler) throws SQLException {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).queryWithRowHandler(id, parameterObject, rowHandler);  
    }  
      
    public void startBatch() throws SQLException {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).startBatch();  
    }  
      
    public int update(String id, Object parameterObject) throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).update(id, parameterObject);  
    }  
      
    public void commitTransaction() throws SQLException {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).commitTransaction();  
    }  
      
    public void endTransaction() throws SQLException {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).endTransaction();  
    }  
      
    public Connection getCurrentConnection() throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).getCurrentConnection();  
    }  
      
    public DataSource getDataSource() {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).getDataSource();  
    }  
      
    public Connection getUserConnection() throws SQLException {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).getUserConnection();  
    }  
      
    public void setUserConnection(Connection connnection) throws SQLException {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).setUserConnection(connnection);  
    }  
      
    public void startTransaction() throws SQLException {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).startTransaction();  
    }  
      
    public void startTransaction(int transactionIsolation) throws SQLException {  
    targetSqlMapClients.get(VendorContextHolder.getVendorType()).startTransaction(transactionIsolation);  
    }  
      
    public Map<VendorTypes, ExtendedSqlMapClient> getTargetSqlMapClients() {  
    return targetSqlMapClients;  
    }  
      
    public void setTargetSqlMapClients(  
    Map<VendorTypes, ExtendedSqlMapClient> targetSqlMapClients) {  
    this.targetSqlMapClients = targetSqlMapClients;  
    }  
      
    public SqlMapExecutorDelegate getDelegate() {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).getDelegate();  
    }  
      
    public MappedStatement getMappedStatement(String id) {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).getMappedStatement(id);  
    }  
      
    public SqlExecutor getSqlExecutor() {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).getSqlExecutor();  
    }  
      
    public boolean isEnhancementEnabled() {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).isEnhancementEnabled();  
    }  
      
    public boolean isLazyLoadingEnabled() {  
    return targetSqlMapClients.get(VendorContextHolder.getVendorType()).isLazyLoadingEnabled();  
    }  
      
    }  

 

package com.localmatters.bo.core.util; 
     
    import org.apache.log4j.Logger; 
     
    import com.localmatters.bo.core.commons.VendorTypes; 
     
    public class VendorContextHolder { 
     
    private static Logger log = Logger.getLogger(VendorContextHolder.class); 
     
    private static final ThreadLocal<VendorTypes> contextHolder 
    = new ThreadLocal<VendorTypes>(); 
     
    public static void setVendorType(VendorTypes vendor) { 
     
    if (contextHolder.get() != null && 
    !contextHolder.get().equals(vendor)) { 
    log.warn("Resetting vendor from " + contextHolder.get() + " to " 
    + vendor); 
    } 
     
    contextHolder.set(vendor); 
    } 
     
    public static VendorTypes getVendorType() { 
     
    if (contextHolder.get() == null) { 
    return VendorTypes.LocalMatters; 
    } 
     
    return (VendorTypes) contextHolder.get(); 
    } 
     
    public static void clearVendorType() { 
    contextHolder.remove(); 
    } 
    } 

 

package com.localmatters.bo.core.nontology.servlet; 
     
    import java.io.IOException; 
     
    import javax.servlet.Filter; 
    import javax.servlet.FilterChain; 
    import javax.servlet.FilterConfig; 
    import javax.servlet.ServletException; 
    import javax.servlet.ServletRequest; 
    import javax.servlet.ServletResponse; 
    import javax.servlet.http.HttpServletRequest; 
     
    import org.apache.log4j.Logger; 
     
    import com.localmatters.bo.core.commons.VendorTypes; 
    import com.localmatters.bo.core.util.VendorContextHolder; 
     
    public class VendorFilter implements Filter { 
     
    public static final String VENDOR_PARAM = "vendor"; 
     
    Logger log = Logger.getLogger(VendorFilter.class); 
     
    public void destroy() { 
     
    } 
     
    public void doFilter(ServletRequest req, ServletResponse resp, 
    FilterChain chain) throws IOException, ServletException { 
     
    log.debug("Handling request"); 
     
    HttpServletRequest httpReq = (HttpServletRequest) req; 
     
    if (req.getParameter(VENDOR_PARAM) == null) { 
    throw new RuntimeException("vendor parameter must be specified"); 
    } 
     
    VendorContextHolder.setVendorType(VendorTypes.valueOf(req.getParameter(VENDOR_PARAM))); 
    log.debug("Setting vendor context to : " + VendorContextHolder.getVendorType()); 
    chain.doFilter(req, resp); 
     
    log.debug("Clearing vendor"); 
    VendorContextHolder.clearVendorType(); 
    } 
     
    public void init(FilterConfig arg0) throws ServletException { 
    log.info("Initializing VendorFilter"); 
    } 
    } 

 

package com.localmatters.bo.core.commons; 
     
    public enum VendorTypes { 
    VendorOne, 
    VendorTwo, 
    VendorThree 
    }