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

  • Multiple database connection

    Hello everyone,

    I am new to the tier applications and my english is far from being perfect. So I ask you to be indulgent.

    I'm using hibernate + spring 2.5 + jpa + tomcat 5.5 and i wanted to connect to two database (postgres and sqlserver).

    My goal is to recover data from a database (sqlserver), and make verification with data from a database (postgres) and if necessary update theme.

    In that order i did some googling , but i didn't secceed to find compete exemples and here where i am :

    1) solution 1

    The solution was to duplicate the entity manager, one for each data source

    so my applicationContext.xml loocks like this :


    <!-- persistance jpa layer -->
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerE ntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter">
    <bean class="org.springframework.orm.jpa.vendor.Hibernat eJpaVendorAdapter">
    <property name="showSql" value="false" />
    <!-- postgres -->
    <property name="databasePlatform" value="org.hibernate.dialect.PostgreSQLDialect" />
    <!-- fin postgres -->
    <property name="generateDdl" value="true" />
    </bean>
    </property>

    </bean>

    <bean id="entityManagerFactorySqlServer" class="org.springframework.orm.jpa.LocalContainerE ntityManagerFactoryBean">
    <property name="dataSource" ref="dataSourceCars" />
    <property name="jpaVendorAdapter1">
    <bean class="org.springframework.orm.jpa.vendor.Hibernat eJpaVendorAdapter">
    <property name="showSql" value="false" />
    <!-- SqlServer -->
    <property name="databasePlatform" value="net.sf.hibernate.dialect.SQLServerDialect" />
    <!-- fin SqlServer -->
    <property name="generateDdl" value="true" />
    </bean>
    </property>
    </bean>

    <!-- data sources -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <!-- postgresql -->
    <property name="driverClass" value="org.postgresql.Driver" />
    <property name="jdbcUrl" value="jdbcostgresql:ip/database" />
    <property name="user" value="" />
    <property name="password" value="" />
    <!-- fin postgresql -->

    </bean>

    <!-- SqlServer -->
    <bean id="dataSourceCars" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <!-- SqlServer -->
    <property name="driverClass" value="net.sourceforge.jtds.jdbc.Driver" />
    <property name="jdbcUrl" value="jdbc:jtds:sqlserver://ip/database" />
    <property name="user" value="" />
    <property name="password" value="" />
    <!-- fin SqlServer -->
    </bean>

    Two persistance unit so my persistence.xml looks like this:

    <persistence-unit name="val1" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence </provider>
    <properties>
    <property name="hibernate.show_sql" value="false" />
    <property name="hibernate.hbm2ddl.auto" value="update" />

    <!-- postgresql -->
    <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" />
    <property name="hibernate.connection.username" value="" />
    <property name="hibernate.connection.password" value="" />
    <property name="hibernate.connection.url" value="" />
    <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
    <!-- fin postgresql -->

    <property name="hibernate.c3p0.min_size" value="5" />
    <property name="hibernate.c3p0.max_size" value="30" />
    <property name="hibernate.c3p0.timeout" value="300" />
    <property name="hibernate.c3p0.max_statements" value="50" />
    <property name="hibernate.c3p0.idle_test_period" value="3000" />
    </properties>
    </persistence-unit>

    <persistence-unit name="val2" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence </provider>
    <properties>
    <property name="hibernate.show_sql" value="false" />
    <property name="hibernate.hbm2ddl.auto" value="update" />

    <!-- SqlServer -->
    <property name="hibernate.connection.driver_class" value="net.sourceforge.jtds.jdbc.Driver" />
    <property name="hibernate.connection.username" value="" />
    <property name="hibernate.connection.password" value="" />
    <property name="hibernate.connection.url" value="" />
    <property name="hibernate.dialect" value="net.sf.hibernate.dialect.SQLServerDialect" />
    <!-- fin SqlServer -->

    <property name="hibernate.c3p0.min_size" value="5" />
    <property name="hibernate.c3p0.max_size" value="30" />
    <property name="hibernate.c3p0.timeout" value="300" />
    <property name="hibernate.c3p0.max_statements" value="50" />
    <property name="hibernate.c3p0.idle_test_period" value="3000" />
    </properties>
    </persistence-unit>

    *But here i have a warning telling me that "Multiple persistence units defined - only the first persistence unit will be recognized".

    Finally two hibernate.cfg.xml, they are simillar and look like this :

    <hibernate-configuration>
    <session-factory>
    <property name="hibernate.connection.driver_class">net.sourc eforge.jtds.jdbc.Driver</property>
    <property name="hibernate.connection.username"></property>
    <property name="hibernate.connection.password"></property>
    <property name="hibernate.connection.url">jdbc:jtds:sqlserve r:/</property>
    <property name="hibernate.dialect">net.sf.hibernate.dialect. SQLServerDialect</property>

    </session-factory>
    </hibernate-configuration>

    But there are many things that i don't understand : how jpa or spring will use the hibernate config files if we don't specify it ? sur i missing some things .

    2) solution 2:

    some said when we need to use multiple databse connections, we need to use JTATransactionManager with (JOTM or Atomikos).

    ok, but how we do this with the xml configuration files and the annotations and where do we specify to the DAO witch datasource manager must be used ?

    So if some one can gide me to some recent documentations or tutorials or have explanations I TAKE .

    Thank in advanced.

  • #2
    Ran into the same issue

    Hi, I ran into the same issue.
    I wanted to connect to 2 databases (datasources) and had trouble finding some good examples.
    In my case I am using Spring 3.0.0 (trunk) + Hibernate 3.5.0 (Trunk) + Postgresql datasources.
    In a nutshell, I ended up using JTATransactionManager, SessionFactories (instead of EntityManager, bean: AnnotationSessionFactoryBean), and Atomikos Transaction Manager).
    I did a write up and posted it on google's blogger so others can find it in the future via google. Here is the post: http://endurotracker.blogspot.com/20...databases.html

    Comment


    • #3
      thank you for the link i'll check.

      Comment


      • #4
        Sorry to come back on the subject after this time and hope that you will answer djdrisco. I wanted to poste a comment on the blog but i didn't succeed. So my request is, can you add an exemple of your UserDao and your UsersService.
        Thanx in advance for your help.

        Comment

        Working...
        X