commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Holly" <mho...@talisentech.com>
Subject RE: Re[2]: [DBCP] Configuring DBCP for Tomcat/Oracle
Date Fri, 20 Jun 2003 13:35:56 GMT
Frans

The web.xml is in the application's jar (see file below)
The server.xml belongs in $TOMCAT_HOME/conf  (nothing special here. the pool
is configured in the $APP.NAME.xml file)
A xml file called $APP.NAME.xml belongs in $TOMCAT_HOME/webapps This is the
tsr.xml file from below.

In $TOMCAT_HOME/common/lib put the following files.

commons-dbcp.jar
commons-pool.jar
commons-collections.jar

naming-common.jar
naming-factory.jar
naming-resources.jar


Below is a servlet to call it.  If you need more help checkout the following
resources

http://jakarta.apache.org/commons/dbcp/   The examples here aren't that
helpful, but this where you get the jars
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-how
to.html   very helpful

Notice that my url defined in the pool is odd.  This is to get a dedicated
connection an oracle MTS database.
Your URL will be more like the commented url.  Also note that the code for
getting the connection is not production
ready.  It just an example.

TestPool servlet ---------------------------------------------------
package net.myco.tsr.servlets;

import net.myco.tsr.StringUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.NamingEnumeration;
import org.apache.log4j.Logger;
import org.apache.commons.dbcp.BasicDataSource;


import java.util.ResourceBundle;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import org.apache.log4j.Logger;
import org.apache.log4j.Level;
import org.apache.log4j.PropertyConfigurator;

/**
 *  This servlet starts needed services when Tomcat starts
 *
 * @author  Michael Holly
 * created  Jan 18, 2002
 */
public class TestPoolServlet extends HttpServlet {

   //ResourceBundle props = ResourceBundle.getBundle("tsr");


   Logger log = Logger.getLogger(TestPoolServlet.class);

   /**
    * The doGet method calls the doPost method.
    *
    * @param aRequest The request to be handled.
    * @param aResponse The response to be sent back after processing the
request.
    */
   public void doGet( HttpServletRequest aRequest
                    , HttpServletResponse aResponse )
               throws ServletException, IOException
   {

      // doGet() calls doPost()
      doPost(aRequest, aResponse );

   }

   /**
    * The doPost method is the standard request handling method.
    *
    * @param aRequest The request to be handled.
    * @param aResponse The response to be sent back after processing the
request.
    */
   public void doPost( HttpServletRequest aRequest
                     , HttpServletResponse aResponse )
                throws ServletException, IOException
   {

      PrintWriter out = null;

      InitialContext initialContext = null;
      ResultSet resultSet = null;
      Context context = null;
      DataSource lDataSource = null;
      //BasicDataSource lDataSource = null;
      Connection connection = null;
      PreparedStatement preparedStatement = null;

      try
      {
         out = aResponse.getWriter();

         initialContext = new InitialContext();

         out.print("<HTML><BODY>");

         log.debug("TESTING Data connections "  );

         // this works
         Context context = (Context) initialContext.lookup("java:comp/env");
         DataSource lDataSource = (DataSource)
context.lookup("jdbc/oracle_tsr");


         out.print("<BR><BR>Initial Context Environment<BR><BR>");

         out.print(initialContext.getEnvironment().toString());


         out.print("<BR><BR><HR><BR><BR>");
         //out.print("<BR><BR>Context List<BR><BR>");

         //Enumeration list =
initialContext.list("java:comp/env/jdbc/oracle_tsr");


         out.print("DataSource is " + lDataSource.toString() + "<BR><BR>");


         out.print("<BR><BR><HR><BR><BR>");

         // throws SQLException
         connection = lDataSource.getConnection();

         out.print("Connection is " + lDataSource.toString());
         out.print("<BR><BR><HR><BR><BR>");


         log.debug("SQL = \n\n Select SYSADATE from dual; \n\n");
         preparedStatement = connection.prepareStatement("Select SYSDATE
from dual;");


         resultSet = preparedStatement.executeQuery();

         log.info( "\n    SQL command Select SYADATE from dual; ran. \n");

         ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

         out.print("SQL ran");


      }
      catch (Exception e)
      {
         out.print("Error");
         out.print(e.getMessage());
         out.print("<HR>");
         out.print("<BR><BR><B><U>Stack Trace</U></B><BR>");
         e.printStackTrace(out);
         out.print("<HR>");
         e.printStackTrace();
      }
      finally
      {
         out.print("</BODY></HTML>");

         try
         {
            if ( resultSet != null ) {
               resultSet.close();
            }

            if ( preparedStatement != null ) {
               preparedStatement.close();
            }

            if ( connection != null ) {
               connection.close();
            }
         }
         catch (java.sql.SQLException sqle)
         {
            // nothing
         }
      }
   }
}

TestPool servlet ---------------------------------------------------








-----Original Message-----
From: Frans Thamura [mailto:java@intercitra.com]
Sent: Friday, June 20, 2003 5:48 AM
To: Jakarta Commons Users List
Subject: Re[2]: [DBCP] Configuring DBCP for Tomcat/Oracle


how to use it??? do you can give me the sample code it Michael??

Thanks

Frans


>
> Hi Michael.
>
> Does it work when you've got the resource configured in the <context>
> element in your server.xml file?
>
> Like so:
> <Resource name="jdbc/JNDI_NAME" auth="Container"
>     type="javax.sql.DataSource"/>
>
> 	<ResourceParams name="jdbc/PORTAL_EINT">
>         <parameter>
>                <name>username</name>
>                <value>user</value>
>             </parameter>
>             <parameter>
>                <name>password</name>
>                <value>******</value>
>             </parameter>
>             <parameter>
>                <name>driverClassName</name>
>                <value>oracle.jdbc.driver.OracleDriver</value>
>             </parameter>
>             <parameter>
>                <name>url</name>
>               <value>jdbc:oracle:thin:@db.server.net:1528:RDB</value>
>             </parameter>
> 	    <parameter>
> 	      <name>maxActive</name>
> 	      <value>2</value>
> 	    </parameter>
> 	    <parameter>
> 	      <name>maxIdle</name>
> 	      <value>1</value>
> 	    </parameter>
> 	</ResourceParams>
>
> -----Original Message-----
> From: Michael Holly [mailto:mholly@talisentech.com]
> Sent: 19 Thursday June 2003 10:33
> To: Jakarta Commons Users List
> Subject: [DBCP] Configuring DBCP for Tomcat/Oracle
>
> I am running Tomcat 4.1.18, Java 1.3.1, Win 2k
>
> Part of getting on automated testing(junit et. al.)  was getting
> the ANT build to undeploy and then redeploy the war after it got created.
>
> Part of getting this dynamic undeploy/deploy cycle to work was
> removing the <context> from the server.xml. The reason I want to
> do this is I have 2 connection pools located there. In a dynamic
> deploy tomcat generates it's own context for the app. I found there
> were three ways I could do this.
>
> #1. put the connection pool into the <GlobalNamingResources> section
> of server.xml
>
> #2. create a $APP_NAME.xml file that contains the context of the app
> and put it into $TOMCAT_HOME/webapps. Both the Tomcat manager and
> Admin apps do this.
>
> #3. Possibly put the context info in the <defaultContext> section for the
> server.
>
> I have tried both #1 and #2 and I am getting a
> java.sql.SQLException: Cannot load JDBC driver class 'null' error
> when the DBCP is accessed. The classes12.jar file is located in
> $TOMCAT_HOME/common/lib. It has been renamed from .zip to .jar
>
> Some things i have tried:
> I have tried:
> 1. config #1,
> 2. config #2,
> 3. replacing the
>       <parameter>
>       <name>driverClassName</name>
>       <value>oracle.jdbc.OracleDriver</value>
>       </parameter>
>    with
>       <parameter>
>       <name>driverClassName</name>
> <value>oracle.jdbc.pool.OracleConnectionPoolDataSource</value>
>       </parameter>
>
> Thess did not help
>
> Some Observations.
> 1. The app seems to deploy correctly. ( I can see app in Tomcat manager )
>
> 2. When I alter the JNDI name(adding and 'x' to the end) I get a
> javax.naming.NameNotFoundException: Name oracle_tsrx is not bound in this
> Context error. So from this it appears that the Context is being created
but
> something in classes12.jar is not being found or referenced properly.
>
> 3. Before I went to this new configuration, this worked with a hard coded
> <context> in the server.xml that contained the references for the pools.
>
> Currently the classes12.jar is located in $TOMCAT_HOME/common/lib. Is this
> the correct location for this class?
>
> Are all jars in $TOMCAT_HOME/common/lib automagically referencable?  I
> understand that the pool classes need to be here because they are
referenced
> by both the app and the container.  Is there any way for me to iterate
> through a context and see what it's properties/parametes/resources are?
>
> As you can tell from the included files I am using configuring a
connection
> pool for two different connections. Below are my files for Option #2 (the
> one I would like to go with).
>
>
>
WEB.XML---------------------------------------------------------------------
> ----------------
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application
> 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
>
> <web-app>
> <display-name>TSR Application</display-name>
> <description>This is the web configuration for the TSR
> application</description>
>
> <!-- SERVLET LISTINGS -->
> <servlet>
> <servlet-name>TestServlet</servlet-name>
> <servlet-class>net.talisen.tsr.servlets.TestServlet</servlet-class>
> </servlet>
>
> <servlet>
> <servlet-name>ControllerServlet</servlet-name>
> <servlet-class>net.talisen.tsr.servlets.ControllerServlet</servlet-class>
> </servlet>
>
> <servlet>
> <servlet-name>FileDownloadServlet</servlet-name>
>
<servlet-class>net.talisen.tsr.servlets.FileDownloadServlet</servlet-class>
> </servlet>
>
> <servlet>
> <servlet-name>StartupServlet</servlet-name>
> <servlet-class>net.talisen.tsr.servlets.StartupServlet</servlet-class>
> <load-on-startup>1</load-on-startup>
> </servlet>
>
> <!--servlet>
> <servlet-name>log4j-init</servlet-name>
> <servlet-class>net.talisen.tsr.servlets.Log4jInit</servlet-class>
> <load-on-startup>1</load-on-startup>
> <init-param>
> <param-name>log4j-init-file</param-name>
> <param-value>WEB-INF\classes\log4j.properties</param-value>
> </init-param>
> </servlet-->
>
> <!-- integrate the testing -->
> <servlet>
> <servlet-name>JUnitEETestServlet</servlet-name>
> <description>JUnitEE test runner</description>
> <servlet-class>org.junitee.servlet.JUnitEEServlet</servlet-class>
> </servlet>
>
> <!-- SERVLET MAPPINGS -->
> <servlet-mapping>
> <servlet-name>TestServlet</servlet-name>
> <url-pattern>/test</url-pattern>
> </servlet-mapping>
>
> <servlet-mapping>
> <servlet-name>StartupServlet</servlet-name>
> <url-pattern>/startup</url-pattern>
> </servlet-mapping>
>
> <servlet-mapping>
> <servlet-name>ControllerServlet</servlet-name>
> <url-pattern>/controller</url-pattern>
> </servlet-mapping>
>
> <servlet-mapping>
> <servlet-name>FileDownloadServlet</servlet-name>
> <url-pattern>/download</url-pattern>
> </servlet-mapping>
>
> <!-- integrate the testing -->
> <servlet-mapping>
> <servlet-name>JUnitEETestServlet</servlet-name>
> <url-pattern>/TestServlet/*</url-pattern>
> </servlet-mapping>
>
> <!-- JNDI resource for DB connection pool -->
> <resource-ref>
> <description>
> Resource reference to a factory for java.sql.Connection
> instances that may be used for talking to a particular
> database that is configured in the server.xml file.
> </description>
>
> <res-ref-name>
> jdbc/oracle_tsr
> </res-ref-name>
> <res-type>
> javax.sql.DataSource
> </res-type>
> <res-auth>
> Container
> </res-auth>
> </resource-ref>
>
>
> <!-- JNDI resource for DB connection pool -->
> <resource-ref>
> <description>
> Resource reference to a factory for java.sql.Connection
> instances that may be used for talking to a particular
> database that is configured in the server.xml file.
> </description>
>
> <res-ref-name>
> jdbc/oracle_myco
> </res-ref-name>
> <res-type>
> javax.sql.DataSource
> </res-type>
> <res-auth>
> Container
> </res-auth>
> </resource-ref>
> </web-app>
>
WEB.XML---------------------------------------------------------------------
> ----------------
>
>
>
TSR.XML---------------------------------------------------------------------
> ----------------
> <Context path="/tsr" docBase="tsr.war" debug="3" reloadable="true"
> crossContext="true">
> <Loader checkInterval="6"/>
> <Logger className="org.apache.catalina.logger.FileLogger"
> prefix="localhost_tsrdb_log."
> suffix=".txt"
> timestamp="true"/>
>
> <Resource name="jdbc/oracle_tsr" auth="Container"
> type="javax.sql.DataSource"/>
>
> <ResourceParams name="jdbc/oracle_tsr">
> <parameter>
> <name>factory</name>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>driverClassName</name>
> <value>oracle.jdbc.OracleDriver</value>
> </parameter>
> <parameter>
> <name>url</name>
> <!--value>jdbc:oracle:thin:@myserver:1521:myco</value-->
> <value>jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL
> = TCP)(HOST = myserver.myco.com)(PORT = 1521))) (CONNECT_DATA = (SERVER =
> DEDICATED) (SID = MYCO)))</value>
> </parameter>
> <parameter>
> <name>username</name>
> <value>tsr_app</value>
> </parameter>
> <parameter>
> <name>password</name>
> <value>actuator</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>20</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>10</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>-1</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select 'validationQuery' from dual</value>
> </parameter>
> <parameter>
> <name>testOnBorrow</name>
> <value>true</value>
> </parameter>
> </ResourceParams>
>
> <Resource name="jdbc/oracle_talisen" auth="Container"
> type="javax.sql.DataSource"/>
>
> <ResourceParams name="jdbc/oracle_myco">
> <parameter>
> <name>factory</name>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>driverClassName</name>
> <value>oracle.jdbc.driver.OracleDriver</value>
> </parameter>
> <parameter>
> <name>url</name>
> <!--value>jdbc:oracle:thin:@myserver:1521:myco</value-->
> <value>jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL
> = TCP)(HOST = myserver.myco.com)(PORT = 1521))) (CONNECT_DATA = (SERVER =
> DEDICATED) (SID = MYCO)))</value>
> </parameter>
> <parameter>
> <name>username</name>
> <value>myname</value>
> </parameter>
> <parameter>
> <name>password</name>
> <value>mypass</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>20</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>10</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>-1</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select 'validationQuery' from dual</value>
> </parameter>
> <parameter>
> <name>testOnBorrow</name>
> <value>true</value>
> </parameter>
> </ResourceParams>
> </Context>
>
TSR.XML---------------------------------------------------------------------
> ----------------
>
>
> My log even shows the look up of the datasource in the context
>
> 2003-06-13 13:42:51,009 DEBUG tsr.DBCmd 208 - Looking up db
> 2003-06-13 13:42:51,009 DEBUG tsr.DBCmd 214 - Getting connection to
> java:/comp/env/jdbc/oracle_tsr
> 2003-06-13 13:42:51,019 ERROR tsr.DBCmd 234 - SQL EXCEPTIONCannot load
JDBC
> driver class 'null'
>
> java.sql.SQLException: Cannot load JDBC driver class 'null'
> at
>
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.jav
> a:529)
> at
>
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:3
> 12)
> at net.talisen.tsr.DBCmd.open(DBCmd.java:219)
> at net.talisen.tsr.DBCmd.open(DBCmd.java:186)
> at net.talisen.tsr.model.customer.Customer.<init>(Customer.java:70)
> at
>
test.talisen.tsr.model.customer.CustomerTest.testCustomer(CustomerTest.java:
> 74)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
>
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
> )
> at
>
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
> .java:25)
> at java.lang.reflect.Method.invoke(Method.java:324)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at junit.framework.TestResult$1.protect(TestResult.java:106)
> at junit.framework.TestResult.runProtected(TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at org.junitee.runner.TestRunner$1.run(TestRunner.java:72)
> at org.junitee.runner.TestRunner.run(TestRunner.java:95)
> at org.junitee.servlet.JUnitEEServlet.runTests(JUnitEEServlet.java:224)
> at org.junitee.servlet.JUnitEEServlet.doGet(JUnitEEServlet.java:195)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
> FilterChain.java:247)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
> ain.java:193)
> at
>
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
> va:260)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.
> java:643)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> at
>
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
> va:191)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.
> java:643)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> at
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
> at
>
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
> )
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.
> java:643)
> at
>
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
> java:170)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.
> java:641)
> at
>
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
> )
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.
> java:641)
> at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:509)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.
> java:641)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> at
>
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
> :174)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.
> java:643)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> at
>
org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:
> 1040)
> at
>
org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1151
> )
> at java.lang.Thread.run(Thread.java:536)
> 2003-06-13 13:42:51,179 ERROR customer.Customer 139 - Customer was not
able
> to be added.
>
> Sorry for the HUGE post.
> Thanks for the help
>
> Michael
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>

--
Frans Thamura <java@intercitra.com>
Intercitra Innovation Center
+62 855 7888 699

We help you manage and control.



---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message