5 Replies Latest reply on Dec 11, 2010 1:36 PM by sterlingsmith

    Best practice for deploying a database connection pool to ServiceMix 4

    benhough

      Greetings,

       

      I'm on a mission to find a "best practice" for deploying database connection pools to ServiceMix/FUSE ESB 4 as services to be reused by multiple other bundles.  Presently I'm attempting to use MySQL and DBCP BasicDataSource with a pool MBean exported using Spring JMX.  I'm using ESB version 4.3.0-fuse-03-00.

       

      The problem that I can't seem to get past is a class not found exception thrown from DBCP for com.mysql.jdbc.Driver.  From related threads on this forum, it appears that the problem has something to do with the servicemix commons-dbcp bundle not allowing dynamic imports.  Meanwhile, I built and installed the jpa-osgi example which starts up a DBCP BasicDataSource using hsql and it seems to work fine.  But after looking through it, I'm still not sure what the problem is.

       

      Is there something I'm missing?  I'd like to use the driver bundles provided by the respective vendors, so if possible I'd like to avoid repackaging drivers as commons-dbcp fragments as suggested in another thread.

       

      So I'm interested in either fixes for my current setup or some directions (link/tutorial/webcast/etc) that will point me in the right direction.

       

      I've got the servicemix commons-dbcp bundle installed (version 1.2.2_6), as well as the MySQL driver bundle (com.mysql.jdbc, version 5.1.13).

       

      I've attached my bundle pom.

       

      Thanks.

      -Ben

       

      Edited by: benhough on Dec 7, 2010 8:21 PM

        • 1. Re: Best practice for deploying a database connection pool to ServiceMix 4
          ffang

          Hi,

           

          A general solution is make your jdbc driver bundle as fragment bundle and attach it to commons-dbcp, so that the class in jdbc driver bundle is available for commons-dbcp.

           

          Freeman

          • 2. Re: Best practice for deploying a database connection pool to ServiceMix 4
            marc.lebeau

            Maybe it is not the ultimate solution, but here is how I deployed it :

            1. Create a class that initialises the driver as soon as possible:

             

                 public class InitialisingBasicDataSource extends org.apache.commons.dbcp.BasicDataSource implements InitializingBean {bq. // This is just so that the Database Driver is initialised when the Spring ClassLoader is used.
                                                public void afterPropertiesSet() throws Exception {bq. Connection conn = getConnection();
                                                String vq = this.getValidationQuery();
                                                if (vq == null) {
                                                vq = "SELECT 1";

            }

            conn.nativeSQL(vq);

            conn.close();

            }

             

            }

             

            2. Expose it as a service to all the other bundles :

             

                <bean id="el1DSImpl" class="be.lampiris.esb.el1DS.InitialisingBasicDataSource"

                      destroy-method="close">

                    <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver"/>

                    <property name="url" value="${dataSource.el1.URL};domain=${dataSource.el1.domain}"/>

                    <property name="username" value="${dataSource.el1.user}"/>

                    <property name="password" value="${dataSource.el1.password}"/>

                    <property name="validationQuery" value="SELECT 1"/>

                    <property name="maxActive" value="${dataSource.el1.maxActive}"/>

                </bean>

             

                <osgi:service id="el1DS" interface="javax.sql.DataSource" ref="el1DSImpl"/>

             

             

             

             

             

            Hoping this will help you.

            • 3. Re: Best practice for deploying a database connection pool to ServiceMix 4
              benhough

              Ok, so I was thinking that attaching a fragment involved repackaging the MySQL driver in a new bundle and re-deploying the whole thing... but when I realized that I could create a tiny fragment bundle that does nothing but import the vanilla MySQL package to the commons-dbcp ServiceMix bundle, it made a whole lot more sense.

               

              I've attached my resulting setup in case it helps anyone.  If anyone knows of a better setup, I welcome your comments.

               

              Note that this setup depends on the following non-default bundles:

              ServiceMix bundles commons-dbcp (osgi:install -s mvn:org.apache.servicemix.bundles/commons-dbcp/1.2.2_6)

              MySQL JDBC driver (osgi:install -s mvn:mysql/mysql-connector-java/5.1.13)

               

               

              Thanks to each of you.

              -Ben

               

               

              Attached:

              dbcp-mysql-fragment.zip: a commons-dbcp fragment project that imports com.mysql.jdbc

              common-mysql-pool.zip: a dbcp pool project with Spring configuration, and MBean export.

              • 4. Re: Best practice for deploying a database connection pool to ServiceMix 4
                ffang

                Hi Ben,

                 

                Thanks so much to share your solution.

                 

                And yeah, a tiny fragment bundle which only expose necessary resource make more sense here.

                Freeman

                • 5. Re: Best practice for deploying a database connection pool to ServiceMix 4
                  sterlingsmith

                  I recently had the exact same issue with a NoClassDefFoundError on my Postgres database. Like you, the drivers were not included as part of my bundle for OSGI and had to be included.

                   

                  Including this as a stand-alone fragment is probably better as that way you can re-use this for any component that needs it (by using Import-Package and Export-Package in the Maven POM file)... I decided to use the Embed-Dir tag to include my driver in the package and included the jar in my resources directory.

                   

                  See code snippet for

                   

                  I'm still trying to get my code away from legacy (but nicely simple jdbc) to use Spring enabled DAO using Hibernate and I suspect I'll be posting here shortly with similar issues.

                   

                  Edited by: sterlingsmith on Dec 11, 2010 6:35 PM