4 Replies Latest reply: Mar 14, 2012 11:46 AM by Mandar Tuljapurkar RSS

    Best way to connect to an external database?

    cris.holm

      I have a need to connect to an outside database from within my custom Jive classes. I don't want to change the way database operations are performed currently (using the DefaultConnectionProvider, I think). I want to add additional connections managed in a connection pool to my other db.

       

      I've looked through the forums, documentation and source code and found a couple of candidates:

      1. com.jivesoftware.community.migration.RemoteJiveInstanceUtils looks like it will make the connection for me, but it appears that it's using DriverManager.getConnection, which will not use a connection pool.
      2. com.jivesoftware.base.database.JndiConnectionProvider looks like it could work for me with the container (Tomcat 6) managing the connection pool. But I don't want this ConnectionProvider to replace the currently used DefaultConnectionProvider. Can these two ConnectionProvider implementations run concurrently and play nicely?

       

      Anyone out there have any feedback on these approaches, or have advice on a better approach to use?

       

      FYI, I'm using Jive 4.5.5.

       

      Thanks in advance.

        • Re: Best way to connect to an external database?
          Mandar Tuljapurkar

          Hi Cristopher,

            I think you can start by looking at com.jivesoftware.base.database.DefaultConnectionProvider, as you mentioned earlier.

          You could probably create a CustomConnectionProvider and use it in your DAO classes.

           

          To specify the connection parameters, you could use jive-startup.xml I believe:

           

          <database>

            <customProvider>

            <username>xxx</username>

            <serverURL>jdbc:postgresql://localhost:5432/customDB</serverURL>

            <maxConnections>25</maxConnections>

            <connectionTimeout>1.0</connectionTimeout>

            <driver>org.postgresql.Driver</driver>

            <password>xxx</password>

            <minConnections>5</minConnections>

            </customtProvider>

            </database>

           

          Remember to use customProvider in your new CustomConnectionProvider class.

          Let me know if this worked...

           

          Regards,

          Mandar

            • Re: Best way to connect to an external database?
              cris.holm

              Hi Mandar,

               

              Thanks for your reply. I think what you suggested is part of what I need, but I need just a little more guidance.

               

              I need to connect to 2 separate databases and maintain connection pools at the same time. Here's what I'm after: I want the default connection to remain intact, but add an additional connection and use one or more ConnectionProvider objects to manage these. Looking through the jive code, it looks like there can be only one ConnectionProvider. So from this perspective, this one ConnectionProvider would need to manage both connection pools.

               

              <database>

                  <defaultProvider> <!-- this is the Jive data connection -->

                    <username>user</username>

                    <maxConnections>50</maxConnections>

                    <minConnections>25</minConnections>

                    <serverURL>jdbc:oracle:oci:@myserver.com:1521:instance1</serverURL>

                    <driver>oracle.jdbc.driver.OracleDriver</driver>

                    <connectionTimeout>1.0</connectionTimeout>

                    <password>password</password>

                  </defaultProvider>

                 <customProvider> <!-- this is the a custom data connection to non-Jive data -->

                    <username>otheruser</username>

                    <maxConnections>50</maxConnections>

                    <minConnections>25</minConnections>

                    <serverURL>jdbc:oracle:oci:@myserver.com:1521:instance2</serverURL>

                    <driver>oracle.jdbc.driver.OracleDriver</driver>

                    <connectionTimeout>1.0</connectionTimeout>

                    <password>otherpassword</password>

                  </customProvider>

                </database>

              <connectionProvider>

                  <className>com.mysoftware.database.CustomConnectionProvider</className>

                </connectionProvider>

               

              I have some thoughts on how to go about creating this CustomConnectionProvider, but I'm wondering if anyone has done this before, and if there are any gotchas or best practices I should be aware of.

               

              I'm using a Tomcat 6 container, so my preference would be to use it to manage my database connection pools and inject them into the Jive using Spring and JNDI.

               

              Thanks,

              Cris

                • Re: Best way to connect to an external database?
                  Mandar Tuljapurkar

                  Hi Cris,

                       I get your point. You require two live connection providers, by declaring them in the xml as follows:

                  <connectionProvider>

                    <className>com.jivesoftware.base.database.DefaultConnectionProvider

                    </className>

                    </connectionProvider>

                  <customConnectionProvider>

                    <className>com.jivesoftware.base.database.DefaultConnectionProvider

                    </className>

                    </customConnectionProvider>

                   

                  I have not done an actual customization of this type. A good place to start with is the ConnectionManager class, the spring-daoContext.xml and DataSourceFactory class.

                  You can either extend the ConnectionManager class or modify it to handle multiple connection providers. Once this is done, we need to find a way to access the providers in DAO class, using simpleJdbcTemplate, which I have not looked into. See if you could find a way to access custom providers in your DAO by some way. I'll get back if I find any.

                   

                  Regards,

                  Mandar