Announcement Announcement Module
Collapse
No announcement yet.
Multiple scemas with Ibatis Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Multiple scemas with Ibatis

    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

  • #2
    Use multiple data sources and sqlmap tamplates

    for example:
    Code:
    <!-- 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.

    Comment


    • #3
      Thank you

      Hi,

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

      Once again thanks.

      Thanks and regards
      Ratna

      Comment


      • #4
        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!

        Comment


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

          Comment


          • #6
            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!

            Originally posted by prashob View Post
            If its is multiple schema its better to give permission to access one schema from other schema than creating 2 datasources.

            Comment


            • #7
              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

              Comment

              Working...
              X