Multiple scemas with Ibatis

[nani2ratna]

Hi guys,

i need to connect to multiple schemas in one web application.

I am using spring 2.5.6. I am using ibatis as a data source.
Can anybody tell me which solution is better practise for this type of problems.

Thanks and Regards
Ratna

[zebthecat]

for example:

<!-- jndi defined data source -->
<bean id="dataSource1" class="org.springframework.jndi.JndiObjectFactoryBean" scope="singleton" abstract="false" lazy-init="default" autowire="default" dependency-check="default">
       <property name="jndiName" value="java:comp/env/jdbc/schema1" /> 
  </bean>
  <bean id="dataSource2" class="org.springframework.jndi.JndiObjectFactoryBean" scope="singleton" abstract="false" lazy-init="default" autowire="default" dependency-check="default">
       <property name="jndiName" value="java:comp/env/jdbc/schema2" /> 
  </bean>

<!-- path to your sqlMap context files -->
<bean id="sqlMapClient1" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
    <property name="configLocation" value="WEB-INF/sqlmap-context1.xml"/>
    <property name="dataSource" ref="dataSource"/>
  </bean>
  <bean id="sqlMapClient2" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
    <property name="configLocation" value="WEB-INF/sqlmap-context2.xml"/>
    <property name="dataSource" ref="mysqlcreditDataSource"/>
  </bean>

<!-- A couple of DAOs -->
 <bean id="firstDAO" class="com.whatever.domain.dao.FirstDAOImpl">
    <property name="sqlMapClient" ref="sqlMapClient1"/>
  </bean>
  <bean id="secondDAO" class="com.whatever.domain.dao.SecondDAOImpl">
    <property name="sqlMapClient" ref="sqlMapClient2"/>
  </bean>

We connect to three separate database this way. The only downside is you'll have figure a way controlling transactions if you want a single transaction to span multiple schemas but we didn't need to.

[nani2ratna]

Hi,

Thank you very much for the reply.
So its very easy to configure multiple data sources.

Once again thanks.

Thanks and regards
Ratna

[mcasanket]

Hi zebthecat,

I am having somewhat similar problem. But I am having both my databases on different machines.
The query is using both the databases at once. How can I solve the problem?

Below is my query:
SELECT * FROM
database2.vfr_prgs a
JOIN database2.vfr_prgdef b ON a.catid = b.catid
JOIN database1.cmisfile cmisfile ON a.THREE_LETTER_LINE_CODE = cmisfile.LLINE WHERE THREE_LETTER_LINE_CODE = #line# AND available = 1
GROUP BY title
ORDER BY title


please help!

[prashob]

If its is multiple schema its better to give permission to access one schema from other schema than creating 2 datasources.

[mcasanket]

Hi Prashob,

Thanks for your reply. How can I give permission to the schema's to be accessed that reside on another machine?
I am really confused that, I am having only one query and this query is using both this databases (both are on different machines). I will be having SqlMapClientTemplate reference in my DAO class. If I create two datasources then how will this both used by the class?

Please help!

If its is multiple schema its better to give permission to access one schema from other schema than creating 2 datasources.

[prashob]

you can do it with using dblink
So that you need to create only one data source and from query you can access.
http://docs.oracle.com/cd/B12037_01/...ments_5005.htm