3 Replies Latest reply on Sep 28, 2007 5:10 AM by stephen.friedrich

    Change Database Connection at Runtime

    saschaq benson Newbie

      Hello,

      I want to change the Database at Runtime. I start with an initial Database Connection and read some data from it. The data Contains the Connection Data to another Database, which i would Connect.
      But i Don't know how it works. I think i must change the Connection via Hibernate and then tell Seam that the Database Connection has been changed or so on.

      If anyone knows a Solution for that, then tell me please ;)

        • 1. Re: Change Database Connection at Runtime
          knuwu Newbie

          Hi breaveFencer

          In your resources/WEB-INF/components.xml configuration file you find the following line:

          <core:managed-persistence-context name="entityManager"
           auto-create="true"
           persistence-unit-jndi-name="java:/yourDBnameEntityManagerFactory" />
          


          Where "yourDBname" is normaly the name of your db reference in /META-INF/persistence.xml

           <persistence-unit name="yourDBname">
           <provider>org.hibernate.ejb.HibernatePersistence</provider>
           <jta-data-source>java:/yourDBnameDatasource</jta-data-source>
           <properties>
           <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
           <property name="hibernate.cache.use_query_cache" value="true"/>
           <property name="hibernate.show_sql" value="true"/>
           <property name="jboss.entity.manager.factory.jndi.name" value="java:/yourDBnameEntityManagerFactory"/>
           </properties>
           </persistence-unit>
          


          which is actually a reference to the jboss *-ds.xml configuration.

          You may configure a second database connection (an addition *-ds.xml, a new enty in your persitence.xml and components.xml).

          <core:managed-persistence-context name="entityManager2"
           auto-create="true"
           persistence-unit-jndi-name="java:/yourOtherDBnameEntityManagerFactory" />
          


          On code level you may reference the new entityManager with

          entityManager= (EntityManager) Component.getInstance( "entityManager2" );
          


          The old one with
          entityManager= (EntityManager) Component.getInstance( "entityManager" );
          
          Or simply with annotation
          
          @In EntityManager entityManager;
          


          Can not garantee this works but i would try it.

          Greetings Ralph

          • 2. Re: Change Database Connection at Runtime
            saschaq benson Newbie

            thank you.

            But the problem is, that the Database Connection Information(url, password, username..) are retrievied at runtime of the application. so i can not configure a second, third.. database in a configuration file.

            at Application start i have the following databasase defined in JBoss-Beans.xml(i'm using tomcat 6.0 server) :


            <?xml version="1.0" encoding="UTF-8"?>

            <deployment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="urn:jboss:bean-deployer bean-deployer_1_0.xsd"
            xmlns="urn:jboss:bean-deployer">


            com.microsoft.sqlserver.jdbc.SQLServerDriver
            jdbc:sqlserver://localhost:1500;databaseName=Userdatabase
            user
            user
            java:/ZDBsource
            0
            10
            1000
            100000















            This datasource will be queried to gain the Datasource Connection Information to another Database. At this point i must programatically establish the Connection to that second Database.



            • 3. Re: Change Database Connection at Runtime
              stephen.friedrich Novice

              We had the same problem and never really found a solution for it.
              The (business/legal) requirement was to store each clients financial data in a separate DB, but have a single web application where a use can switch between different clients (depending on authorizations).

              We never really found a way to solve the problem. As a workaround we
              did what Ralph proposed and individually configured lots of datasources (one for each client, plus a couple spare ones) with generic names and access the entity manager programmatically.
              Besides being an ugly hack and wasting resources it does not really solve the problem the original poster is having:

              What do you do if the database connection details are only delivered dynamically after login?

              This would be dead-easy to do with JDBC, but Hibernate/JPA/Seam makes this hard to do.

              @bravefencer (?): Put code into code tags!