6 Replies Latest reply on Dec 4, 2012 5:39 AM by Nicklas Karlsson

    How do I declare the data source factory to use in JBoss 7?

    Stephen McCormick Newbie

      I am attempting to write some code that will allow me to access all DB datasources at startup for Tomcat or JBoss. To do this I want to use the org.apache.commons.dbcp.BasicDataSource class, generated by the org.apache.commons.dbcp.BasicDataSourceFactory (both from Apache in commons-dbcp). The BasicDataSource class allows me to view the number of connections and some other fun stuff we'd like to know at server startup, programmatically.

       

      Tomcat provides a mechanism to accomplish this in the server.xml file:

       

      <Resource name="jdbc/MYDATASOURCE" auth="Container" type="javax.sql.DataSource"

                 factory="org.apache.commons.dbcp.BasicDataSourceFactory"

                     maxActive="3" maxIdle="1" maxWait="10000"

                     username="USERNAME" password="MYPASSWORD" driverClassName="com.mysql.jdbc.Driver"

                     url="jdbc:mysql://192.168.0.0:3310/MYDATASOURCE" validationQuery="select 1" testOnBorrow="true"/>

       

      Unfortunately I have not found anything in JBoss standalone.xml that allows me to do this, other than the <datasource-class>:

       

       

      <datasource jndi-name="java:/MYDATASOURCE" pool-name="MYDATASOURCE_DB" enabled="true" use-java-context="true" use-ccm="true">

                          <connection-url>jdbc:mysql://192.168.0.0:3310/MYDATASOURCE</connection-url>

                          <datasource-class>org.apache.commons.dbcp.BasicDataSource</datasource-class>

                          <driver>mysql-connector-java-5.1.21.jar</driver>

                          <pool>

                              <min-pool-size>1</min-pool-size>

                              <max-pool-size>10</max-pool-size>

                              <prefill>true</prefill>

                              <use-strict-min>false</use-strict-min>

                              <flush-strategy>FailingConnectionOnly</flush-strategy>

                          </pool>

                          <validation>

                              <check-valid-connection-sql>select 1</check-valid-connection-sql>

                              <validate-on-match>false</validate-on-match>

                              <background-validation>false</background-validation>

                              <use-fast-fail>false</use-fast-fail>

                          </validation>

                          <statement>

                              <track-statements>true</track-statements>

                              <prepared-statement-cache-size>0</prepared-statement-cache-size>

                          </statement>

                      </datasource>

       

      Which still results in the return of org.jboss.jca.adapters.jdbc.WrapperDataSource as a result at runtime. So my guess is there must be a way to specify the connection factory to be used...or not?

       

      Is there a mechanism in JBoss 7 (or 5 for that matter, we are actually trying to upgrade) that will allow me to do this? I have looked through tons of Blogs without success.

       

      Forgive me if I have posted this incorrectly...I am new to the community.

       

      Thanks!

        • 1. Re: How do I declare the data source factory to use in JBoss 7?
          Nicklas Karlsson Master

          Hmm, you can probably call unwrap() on the WrapperDataSource but it will probably just give you the JBoss implementation of the datasource (whatever that is). You can connect with a management client in order to view the datasource stats

          • 2. Re: How do I declare the data source factory to use in JBoss 7?
            Philippe Marschall Expert

            There isn't really a need to use dbcp in JBoss because JBoss comes with its own connection pool.

            • 3. Re: How do I declare the data source factory to use in JBoss 7?
              Stephen McCormick Newbie

              Thanks for the quick replies! Probably some more information is in order. We are using container managed pools, and I want the dbcp BasicDataSource only because it can give me (in my program) the ability to determine the user used to login, the number of connections, max connections, etc. Our main issue is that on some of our production deployments we have DB configuration problems, firewall issues or login issues - by the time we figure out what is really going wrong we have spent many hours late at night.

               

              So the idea is to check all connections at startup. If someone is watching the logs (or we programmatically do so) we save ourselves tons of headaches. Another option is to use the JBoss console, but we have to rely on our Sys Engineers to do so on a lot of boxes which is not optimal. About all I can currently do with the JBoss connection is see that a connection is successful. But I am not a connection pools expert so any advice is welcome!

               

              The code we are attempting to get to work on JBoss (which works under Tomcat is):

               

               

                  private static final String FAILURE_CONSTANT = "..............................*** Datasource Failure!!! ***";

                  private static final String NOT_DATASOURCE_CONSTANT = "..............................*** Not a DataSource!!! ***";   

                  private static final String NULL_CONNECTION_CONSTANT = "..............................*** Connection Null!!! ***";

                  private static final String TOMCAT_JNDI = "java:comp/env/jdbc/";

                     

                  public boolean executeTest()

                  {

                      boolean success = false;

                     

                      try

                      {

                          // Get the context

                          Context ctx = new InitialContext();

               

                          NamingEnumeration<NameClassPair> list = null;

                          String lookupStr = TOMCAT_JNDI;

                         

                          // Get list of all JDBC connections

                          try

                          {

                              NamingEnumeration<NameClassPair> list = ctx.list(lookupStr);

                          }

                          catch (Exception ex)

                          {

                              log.warn(lookupStr + " FAILED");

                          }

               

               

                          log.info(StartUp.DIVIDER_LINE);

                          log.info(" DATABASE CONNECTIVITY ");

                          log.info("                                                                                       Connections");

                          //                               1         2         3         4         5         6         7         8  

                          //                      12345678901234567890123456789012345678901234567890123456789012345678901234567890           

                          log.info(" Name          IP                                                     User          Min    Max    Current ");

                          // For each JDBC connection print out the information and test the connection

                          log.info(" --------------------------------------------------------------------------------------------------------- ");

                          while (list.hasMore())

                          {

                              // Get the data source

                              NameClassPair namePair = list.next();

                              BasicDataSource ds = (BasicDataSource) (ctx.lookup(lookupStr + namePair.getName()));

                             

                              String errorTxt = FAILURE_CONSTANT;

                             

                              try

                              {

                                  ds = (DataSource) (ctx.lookup(lookupStr + namePair.getName()));

                              }

                              catch (Exception ex)

                              {

                                  errorTxt += NOT_DATASOURCE_CONSTANT;

                              }

                             

                              Connection connection = null;

                              boolean successconn = false;

               

                              try

                              {

                                  // Just verify we can get a connection and it is not null

                                  if (!RSUtilities.isNull(ds))

                                  {

                                      connection = ds.getConnection();

                                      if (connection != null)

                                      {

                                          successconn = true;

                                      }

                                      else

                                      {

                                          errorTxt += NULL_CONNECTION_CONSTANT;

                                      }

                                  }

                                  else

                                  {

                                      errorTxt += NOT_DATASOURCE_CONSTANT;

                                  }

                              }

                              catch (Exception e)

                              {

                                  errorTxt += e.getMessage();

                              }

                              finally

                              {

                                  if (connection != null)

                                  {

                                      connection.close();

                                  }

                              }

               

                              String testResult =

                                  " " + RSUtilities.pad(namePair.getName(), 14)

                                      + RSUtilities.pad(ds.getUrl(), 55) + RSUtilities.pad(ds.getUsername(), 14);

               

                              // Handle failure and success results

                              if (successconn)

                              {

                                  testResult +=

                                      RSUtilities.pad(Integer.toString(ds.getInitialSize()), 7)

                                         + RSUtilities.pad(Integer.toString(ds.getMaxActive()), 7)

                                          + RSUtilities.pad(Integer.toString(ds.getNumActive()), 7);

                              }

                              else

                              {

                                  testResult += errorTxt;

                              }

               

                              log.info(testResult);

                          }

                          log.info(StartUp.DIVIDER_LINE);

               

                          success = true;

                      }

                      catch (Exception ex)

                      {

                          log.error("DBConnection Test   : ********* FAILED **********  ", ex);

                          ex.printStackTrace();

                          success = false;

                      }

               

                      return success;

                  }      

               

               

              Any other suggestions are welcome...

              • 4. Re: How do I declare the data source factory to use in JBoss 7?
                Nicklas Karlsson Master

                If you have remote access to the management ports, you could use the REST API for checking the status of the connection pool and automate that with wget or something similar.

                1 of 1 people found this helpful
                • 5. Re: How do I declare the data source factory to use in JBoss 7?
                  Stephen McCormick Newbie

                  Thanks Nicklas,

                   

                  I was thinking something similar. I know the console has access to the connection pools, but I didn't know how. Do you know where I can get the specifications for the interface? The only negative is that the solution will be very container specific. My hope was that I could write one piece of code to access this information no matter what container (Tomcat, JBoss, Weblogic). When we had Spring managed (not container managed) connections I was able to do that, but I'll take what I can get!

                   

                  Thanks!

                   

                  Stephen

                  • 6. Re: How do I declare the data source factory to use in JBoss 7?
                    Nicklas Karlsson Master

                    Try  https://community.jboss.org/wiki/JBossAS7Command-linePublicAPI or https://docs.jboss.org/author/display/AS71/Detyped+management+and+the+jboss-dmr+library

                    The command available are pretty much the same you can see in the CLI

                     

                    I guess it would be possible to use your own connection pool and bind it to JNDI but I'm not sure how the container integration would work in that case.