5 Replies Latest reply on May 25, 2010 2:21 PM by Rajesh Swain

    How to use DB connection pool from DB ?

    Javier Leyba Newbie

      Hi

      I'm new to JBoss and want to use a MySQL DB Connection Pool from my servlet application.

      Following docs instructions I did.

      I've placed mysql jar file driver in lib dir of default server.

      I've copied mysql-ds.xml to default server deploy directory and edited it as follows

      
      <?xml version="1.0" encoding="UTF-8"?>
      
      <!-- $Id: mysql-ds.xml,v 1.3.2.1 2004/12/01 11:46:00 schrouf Exp $ -->
      <!-- Datasource config for MySQL using 3.0.9 available from:
      http://www.mysql.com/downloads/api-jdbc-stable.html
      -->
      
      <datasources>
       <local-tx-datasource>
       <jndi-name>MySqlDS</jndi-name>
       <connection-url>jdbc:mysql://172.31.112.16:3306/proteo1</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>mapa</user-name>
       <password>mapa</password>
       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
      
       <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
       <min-pool-size>5</min-pool-size>
      
       <!-- The maximum connections in a pool/sub-pool -->
       <max-pool-size>20</max-pool-size>
      
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
       <!-- sql to call on an existing pooled connection when it is obtained from pool
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
      
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
       <metadata>
       <type-mapping>mySQL</type-mapping>
       </metadata>
       </local-tx-datasource>
      </datasources>
      
      



      In my application WEB_INF directory I did a web.xml like this:

      
      <web-app>
       <display-name>Apache-Axis</display-name>
      
       <servlet>
       <servlet-name>MyTest</servlet-name>
       <display-name>MyTest Servlet</display-name>
       <servlet-class>
       MyTest
       </servlet-class>
       </servlet>
      
       <servlet-mapping>
       <servlet-name>MyTest</servlet-name>
       <url-pattern>/MyTest</url-pattern>
       </servlet-mapping>
      
       <resource-ref>
       <description>The default MySQL DS</description>
       <res-ref-name>jdbc/MySQLDB</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <res-auth>Container</res-auth>
       </resource-ref>
      </web-app>
      
      



      and a jboss-web.xml

      
      <jboss-web>
       <!-- context-root>/axis</context-root -->
       <resource-ref>
       <res-ref-name>jdbc/MySQLDB</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <jndi-name>java:/MySQLDS</jndi-name>
       </resource-ref>
      </jboss-web>
      
      



      Those are the needed steps to configure a DS AFAIK.

      Then in my servlet I did:

      public class MyTest extends HttpServlet {
      
       public void doGet(HttpServletRequest request, HttpServletResponse response)
       throws ServletException {
       doPost(request, response);
       }
      
       public void doPost(HttpServletRequest request, HttpServletResponse response)
       throws ServletException {
       try {
       InitialContext ctx = new InitialContext();
      
       /*
       * Lookup the DataSource, which will be backed by a pool
       * that the application server provides. DataSource instances
       * are also a good candidate for caching as an instance
       * variable, as JNDI lookups can be expensive as well.
       */
      
       DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDB");
      
       /*
       * The following code is what would actually be in your
       * Servlet, JSP or EJB 'service' method...where you need
       * to work with a JDBC connection.
       */
      
       Connection conn = null;
       Statement stmt = null;
      
      
       conn = ds.getConnection();
      
       if (conn != null)
       System.out.println("Esta biennnn !!");
       else
       System.out.println("Pal toor !");
      
       } catch (Exception e) {
       throw new ServletException(e.getMessage());
       }
       }
      



      But I receive the error:

      javax.servlet.ServletException: Could not dereference object
       MyTest.doPost(MyTest.java:53)
       MyTest.doGet(MyTest.java:18)
       javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
       javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
       org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
      




      Could somebody tell what am I doing wrong ? I've RTFM several times but I can't get it working.... :(



      Thanks in advance

      J



        • 1. Re: How to use DB connection pool from DB ?
          Javier Leyba Newbie


          If I put a stacktrace I get:

          
          javax.naming.NamingException: Could not dereference object [Root exception is javax.naming.NameNotFoundException: MySQLDS not bound]
           at org.jnp.interfaces.NamingContext.resolveLink(NamingContext.java:1052)
           at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:685)
           at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:701)
           at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:572)
           at javax.naming.InitialContext.lookup(InitialContext.java:351)
           at MyTest.doPost(MyTest.java:33)
           at MyTest.doGet(MyTest.java:18)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
           at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
           at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
           at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
           at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
           at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
           at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
           at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
           at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
           at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
           at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
           at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
           at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
           at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
           at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
           at java.lang.Thread.run(Thread.java:595)
          Caused by: javax.naming.NameNotFoundException: MySQLDS not bound
           at org.jnp.server.NamingServer.getBinding(NamingServer.java:514)
           at org.jnp.server.NamingServer.getBinding(NamingServer.java:522)
           at org.jnp.server.NamingServer.getObject(NamingServer.java:528)
           at org.jnp.server.NamingServer.lookup(NamingServer.java:281)
           at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:610)
           at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:572)
           at javax.naming.InitialContext.lookup(InitialContext.java:351)
           at org.jnp.interfaces.NamingContext.resolveLink(NamingContext.java:1046)
           ... 27 more
          12:00:33,711 ERROR [[MyTest]] Servlet.service() for servlet MyTest threw exception
          javax.servlet.ServletException: Could not dereference object
           at MyTest.doPost(MyTest.java:54)
           at MyTest.doGet(MyTest.java:18)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
           at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
           at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
           at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
           at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
           at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
           at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextVa
          
          



          But when JBoss starts I can see:

          
          11:56:48,092 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=MySqlDS' to JNDI name 'java:MySqlDS'
          
          


          Then I really don't understand why my servlet couldn't see JNDI object.

          Help, pleaaase !


          J



          • 2. Re: How to use DB connection pool from DB ?
            Kurt De Wit Newbie

            Try by removing "java:comp/env" in the lookup...

            Regards,
            Kurt

            • 3. Re: How to use DB connection pool from DB ?
              Kurt De Wit Newbie

              Sorry, my mistake...

              The JBoss output states : MySqlDS
              Your deployment descriptor says : MySQLDS --> JNDI names are case sensitive...


              Regards,
              Kurt

              • 4. Re: How to use DB connection pool from DB ?
                Javier Leyba Newbie

                 

                "jboss2005_01" wrote:
                Sorry, my mistake...

                The JBoss output states : MySqlDS
                Your deployment descriptor says : MySQLDS --> JNDI names are case sensitive...


                Regards,
                Kurt



                Kurt

                It worked, thanks for your help !


                J





                • 5. Re: How to use DB connection pool from DB ?
                  Rajesh Swain Newbie

                  Hi,

                   

                  Is there any configuration in JBoss, which can make the jndi lookup case in sensitive ?

                  So that it will satisfy the following requirement.

                  The datasource created and deployed as jdbc/mysqldb , But I should be able to look up the jndi by jdbc/MYSQLDB, jdbc/mysqldb or jdbc/MySqlDb,so on...

                   

                  Rajesh Swain