using tomcat/jakarta connection pool

[developerinlondon]

I looked at the document below :

http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html

and setup the connection pool in the server.xml/web.xml as they described. However I cant seem to be able to figure out what exactly needs to be written in a servlet to use the specified connection using mySQL. Does anyone have an example code?

I tried the example that was given with Oracle but I get the class not found error for 'Context'.

n

 

[sedj]

There is an example in that link :

Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
Connection conn = ds.getConnection();

Context and InitialContext are part of the javax.naming package, so you need to import that.

A friendly word of advice - From your other post in the Java forum, it seems are are extrememly new to Java. Trying to set up tomcat, doing connection pooling and servlets is really not beginners stuff, and I really would advise learning the basics of Java before attempting to dive in at the deep end. Any person even faintly familiar with Java should be able to work out what a "ClassNotFoundException" means, and how it should be fixed. Without these basic debug skills you will find doing JDBC and servlet stuff extremely slow-going.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[developerinlondon]

thanks I wrote about that sample code in my post also.
so what do I import to get the DataSource?

 

[developerinlondon]

oh got it! its javax.sql.DataSource :=)

 

[developerinlondon]

ok i tried the example from apache's site but for some reason it seems its not reading the configuration off from server.xml properly. I am getting the exception : Cannot create JDBC driver of class '' for connect URL 'null'

snippet from web.xml

  <resource-ref>
    <description>jdbc:mysql://10.2.0.250:3306/xml_availability_dev [search on Default schema]</description>
    <res-ref-name>jdbc/myDatabase</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
  </resource-ref>

and from server.xml

   <Resource auth="Container" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" name="UserDatabase" pathname="conf/tomcat-users.xml" type="org.apache.catalina.UserDatabase"/>

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

  <ResourceParams name="jdbc/myDatabase">
    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>
   <!-- retrieve abandoned connections back to the pool -->
    <parameter>
      <name>removeAbandoned</name>
      <value>true</value>
    </parameter>

       <!-- Maximum number of dB connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to 0 for no limit.
         -->
    <parameter>
      <name>maxActive</name>
      <value>100</value>
    </parameter>

    <!-- Maximum number of idle dB connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->
    <parameter>
      <name>maxIdle</name>
      <value>30</value>
    </parameter>

    <!-- Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->
    <parameter>
      <name>maxWait</name>
      <value>10000</value>
    </parameter>

    <!-- MySQL dB username and password for dB connections  -->
    <parameter>
     <name>username</name>
     <value>*****</value>
    </parameter>
    <parameter>
     <name>password</name>
     <value>*****</value>
    </parameter>
    
    <!-- Class name for the official MySQL Connector/J driver -->
    <parameter>
       <name>driverClassName</name>
       <value>com.mysql.jdbc.Driver</value>
    </parameter>
    
    <!-- The JDBC connection url for connecting to your MySQL dB.
         The autoReconnect=true argument to the url makes sure that the
         mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
         connection.  mysqld by default closes idle connections after 8 hours.
         -->
    <parameter>
      <name>url</name>
      <value>jdbc:mysql://xx.xx.xx.xx:3306/xml_availability_dev?autoReconnect=true</value>
    </parameter>
  </ResourceParams>

 

[sedj]

where have you put your jdbc driver ?

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[developerinlondon]

I tried putting it in the WEB-INF/lib, tomcat's common/lib, JREs lib and ant's lib (since I am using netbeans which uses ant to deploy servlets). But I am getting the same error regardless.
I also tried putting in the <GlobalNamingResources> tag (which I really like to have it as in the end) with no luck.

 

[developerinlondon]

ps. Earlier I tried using the JDBC individually by something like the following -
String murl = "jdbc:mysql://xx.xx.xx.xx:3306/xml_availability_dev";

Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection ( murl, "search", "apache" );

and this works fine.

 

[developerinlondon]

OK. got it sorted now finally. Heres what I have in case anyone else runs into the same problem:

server.xml (inside <host>)

        <Context
            crossContext="true"
            docBase="C:/Documents and Settings/Nayeem/My Documents/EssexWeb7/nsm-build-dir/build/web"
            path="/nsmtest"
            reloadable="true">
          <Resource
              auth="Container"
              name="jdbc/myDatabase"
              type="javax.sql.DataSource"
              password="xxxxx"
              driverClassName="org.gjt.mm.mysql.Driver"
              maxIdle="2"
              maxWait="5000"
              username="xxxxx"
              url="jdbc:mysql://xx.xx.xx.xx:3306/xml_availability_dev?autoReconnect=true"
              maxActive="4"
              logAbandoned="true"
              removeAbandonedTimeout="60" />          
          <WatchedResource>WEB-INF/web.xml</WatchedResource>
        </Context>

web.xml (inside <web-app>)

  <resource-ref>
    <description>jdbc:mysql://xx.xx.xx.xx:3306/xml_availability_dev [search on Default schema]</description>
    <res-ref-name>jdbc/myDatabase</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
  </resource-ref>

it seems only thing I was missing was the

         <WatchedResource>WEB-INF/web.xml</WatchedResource>

in the context server.xml tag.

 

[developerinlondon]

OK My problem has popped up again. Before I was configuring it on windows but now I am doing it on linux.
The error I get is :
Cannot create JDBC driver of class '' for connect URL 'null'

So it seems it cant find the class, I put copies of the mysql+common classes in the jdk's lib, tomcat's common/lib, WEB-INF/lib but nothing seems to do the trick.

In my server.xml I have the following :

        <Context
            crossContext="true"
            docBase="/usr/local/tomcat-50/webapps/MultiFetcher"
            path="/MultiFetcher"
            reloadable="true">
          <Resource
              auth="Container"
              name="jdbc/myDatabase"
              type="javax.sql.DataSource"
              password="xxxx"
              driverClassName="org.gjt.mm.mysql.Driver"
              maxIdle="2"
              maxWait="5000"
              username="xxxx"
              url="jdbc:mysql://xx.xx.xx.xx:3306/xml_availability_dev?autoReconnect=true"
              maxActive="4"
              logAbandoned="true"
              removeAbandonedTimeout="60" />
          <WatchedResource>WEB-INF/web.xml</WatchedResource>
        </Context>

and in web.xml I have the following :

  <resource-ref>
    <description>jdbc connection</description>
    <res-ref-name>jdbc/myDatabase</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
  </resource-ref>

any ideas where I could be going wrong here?

 

[developerinlondon]

ok i found the problem again this time.
it seems with Tomcat 5.0 we need to pass the resource parameters in seperate "ResourceParams" tags. Tomcat 5 ignores anything extra in the Resource Tag like we put above. The above works fine on Tomcat 5.8. Strange it hasnt been documented anywhere.