While writing a Java application for backend processing, I encountered the need to access multiple instances of Oracle databases from one single application. I used iBATIS for my data access layer and to do this in iBATIS is very straightforward.

Create the database.properties file which contains the information for the databases you want to access

driver=oracle.jdbc.driver.OracleDriver

jdbc.url.db1=jdbc:oracle:thin:@//<ip>:<port>/<instance>
username.db1=user1
password.db1=password1

jdbc.url.db2=jdbc:oracle:thin:@//<ip>:<port>/<instance>
username.db2=user2
password.db2=password2

Create the respective iBatis configuration files for db1 and db2

db1.xml

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<!DOCTYPE sqlMapConfig
PUBLIC “-//iBATIS.com//DTD SQL Map Config 2.0//EN” “http://www.ibatis.com/dtd/sql-map-config-2.dtd”>

<!– Always ensure to use the correct XML header as above! –>
<sqlMapConfig>
<!– The properties (name=value) in the file specified here can be used placeholders in this config
file (e.g. ${driver}. The file is usually relative to the classpath and is optional. –>
<properties resource=”database.properties”/>

<!– These settings control SqlMap configuration details, primarily to do with transaction
management. They are all optional (see the Developer Guide for more). –>
<settings
cacheModelsEnabled=”true”
enhancementEnabled=”true”
lazyLoadingEnabled=”true”
maxRequests=”1024″
maxSessions=”128″
maxTransactions=”32″
useStatementNamespaces=”false”
/>

<!– Type aliases allow you to use a shorter name for long fully qualified class names. –>

<!– Configure a datasource to use with this SQL Map using SimpleDataSource.
Notice the use of the properties from the above resource –>
<transactionManager type=”JDBC”>
<dataSource type=”DBCP”>
<property name=”driverClassName” value=”${driver}”/>
<property name=”url” value=”${jdbc.url.db1}”/>
<property name=”username” value=”${username.db1}”/>
<property name=”password” value=”${password.password1}”/>

<!– OPTIONAL PROPERTIES BELOW –>
<property name=”initialSize” value=”5″/>
<property name=”maxActive” value=”30″/>
<property name=”maxIdle” value=”20″/>
<property name=”maxWait” value=”60000″/>
<property name=”poolPreparedStatements” value=”true”/>
<property name=”validationQuery” value=”select 0 from dual”/>
<property name=”testOnBorrow” value=”true”/>
<!– <property name=”testWhileIdle” value=”true”/>–>
</dataSource>
</transactionManager>

<!– Identify all SQL Map XML files to be loaded by this SQL map. Notice the paths
are relative to the classpath. For now, we only have one –>
<sqlMap resource=”maps/table1.xml”/>

</sqlMapConfig>

db2.xml

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<!DOCTYPE sqlMapConfig
PUBLIC “-//iBATIS.com//DTD SQL Map Config 2.0//EN” “http://www.ibatis.com/dtd/sql-map-config-2.dtd”>

<!– Always ensure to use the correct XML header as above! –>
<sqlMapConfig>
<!– The properties (name=value) in the file specified here can be used placeholders in this config
file (e.g. ${driver}. The file is usually relative to the classpath and is optional. –>
<properties resource=”database.properties”/>

<!– These settings control SqlMap configuration details, primarily to do with transaction
management. They are all optional (see the Developer Guide for more). –>
<settings
cacheModelsEnabled=”true”
enhancementEnabled=”true”
lazyLoadingEnabled=”true”
maxRequests=”1024″
maxSessions=”128″
maxTransactions=”32″
useStatementNamespaces=”false”
/>

<!– Type aliases allow you to use a shorter name for long fully qualified class names. –>

<!– Configure a datasource to use with this SQL Map using SimpleDataSource.
Notice the use of the properties from the above resource –>
<transactionManager type=”JDBC”>
<dataSource type=”DBCP”>
<property name=”driverClassName” value=”${driver}”/>
<property name=”url” value=”${jdbc.url.db2}”/>
<property name=”username” value=”${username.db2}”/>
<property name=”password” value=”${password.password2}”/>

<!– OPTIONAL PROPERTIES BELOW –>
<property name=”initialSize” value=”5″/>
<property name=”maxActive” value=”30″/>
<property name=”maxIdle” value=”20″/>
<property name=”maxWait” value=”60000″/>
<property name=”poolPreparedStatements” value=”true”/>
<property name=”validationQuery” value=”select 0 from dual”/>
<property name=”testOnBorrow” value=”true”/>
<!– <property name=”testWhileIdle” value=”true”/>–>
</dataSource>
</transactionManager>

<!– Identify all SQL Map XML files to be loaded by this SQL map. Notice the paths
are relative to the classpath. For now, we only have one –>
<sqlMap resource=”maps/table2.xml”/>

</sqlMapConfig>

db1.xml and db2.xml use the information in database.properties

Now using the following SqlConfig class, you can create the SqlMapClient using the 2 XML files to access your databases.

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.io.Reader;

public class SqlConfig {

private static SqlMapClient db1SqlMap = null;
private static SqlMapClient db2SpSqlMap = null;

// Static initializer
static {
try {
String resource = “db1.xml”;
Reader reader = Resources.getResourceAsReader(resource);
db1SqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

resource = “db2.xml”;
reader = Resources.getResourceAsReader(resource);
db2SpSqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

} catch (Exception e) {
// If you get an error at this point, it doesn�t matter what it was. It is going to be
// unrecoverable and we will want the app to blow up hard so we are aware of the
// problem. You should always log such errors and re-throw them in such a way that
// you can be made immediately aware of the problem.
e.printStackTrace();
throw new RuntimeException(”Error initializing Cause: ” + e);
}
}

public static SqlMapClient getDb1SqlMapInstance() {
return db1SqlMap;
}

public static SqlMapClient getDb2SpSqlMapInstance() {
return db2SpSqlMap;
}

}