# Trouble with Tomcat5 and jdbc

## memborg

Mjallo

I'm havin trouble getting my JAVA page loading my jdbc mydql driver (Connector/J). It seems to be a problem with Tomcat5 and not my coding skill. Maybe i have forgot som settings in the server.xml file.

Server.xml

```

<!-- A "Server" is a singleton element that represents the entire JVM,

     which may contain one or more "Service" instances.  The Server

     listens for a shutdown command on the indicated port.

     Note:  A "Server" is not itself a "Container", so you may not

     define subcomponents such as "Valves" or "Loggers" at this level.

 -->

<Server port="8005" shutdown="SHUTDOWN" debug="0">

  <!-- Comment these entries out to disable JMX MBeans support -->

  <!-- You may also configure custom components (e.g. Valves/Realms) by 

       including your own mbean-descriptor file(s), and setting the 

       "descriptors" attribute to point to a ';' seperated list of paths

       (in the ClassLoader sense) of files to add to the default list.

       e.g. descriptors="/com/myfirm/mypackage/mbean-descriptor.xml"

  -->

  <Listener className="org.apache.catalina.mbeans.ServerLifecycleListener"

            debug="0"/>

  <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"

            debug="0"/>

  <!-- Global JNDI resources -->

  <GlobalNamingResources>

    <!-- Test entry for demonstration purposes -->

    <Environment name="simpleValue" type="java.lang.Integer" value="30"/>

    <!-- Editable user database that can also be used by

         UserDatabaseRealm to authenticate users -->

    <Resource name="UserDatabase" auth="Container"

              type="org.apache.catalina.UserDatabase"

       description="User database that can be updated and saved">

    </Resource>

    <ResourceParams name="UserDatabase">

      <parameter>

        <name>factory</name>

        <value>org.apache.catalina.users.MemoryUserDatabaseFactory</value>

      </parameter>

      <parameter>

        <name>pathname</name>

        <value>conf/tomcat-users.xml</value>

      </parameter>

    </ResourceParams>

  </GlobalNamingResources>

  <!-- A "Service" is a collection of one or more "Connectors" that share

       a single "Container" (and therefore the web applications visible

       within that Container).  Normally, that Container is an "Engine",

       but this is not required.

       Note:  A "Service" is not itself a "Container", so you may not

       define subcomponents such as "Valves" or "Loggers" at this level.

   -->

  <!-- Define the Tomcat Stand-Alone Service -->

  <Service name="Catalina">

    <!-- A "Connector" represents an endpoint by which requests are received

         and responses are returned.  Each Connector passes requests on to the

         associated "Container" (normally an Engine) for processing.

         By default, a non-SSL HTTP/1.1 Connector is established on port 8080.

         You can also enable an SSL HTTP/1.1 Connector on port 8443 by

         following the instructions below and uncommenting the second Connector

         entry.  SSL support requires the following steps (see the SSL Config

         HOWTO in the Tomcat 5 documentation bundle for more detailed

         instructions):

         * If your JDK version 1.3 or prior, download and install JSSE 1.0.2 or

           later, and put the JAR files into "$JAVA_HOME/jre/lib/ext".

         * Execute:

             %JAVA_HOME%\bin\keytool -genkey -alias tomcat -keyalg RSA (Windows)

             $JAVA_HOME/bin/keytool -genkey -alias tomcat -keyalg RSA  (Unix)

           with a password value of "changeit" for both the certificate and

           the keystore itself.

         By default, DNS lookups are enabled when a web application calls

         request.getRemoteHost().  This can have an adverse impact on

         performance, so you can disable it by setting the

         "enableLookups" attribute to "false".  When DNS lookups are disabled,

         request.getRemoteHost() will return the String version of the

         IP address of the remote client.

    -->

    <!-- Define a non-SSL Coyote HTTP/1.1 Connector on port 8080 -->

    <Connector port="8080"

               maxThreads="150" minSpareThreads="25" maxSpareThreads="75"

               enableLookups="false" redirectPort="8443" acceptCount="100"

               debug="0" connectionTimeout="20000" 

               disableUploadTimeout="true" />

    <!-- Note : To disable connection timeouts, set connectionTimeout value

     to 0 -->

   

   <!-- Note : To use gzip compression you could set the following properties :

   

            compression="on" 

            compressionMinSize="2048" 

            noCompressionUserAgents="gozilla, traviata" 

            compressableMimeType="text/html,text/xml"

   -->

    <!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->

    <!--

    <Connector port="8443" 

               maxThreads="150" minSpareThreads="25" maxSpareThreads="75"

               enableLookups="false" disableUploadTimeout="true"

               acceptCount="100" debug="0" scheme="https" secure="true"

               clientAuth="false" sslProtocol="TLS" />

    -->

    <!-- Define a Coyote/JK2 AJP 1.3 Connector on port 8009 -->

    <Connector port="8009" 

               enableLookups="false" redirectPort="8443" debug="0"

               protocol="AJP/1.3" />

    <!-- Define a Proxied HTTP/1.1 Connector on port 8082 -->

    <!-- See proxy documentation for more information about using this. -->

    <!--

    <Connector port="8082" 

               maxThreads="150" minSpareThreads="25" maxSpareThreads="75"

               enableLookups="false"

               acceptCount="100" debug="0" connectionTimeout="20000"

               proxyPort="80" disableUploadTimeout="true" />

    -->

    <!-- An Engine represents the entry point (within Catalina) that processes

         every request.  The Engine implementation for Tomcat stand alone

         analyzes the HTTP headers included with the request, and passes them

         on to the appropriate Host (virtual host). -->

    <!-- You should set jvmRoute to support load-balancing via JK/JK2 ie :

    <Engine name="Standalone" defaultHost="localhost" debug="0" jvmRoute="jvm1">         

    --> 

         

    <!-- Define the top level container in our container hierarchy -->

    <Engine name="Catalina" defaultHost="localhost" debug="0">

      <!-- The request dumper valve dumps useful debugging information about

           the request headers and cookies that were received, and the response

           headers and cookies that were sent, for all requests received by

           this instance of Tomcat.  If you care only about requests to a

           particular virtual host, or a particular application, nest this

           element inside the corresponding <Host> or <Context> entry instead.

           For a similar mechanism that is portable to all Servlet 2.4

           containers, check out the "RequestDumperFilter" Filter in the

           example application (the source for this filter may be found in

           "$CATALINA_HOME/webapps/examples/WEB-INF/classes/filters").

           Request dumping is disabled by default.  Uncomment the following

           element to enable it. -->

      <!--

      <Valve className="org.apache.catalina.valves.RequestDumperValve"/>

      -->

      <!-- Global logger unless overridden at lower levels -->

      <Logger className="org.apache.catalina.logger.FileLogger"

              prefix="catalina_log." suffix=".txt"

              timestamp="true"/>

      <!-- Because this Realm is here, an instance will be shared globally -->

      <!-- This Realm uses the UserDatabase configured in the global JNDI

           resources under the key "UserDatabase".  Any edits

           that are performed against this UserDatabase are immediately

           available for use by the Realm.  -->

      <Realm className="org.apache.catalina.realm.UserDatabaseRealm"

                 debug="0" resourceName="UserDatabase"/>

      <!-- Comment out the old realm but leave here for now in case we

           need to go back quickly -->

      <!--

      <Realm className="org.apache.catalina.realm.MemoryRealm" />

      -->

      <!-- Replace the above Realm with one of the following to get a Realm

           stored in a database and accessed via JDBC -->

      <!--

      <Realm  className="org.apache.catalina.realm.JDBCRealm" debug="99"

             driverName="org.gjt.mm.mysql.Driver"

          connectionURL="jdbc:mysql://localhost/authority"

         connectionName="test" connectionPassword="test"

              userTable="users" userNameCol="user_name" userCredCol="user_pass"

          userRoleTable="user_roles" roleNameCol="role_name" />

      -->

      <!--

      <Realm  className="org.apache.catalina.realm.JDBCRealm" debug="99"

             driverName="oracle.jdbc.driver.OracleDriver"

          connectionURL="jdbc:oracle:thin:@ntserver:1521:ORCL"

         connectionName="scott" connectionPassword="tiger"

              userTable="users" userNameCol="user_name" userCredCol="user_pass"

          userRoleTable="user_roles" roleNameCol="role_name" />

      -->

      <!--

      <Realm  className="org.apache.catalina.realm.JDBCRealm" debug="99"

             driverName="sun.jdbc.odbc.JdbcOdbcDriver"

          connectionURL="jdbc:odbc:CATALINA"

              userTable="users" userNameCol="user_name" userCredCol="user_pass"

          userRoleTable="user_roles" roleNameCol="role_name" />

      -->

      <!-- Define the default virtual host

           Note: XML Schema validation will not work with Xerces 2.2.

       -->

      <Host name="localhost" debug="0" appBase="webapps"

       unpackWARs="true" autoDeploy="true"

       xmlValidation="false" xmlNamespaceAware="false">

        <!-- Defines a cluster for this node,

             By defining this element, means that every manager will be changed.

             So when running a cluster, only make sure that you have webapps in there

             that need to be clustered and remove the other ones.

             A cluster has the following parameters:

             className = the fully qualified name of the cluster class

             name = a descriptive name for your cluster, can be anything

             debug = the debug level, higher means more output

             mcastAddr = the multicast address, has to be the same for all the nodes

             mcastPort = the multicast port, has to be the same for all the nodes

             

             mcastBindAddr = bind the multicast socket to a specific address

             

             mcastTTL = the multicast TTL if you want to limit your broadcast

             

             mcastSoTimeout = the multicast readtimeout 

             mcastFrequency = the number of milliseconds in between sending a "I'm alive" heartbeat

             mcastDropTime = the number a milliseconds before a node is considered "dead" if no heartbeat is received

             tcpThreadCount = the number of threads to handle incoming replication requests, optimal would be the same amount of threads as nodes 

             tcpListenAddress = the listen address (bind address) for TCP cluster request on this host, 

                                in case of multiple ethernet cards.

                                auto means that address becomes

                                InetAddress.getLocalHost().getHostAddress()

             tcpListenPort = the tcp listen port

             tcpSelectorTimeout = the timeout (ms) for the Selector.select() method in case the OS

                                  has a wakup bug in java.nio. Set to 0 for no timeout

             printToScreen = true means that managers will also print to std.out

             expireSessionsOnShutdown = true means that 

             useDirtyFlag = true means that we only replicate a session after setAttribute,removeAttribute has been called.

                            false means to replicate the session after each request.

                            false means that replication would work for the following piece of code:

                            <%

                            HashMap map = (HashMap)session.getAttribute("map");

                            map.put("key","value");

                            %>

             replicationMode = can be either 'pooled', 'synchronous' or 'asynchronous'.

                               * Pooled means that the replication happens using several sockets in a synchronous way. Ie, the data gets replicated, then the request return. This is the same as the 'synchronous' setting except it uses a pool of sockets, hence it is multithreaded. This is the fastest and safest configuration. To use this, also increase the nr of tcp threads that you have dealing with replication.

                               * Synchronous means that the thread that executes the request, is also the

                               thread the replicates the data to the other nodes, and will not return until all

                               nodes have received the information.

                               * Asynchronous means that there is a specific 'sender' thread for each cluster node,

                               so the request thread will queue the replication request into a "smart" queue,

                               and then return to the client.

                               The "smart" queue is a queue where when a session is added to the queue, and the same session

                               already exists in the queue from a previous request, that session will be replaced

                               in the queue instead of replicating two requests. This almost never happens, unless there is a 

                               large network delay.

        -->             

        <!--

            When configuring for clustering, you also add in a valve to catch all the requests

            coming in, at the end of the request, the session may or may not be replicated.

            A session is replicated if and only if all the conditions are met:

            1. useDirtyFlag is true or setAttribute or removeAttribute has been called AND

            2. a session exists (has been created)

            3. the request is not trapped by the "filter" attribute

            The filter attribute is to filter out requests that could not modify the session,

            hence we don't replicate the session after the end of this request.

            The filter is negative, ie, anything you put in the filter, you mean to filter out,

            ie, no replication will be done on requests that match one of the filters.

            The filter attribute is delimited by ;, so you can't escape out ; even if you wanted to.

            filter=".*\.gif;.*\.js;" means that we will not replicate the session after requests with the URI

            ending with .gif and .js are intercepted.

            

            The deployer element can be used to deploy apps cluster wide.

            Currently the deployment only deploys/undeploys to working members in the cluster

            so no WARs are copied upons startup of a broken node.

            The deployer watches a directory (watchDir) for WAR files when watchEnabled="true"

            When a new war file is added the war gets deployed to the local instance,

            and then deployed to the other instances in the cluster.

            When a war file is deleted from the watchDir the war is undeployed locally 

            and cluster wide

        -->

        

        <!--

        <Cluster className="org.apache.catalina.cluster.tcp.SimpleTcpCluster"

                 managerClassName="org.apache.catalina.cluster.session.DeltaManager"

                 expireSessionsOnShutdown="false"

                 useDirtyFlag="true">

            <Membership 

                className="org.apache.catalina.cluster.mcast.McastService"

                mcastAddr="228.0.0.4"

                mcastPort="45564"

                mcastFrequency="500"

                mcastDropTime="3000"/>

            <Receiver 

                className="org.apache.catalina.cluster.tcp.ReplicationListener"

                tcpListenAddress="auto"

                tcpListenPort="4001"

                tcpSelectorTimeout="100"

                tcpThreadCount="6"/>

            <Sender

                className="org.apache.catalina.cluster.tcp.ReplicationTransmitter"

                replicationMode="pooled"/>

            <Valve className="org.apache.catalina.cluster.tcp.ReplicationValve"

                   filter=".*\.gif;.*\.js;.*\.jpg;.*\.htm;.*\.html;.*\.txt;"/>

                   

            <Deployer className="org.apache.catalina.cluster.deploy.FarmWarDeployer"

                      tempDir="/tmp/war-temp/"

                      deployDir="/tmp/war-deploy/"

                      watchDir="/tmp/war-listen/"

                      watchEnabled="false"/>

        </Cluster>

        -->        

        <!-- Normally, users must authenticate themselves to each web app

             individually.  Uncomment the following entry if you would like

             a user to be authenticated the first time they encounter a

             resource protected by a security constraint, and then have that

             user identity maintained across *all* web applications contained

             in this virtual host. -->

        <!--

        <Valve className="org.apache.catalina.authenticator.SingleSignOn"

                   debug="0"/>

        -->

        <!-- Access log processes all requests for this virtual host.  By

             default, log files are created in the "logs" directory relative to

             $CATALINA_HOME.  If you wish, you can specify a different

             directory with the "directory" attribute.  Specify either a relative

             (to $CATALINA_HOME) or absolute path to the desired directory.

        -->

        <!--

        <Valve className="org.apache.catalina.valves.AccessLogValve"

                 directory="logs"  prefix="localhost_access_log." suffix=".txt"

                 pattern="common" resolveHosts="false"/>

        -->

        <!-- Logger shared by all Contexts related to this virtual host.  By

             default (when using FileLogger), log files are created in the "logs"

             directory relative to $CATALINA_HOME.  If you wish, you can specify

             a different directory with the "directory" attribute.  Specify either a

             relative (to $CATALINA_HOME) or absolute path to the desired

             directory.-->

        <Logger className="org.apache.catalina.logger.FileLogger"

                 directory="logs"  prefix="localhost_log." suffix=".txt"

            timestamp="true"/>

   <Context path="/DBTest" docBase="DBTest" debug="5" reloadable="true" crossContext="true">

   

  <Logger className="org.apache.catalina.logger.FileLogger"

             prefix="localhost_DBTest_log." suffix=".txt"

             timestamp="true"/>

  <Resource name="jdbc/TestDB"

               auth="Container"

               type="javax.sql.DataSource"/>

  <ResourceParams name="jdbc/TestDB">

    <parameter>

      <name>factory</name>

      <value>org.apache.commons.dbcp.BasicDataSourceFactory</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 0 for no limit.

         -->

    <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>javauser</value>

    </parameter>

    <parameter>

     <name>password</name>

     <value>javadude</value>

    </parameter>

    <!-- Class name for the old mm.mysql JDBC driver - uncomment this entry and comment next

         if you want to use this driver - we recommend using Connector/J though  

    <parameter>

       <name>driverClassName</name>

       <value>org.gjt.mm.mysql.Driver</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://localhost:3306/javatest?autoReconnect=true</value>

    </parameter>

  </ResourceParams>

   </Context>

      </Host>

    </Engine>

  </Service>

</Server>

```

Java code

```

package com;

import java.sql.*;

import java.util.*;

public class scrap

{

   String error;

   Connection con;

   

   public scrap(){}

   

   public void connect() throws ClassNotFoundException, SQLException, Exception

   {

      try{

         Class.forName("com.mysql.jdbc.Driver").newInstance();

         con = DriverManager.getConnection("jdbc:mysql//localhost/memborg?--user=user --password=userpass");

      }

      catch(ClassNotFoundException cnfe){error = "Could not locate Database driver"; throw new ClassNotFoundException(error);}

      catch(SQLException cnfe){error = "Could not connect to database"; throw new SQLException(error);}

      catch(Exception e){error = "An unknown error occured"; throw new Exception(error);}

   }

   

   public void disconnect() throws SQLException

   {

      try{

         if(con != null)

         {

            con.close();

         }

      }

      catch(SQLException sqle)

      {error = "Could not connect to database"; throw new SQLException(error);}

   }

   

   public ResultSet visScrap(String tmp) throws SQLException, Exception

   {

      ResultSet rs = null;

      try{

         String hent = "select * From billeder, scrapbog where dato='"+tmp+"' and billder.scrap_id = scrapbog.scrap_id";

         Statement stmt = con.createStatement();

         rs = stmt.executeQuery(hent);

      }

      catch(SQLException sqle){error="Could no execute request"; throw new SQLException(error);}

      catch(Exception e){error="An unknown error occured"; throw new Exception(error);}

      return rs;

   }

}

```

Hope some one can figure this out.

Memborg

----------

## DarrenM

Is the mysql jdbc jar in the classpath so that tomcat can find it?

What errors do you get in your program and are there any errors in Tomcats logs?

----------

## memborg

here's my output from starting tomcat5

```

* Starting Tomcat...

Using CATALINA_BASE:   /opt/tomcat5

Using CATALINA_HOME:   /opt/tomcat5

Using CATALINA_TMPDIR: /opt/tomcat5/temp

Using JAVA_HOME:       /opt/sun-jdk-1.4.2.05                              [ ok ]

```

And here's my CLASSPATH

```

/usr/share/connectorj-3/lib/mysql-connector-java-3.0.14-production-bin.jar:/usr/share/commons-fileupload/lib/commons-fileupload.jar:.

```

There's no errors in my tomcat log.

I'm kinda lost here. Hopes this can help  :Smile: 

----------

## DarrenM

catch(ClassNotFoundException cnfe){error = "Could not locate Database driver"; throw new ClassNotFoundException(error);} 

       catch(SQLException cnfe){error = "Could not connect to database"; throw new SQLException(error);} 

       catch(Exception e){error = "An unknown error occured"; throw new Exception(error);} 

Which of those gets thrown or does your program fail somewhere else? What exactly is going wrong?

Have you looked through the documentation?

http://dev.mysql.com/doc/connector/j/en/

----------

## memborg

"Could not locate Database driver" is thrown...

----------

## brianshowalter

 *memborg wrote:*   

> here's my output from starting tomcat5
> 
> ```
> 
> * Starting Tomcat...
> ...

 

You might want to move that mysql-connector JAR to the /opt/tomcat5/shared/lib directory so Tomcat can find it when it starts up.  JAR's in that directory will also be shared with your webapps.

----------

## memborg

OK... Well that's duplicating the source... If it works, then it have to like that...

Is it possible to do it with a symbolic link... That would be nice...

BTW are all JAVA servers working like that?

----------

## psylo

Or you can put the jar file in the WEB-INF/lib of your project...

Anyway, why do you declare a DataSource in your server.xml? 

With Tomcat 5.x, you can declare it in a context.xml wich is in /META-INF in your project. Here is mine for a JSP Project with Firebird:

```
<Context path="/IPSMS" reloadable="true" crossContext="true" debug="1" >

  <Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_DBTest_log." suffix=".txt" timestamp="true"/>

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

  <ResourceParams name="jdbc/TestDB">

    <parameter>

      <name>factory</name>

      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>

    </parameter>

    <parameter>

      <name>driverClassName</name>

      <value>org.firebirdsql.jdbc.FBDriver</value>

    </parameter>

    <parameter>

      <name>url</name>

      <value>jdbc:firebirdsql:localhost/3050:/Data/FirebirdDB/IPSMS/IPSMS.FDB</value>

    </parameter>

    <parameter>

      <name>username</name>

      <value>IPSMSADMIN</value>

    </parameter>

    <parameter>

      <name>password</name>

      <value>********</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>

  </ResourceParams>

</Context>
```

And in my /WEB-INF/web.xml, I've added this:

```

  <resource-ref>

    <description>Firebird Datasource</description>

    <res-ref-name>jdbc/TestDB</res-ref-name>

    <res-type>javax.sql.DataSource</res-type>

    <res-auth>Container</res-auth>

  </resource-ref>

```

And finally for my jdbc connection, I use the following code:

```

Connection con;

Context initContext = new InitialContext();

Context envContext  = (Context)initContext.lookup("java:/comp/env");

DataSource ds = (DataSource)envContext.lookup(contextName);

con = ds.getConnection();
```

Hope this helps.

[EDIT]: I forgot to tell you the value of the String contextName:

"jdbc/TestDB" as declared in my web.xml.

----------

## memborg

OK... So I just declare the database acordingly to the project and not to server then?

Well i thought declaring the Datasource in the server.xml would work and then i didn't have to this  more than once. but it didn't work. 

The other reason was i was dusting up an old book about JSP developement, and this were done that way.

But if your suggestion makes it all come alive, it would be nice.

I will work on it over the weekend an d come back with result  :Smile: 

PS. I'm still open for suggestions. My JAVA server skills are kinda dusty.

----------

## memborg

Well now it seems to find my database correctly. THANX!!

One other thing...

Is this where you tell which database your are going to use and what are the parameters for it?  :Smile: 

```
Context envContext  = (Context)initContext.lookup("java:/comp/env");
```

Everytime you knockout one problem a new arises  :Smile: 

THANX for the help so far  :Smile: 

----------

## psylo

No.

All the option about the database use are set in the context.xml file.

```
Context envContext  = (Context)initContext.lookup("java:/comp/env");
```

This is used to tell my project to load context.

```
DataSource ds = (DataSource)envContext.lookup("jdbc/TestDB");
```

This is used to tell my project to load the Datasource declared in my context.xml (or web.xml... I'm not sure about this).

```
DataSource ds = (DataSource)envContext.lookup(contextName);
```

This is used to get a connexion with database.

Hope this helps.

You can go there to have a little more help about Datasource:

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

----------

## memborg

It does helps..

Now i will fiddle with it...

----------

## brianshowalter

 *psylo wrote:*   

> Or you can put the jar file in the WEB-INF/lib of your project...
> 
> Anyway, why do you declare a DataSource in your server.xml? 
> 
> With Tomcat 5.x, you can declare it in a context.xml wich is in /META-INF in your project.

 

Yup, that's another one of several ways to do it.  The reason to declare a DataSource in server.xml is because it lets you have just one place where database access credentials are maintained, and then all webapps can use the same DataSource.  This is particularly handy if you're pooling connections to a database.  Of course, this sort of solution isn't for everyone, as there may be valid reasons for having a webapp set up and use its own DataSource.

In my Tomcat 5 environment, I set up the DataSource in the default context so it's shared among all webapps, as follows:

In server.xml:

```
<DefaultContext useNaming="true">

     <Resource name="jdbc/pg"

          type="org.postgresql.jdbc3.Jdbc3PoolingDataSource"

          auth="container"

          description="PostgreSQL Data Source"/>

     <ResourceParams name="jdbc/pg">

          <parameter>

               <name>factory</name>

               <value>org.postgresql.jdbc3.Jdbc3ObjectFactory</value>

          </parameter>

          <parameter>

               <name>serverName</name>

               <value>db.example.com</value>

          </parameter>

          <parameter>

               <name>databaseName</name>

               <value>intranet</value>

          </parameter>

          <parameter>

               <name>user</name>

               <value>dbUser</value>

          </parameter>

          <parameter>

               <name>password</name>

               <value>itsasecret</value>

          </parameter>

          <parameter>

               <name>initialConnections</name>

               <value>3</value>

          </parameter>

          <parameter>

               <name>maxConnections</name>

               <value>10</value>

          </parameter>

     </ResourceParams>

</DefaultContext>
```

Then, to retrieve a DataSource object, I do something like the following in my servlet code:

```
public class DirectoryAction extends Action {

     private DataSource webDs;

     public DirectoryList() {

          Context envCtx = null;

          try {

               envCtx = (Context) new InitialContext().lookup("java.comp/env");

               webDs = (DataSource) envCtx.lookup("jdbc/pg");

          }

          catch (NamingException ne) { webDs = null; }

     }

     public ActionForward execute(ActionMapping mapping,

          ActionForm form,

          HttpServletRequest req,

          HttpServletResponse res)

     throws ServletException {

          Connection dbConn = null;

          Statement stmt = null;

          ResultSet rs = null;

          if (webDs == null) {

               throw new ServletException("Null webDs DataSource");

          } else {

               try {

                    dbConn = webDs.getConnection();

                    stmt = dbConn.createStatement();

                    rs = stmt.executeQuery("select * from directory order by name");

               }

               catch (SQLException sqe) { throw new ServletException(sqe); }

               finally { SqlUtils.cleanup(dbConn, stmt); }

          }

     }

}
```

The shared DataSource can also be used in a JSP with the JSTL tags as follows:

```
<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>

<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>

<html>

     <head><title>SQL JSP</title></head>

     <body>

          <sql:query var="addrs" dataSource="jdbc/pg">

               select * from addresses

               order by sortname

          </sql:query>

          <c:forEach var="row" items="${addrs.rows}">

               <c:out value="${row.name}" escapeXml="false"/><br/>

               <c:out value="${row.addr}" escapeXml="false"/><br/>

               <c:out value="${row.city}" escapeXml="false"/>, <c:out value="${row.state}"/> <c:out value="${row.zip}"/>

               <br/><br/>

          </c:forEach>

     </body>

</html>
```

----------

## memborg

Well i have been trying to get this to work but now I get a:

```
javax.naming.NameNotFoundException: Name jdbc is not bound in this Context
```

Here's my context.xml

```

<Context path="/memborg" reloadable="true" crossContext="true" debug="1" >

  <Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_DBTest_log." suffix=".txt" timestamp="true"/>

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

  <ResourceParams name="jdbc/memborg">

    <parameter>

      <name>factory</name>

      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>

    </parameter>

    <parameter>

      <name>driverClassName</name>

      <value>com.mysql.jdbc.Driver</value>

    </parameter>

    <parameter>

      <name>url</name>

      <value>jdbc:mysql://localhost:3306/memborg</value>

    </parameter>

    <parameter>

      <name>username</name>

      <value>user</value>

    </parameter>

    <parameter>

      <name>password</name>

      <value>user_pass</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>

  </ResourceParams>

</Context>

```

my web.xml

```

<resource-ref>

    <description>Mysql</description>

    <res-ref-name>jdbc/memborg</res-ref-name>

    <res-type>javax.sql.DataSource</res-type>

    <res-auth>Container</res-auth>

  </resource-ref>

```

and my modified scrap.java

```

package com;

import java.sql.*;

import javax.sql.DataSource;

import javax.naming.*;

import java.util.*;

public class scrap

{

   String error;

   Connection con;

   String contextName = "jdbc/memborg";

   

   public scrap(){}

   

   public void connect() throws ClassNotFoundException, SQLException, Exception

   {

      try{

         Connection con;

         Context initContext = new InitialContext();

         Context envContext  = (Context)initContext.lookup("java:/comp/env");

         DataSource ds = (DataSource)envContext.lookup(contextName);

         con = ds.getConnection();

      }

      //catch(ClassNotFoundException cnfe){error = "Could not locate DB driver"; throw new ClassNotFoundException(error);}

      catch(SQLException cnfe){error = "Could not locate database"; throw new SQLException(error);}

      catch(Exception e){error = "An Unknown error occured" + e.toString(); throw new Exception(error);}

   }

   

   public void disconnect() throws SQLException

   {

      try{

         if(con != null)

         {

            con.close();

         }

      }

      catch(SQLException sqle)

      {error = "Could not locate databasen"; throw new SQLException(error);}

   }

   

   public ResultSet visScrap(String tmp) throws SQLException, Exception

   {

      ResultSet rs = null;

      try{

         String hent = "select * From billeder, scrapbog where dato='"+tmp+"' and billder.scrap_id = scrapbog.scrap_id";

         Statement stmt = con.createStatement();

         rs = stmt.executeQuery(hent);

      }

      catch(SQLException sqle){error="Could not execute request"; throw new SQLException(error);}

      catch(Exception e){error="An Unknown error occured"; throw new Exception(error);}

      return rs;

   }

}

```

My database is named "memborg" if that helps.

I've been trying to use http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html

 as psylo suggested, but it still doesn't work.

----------

## memborg

Well all this were kinda unessesary because I have found the solution. First i copied the jar file for Connector/J into CATALINA_HOME/common/lib

Then i changed back to the old code

```
Class.forName("com.mysql.jdbc.Driver").newInstance();

         con = DriverManager.getConnection("jdbc:mysql//localhost/memborg?--user=user --password=userpass");
```

And now it connects to my database...

Sorry for all the fuzz... And thanx for the help anyways.

----------

## Wilhelm

It's simple to get things working

emerge jdbc-mysql

cp /usr/share/jdbc-mysql/lib/<mysql jar file> /opt/blackdown<blah blah>/jre/lib/ext/

Easy as pie (ok took me a while to sort out  :Wink: )

----------

