how to resolve design issue in my App using Spring + iBatis

Question

We use Spring + iBatis in all of our DAO's to fetch data from Stored Procedures.

There are two main JNDI connections. one going to the datawarehouse and another going to livedb.

recently lot of SPs have been moved from the livedb to the datawarehouse and vice versa.

This is creating issues on the java side because:

Now, each DAO does not directly JUST relate to either datawarehouse or livedb. There might be methods in DAO A which relate to datawarehouse and others might relate to livedb. In order to do this we have to change the sqlMapClientTemplate (because spring makes a dao have one to one mapping with JNDI connection). So we do this by:

this.setSqlMapClientTemplate(getSqlTemplDW()); //get connection to DW
getSqlMapClientTemplate().queryForList("dw_sps.somemapping", parmMap);
this.setSqlMapClientTemplate(getSqlTempl()); //set connection to live db

as you can see ...this is forcing us to have a lot of this same code in bunch of places.

Questions

Is it considered a design flaw to have one DAO talk to two different JNDI's? ( I know its not a design flaw in classic JDBC daos but is it different with Spring + iBatis?)

the getSqlTemplDW() method you see up there looks like:

public SqlMapClientTemplate getSqlTemplDW() {
    SqlMapClient scl = (SqlMapClient) ApplicationInitializer.getApplicationContext().getBean("SqlMapClientDW");
    DataSource dsc = (DataSource) ApplicationInitializer.getApplicationContext().getBean("DataSourceDW");
    return new SqlMapClientTemplate(dsc, scl);
}

as you can see, I am using javax.sql.DataSource. However, we have been told to not use this import!! So now I am stuck. I cant use this import (meaning cant change connections in my DAO). So I've been getting suggestions that every dao should only have one to one mapping to the JNDI.

I want to know..is there a way around this at all?

Skeleton

spring-for-ibatis.xml

<bean id="datasource1" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="jdbc/RSRC/asdf/sdf/oltp"/>
</bean>

<bean id="datasource2" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="jdbc/RSRC/asdf/efs/dw"/>
</bean>

<bean id="sqlMapClient1" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
  <property name="configLocation" value="classpath:sql-map-config-oracle.xml"/>
  <property name="dataSource" ref="datasource1"/>
</bean>

<bean id="sqlMapClient2" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
  <property name="configLocation" value="classpath:sql-map-config-dw.xml"/>
  <property name="dataSource" ref="datasource2"/>
</bean>

<!--dao bean-->
<bean id="examinationIfaceDAO" class="some.path.ExaminationIbatisDAO">
  <property name="sqlMapClient" ref="sqlMapClient1"/>
  <property name="dataSource" ref="datasource1"/>
</bean>

sql-map-config-oracle.xml

<sqlMapConfig>
   <settings enhancementEnabled="true" useStatementNamespaces="true" />
        <sqlMap resource="iBatis_file_with_sps_to_live_db.xml"/>
</sqlMapConfig>

sql-map-config-dw.xml

<sqlMapConfig>
   <settings enhancementEnabled="true" useStatementNamespaces="true" />
    <sqlMap resource="iBatis_file_with_sps_to_dw.xml" />
</sqlMapConfig>

Interface for Examination

 public interface ExaminationIfaceDAO {
    public boolean goToDW(String userId);
    public boolean goToLiveDB(String userId);
 }

ExaminationIbatisDAO

 public class ExaminationIbatisDAO implements EexaminationIfaceDAO {
    public boolean goToDW(String userId) {
        HashMap paramMap = new HashMap();
        paramMap.put("userId", userId);
        //following line will break as it does not know about this mapping file
        getSqlMapClientTemplate().queryForObject("iBatis_file_with_sps_to_dw.isAuthorized", paramMap);
        return true;
    }
    public boolean goToLiveDB(String userId) {
        HashMap paramMap = new HashMap();
        paramMap.put("userId", userId);
        //following line will be ok as it knows about this mapping file
        getSqlMapClientTemplate().queryForObject("iBatis_file_with_sps_to_live_db.isAuthorized", paramMap);
        return true;
    }
 }

calling all this from some action

examDAO = (ExaminationIfaceDAO)ApplicationInitializer.getApplicationContext().getBean("eexaminationIfaceDAO");
boolean b = reexamDAO.goToDW("myuserid");

Answer 1

JDBC or not, I consider that a Data Access Object abstracts one underlying data access implementation. So even if they share the same interface, I would provide two implementations if I have two data sources (whether they are two RDBMS or not).

Answer 2

It is not easy (for me) to understand your exact difficulty, perhaps it would help if you give us more skeleton of your DAO class and its relations with other spring managed beans. You say "spring makes a dao have one to one mapping with JNDI connection"; I don't get that. You surely can have (in your Spring container) a pair of DataSource beans (one for each database), and a corresponding pair SqlMapClientTemplate beans. Then you'd inject, into each DAO object, the two SqlMapClientTemplate beans and use (in each method) the one that points to the correct database. Am I missing something?

Update: looking at the skeleton, I see nothing that prevents you to have the two clientMaps injected int your dao, and instead of having one getSqlMapClientTemplate() having two methods: getSqlMapClientTemplateDb1() getSqlMapClientTemplateDb2() or whatever.

Perhaps there is some conceptual issue here.

A standard practice is to have the DAOs defined as interfaces and then implement the concrete classes for the particular framework or database. The goal is to ease the migration from one framework/database to another, without touching the interface. So that, for example, you can have a IUserDao interface with the method public User getUser(int id), and two different implementations -say- UserDaoPostgresql and UserDaoMysql; the methods would implement two alternative ways of doing the same thing (getting the user from alternative repositories). Typically, in this scenario, the upper layers will ignore this - and the concrete DAO to be used will be specified in the wiring (eg with Spring), and hence, fixed at deploy time. But only one implementation will be used in each deployed instance (except perhaps in some testing or migration code) and the code inside the dao (and also in the upper layers) should remain agnostic about these two alternative implementations.

But there are other scenarios. For example, when one has part of the application data in a Postgresql database, and another part in a Mysql db (or in another independent Pg db, or in some non relation db, even some logs). Then, as the role of the DAO is simply to abstract the acces to your data repositories, your IUserDao might have two methods getUser(int userid) getUserHistory(int userid) and it can perfectly happen that (in one particular implementation) each method must access a different database or resource. Here, it would not be at all bad practice to choose explicitly different datasources inside one DAO class.

Perhaps you should make clear if your scenario is the former or the later.

Answer 3

Better design would be to refactor your DAOs. Something like this.

public interface ExaminationIfaceDAO {
   boolean checkUser(String userId);
}

public class OracleExaminationDAO implements ExaminationIfaceDAO{
   public boolean checkUser(String userId){
      //TO:DO
   }
}
public class DWExaminationDAO implements ExaminationIfaceDAO{
  public boolean checkUser(String userId){
      //TO:DO
  }
}

Your DAOs look to be singletons and its not advisable to switch datasource like this.Also you can consider creating two different beans of same type one with livedb datasource and one with dw datasource. And use the appropriate bean for your task.

<bean id="examinationDBDAO" class="some.path.ExaminationIbatisDAO">
 <property name="sqlMapClient" ref="sqlMapClient1"/>
 <property name="dataSource" ref="datasource1"/>
</bean>
<bean id="examinationDWDAO" class="some.path.ExaminationIbatisDAO">
 <property name="sqlMapClient" ref="sqlMapClient1"/>
 <property name="dataSource" ref="datasource2"/>
</bean>