Now blogging at diego's weblog. See you over there!

configuring apache 2 + tomcat 5 + mysql + jdbc access on linux and windows


Heh. That title took almost as long to write as it took to complete the configuration. :)

I spent some time today preparing the basics for webapp development and runtime. The "basics" include:

Update (Jan 26, 2005): I've posted some new information related to the tomcat config for versions higher than 5.0.18.
To make sure I understood the differences across environments, I configured the system in parallel on both a Linux Red Hat 9 machine and a Windows XP machine. Before I begin with describing the steps I took to configure it, I want to thank Erik for his help in finding/building the right packages and distributions, particularly on the Linux side of things. It would have taken a lot longer without it.

So here are the steps I took to get it up and running...

Apache

The installation of Apache is pretty straightforward, both for Linux and Windows. Red Hat 9 usually includes Apache "out of the box" so there's one less step to go through. When in doubt, the Apache docs usually fill in the picture (documentation for 2.0 has improved a lot with respect to 1.3.x).

Tomcat

Here's where things got interesting. The last time I used Tomcat was when 4.0 was about to be released, and I had switched over to the dev tree for 4 since 3.x had serious scalability problems. There are tons of new things in the package, but the basic configuration doesn't need most of them. Installing Tomcat itself is in fact also quite straightforward (Again, the docs are quite complete), but it's when you want it to access it through Apache that things get a little more complicated.

Apache + Tomcat: The Fun Begins

To access Tomcat through a Apache you need a connector. Tomcat has connectors for both Apache and IIS, but the problem is that apache.org doesn't include RPMs (and in some cases) binaries. The connector that I wanted to use was JK2, but binaries for RH9 where not available (I got the Windows binaries from there though). So. I first tried downloading the package supplied at JPackage.org (which is a really handy resource for Java stuff on Linux) but after a few tries (both getting the binaries and rebuilding from the source RPMS, including having to install most of the dev stuff which still wasn't in the server) it wasn't working. Most probable reason for this was that these packages are actually being done for Fedora, not RH9.... it's amazing that Fedora hasn't officially taken over and already we've got compatibility problems. Finally Erik pointed me to this page at RPM.pbone.net where I could actually get the binary RPMs directly and install it. So far so good. Now for the configuration.

Configuring the connector is not really that complicated, and it worked on the first try. The steps are as follows ("APACHE_DIR" is Apache's installation directory, and "TOMCAT_DIR" is Tomcat's install dir):

  1. Go to TOMCAT_DIR/conf and edit the file jk2.properties, adding the following line:
    channelSocket.port=8009
    (This is just because I chose to use a port other than the default).
  2. In the same directory, open server.xml and look for the "non-SSL Coyote HTTP/1.1 Connector". This is the standard Tomcat-only connector and comment it out since we'll be using Apache for handling HTTP requests. In the same file, look for a commented line that says "<Context path="" docBase="ROOT" debug="0">". Right after that line, add the following Context path:
    <Context path="" docBase="APACHE_DIR/htdocs" debug="0" reloadable="true" crossContext="true"/>
    Btw, htdocs is the standard Apache root document dir, you will need to change that if you have moved it to another location.
  3. Now go to the APACHE_DIR/conf directory. There, create a file workers2.properties, with the following contents:
    [shm]
    file=APACHE_DIR/logs/shm.file
    size=1048576

    # socket channel
    [channel.socket:localhost:8009]
    port=8009
    host=127.0.0.1

    # worker for the connector
    [ajp13:localhost:8009]
    channel=channel.socket:localhost:8009

    Note that the port matches that defined in server.xml above for Tomcat.
  4. Copy the module file into APACHE_DIR/modules (for Windows this will be something of the form mod_jk2*.dll and for linux mod_jk2*.so).
  5. Edit the file APACHE_DIR/conf/httpd.conf and add the following lines at the end of the list of modules loaded into Apache:
    LoadModule jk2_module modules/MODULE_FILE_NAME

    <Location "/*.jsp">
    JkUriSet worker ajp13:localhost:8009
    </Location>

    <Location "/mywebapp">
    JkUriSet worker ajp13:localhost:8009
    </Location>

    The "mywebapp" reference points to a directory that will be handled by Tomcat, you can add as many mappings/file types as you need to be handled by the connector.
And we're set! You can now drop a simple test.jsp file into the Apache document root, something like this:
<HTML>
<BODY>
<H1><%= " Tomcat works!" %></h1><%= "at " + java.util.Calendar.getInstance().getTime() %>
</BODY>
</HTML>
And then access it simply with
http://localhost/test.jsp

JDBC + MySQL

Before moving on to configuring database pools on Tomcat and so on, it's a good idea to test JDBC in an isolated environment. This is easy. First, get the MySQL Control Center application to create a test user and database/table to prepare the environment. This app is quite complete, and multiplatform to boot (Erik also mentioned Navicat as an app with similar functionality but better UI for Mac OS). For this test I created a a database called testdb and a single table in it, called user. I added three fields to the table: name (varchar), password (varchar) and id (int). I also created a test user (username=test, password=testpwd). Note that the user has to be allowed access from the host that you'll be running the test on, typically localhost, as well as permissions on the database that you'll be using (in this case, testdb).

Once the db is ready, you can get MySQL's JDBC driver, Connector/J, from this page. After adding it to the classpath, you should be able to both compile and run the following simple JDBC test app:

import java.sql.*;

public class TestSQL {

    public static void main(String[] s)
    {
        try {
            Connection conn = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String dbUrl = "jdbc:mysql://localhost/testdb?user=test&password=testpwd";
            //fields are name (String), password (String) and ID (int)
            conn = DriverManager.getConnection(dbUrl);
            String statement = "INSERT INTO user (name, password, id) VALUES ('usr1', 'pwd1', '1')";
            Statement stmt = conn.createStatement();
            boolean res = stmt.execute(statement);
                    
            String query = "SELECT * FROM user";
            
            ResultSet st = stmt.executeQuery(query);
            while (st.next()) {
                String name = st.getString("name");
                String pwd = st.getString("password");
                String id = st.getString("id");
                System.err.println("Query result="+name+"/"+pwd+"/"+id);
            }

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

            if (conn != null) {
                conn.close();
            }
                        
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
}

Which, when executed, should print Query result: usr1/pwd1/1.

Using JDBC/MySQL from Tomcat

Once JDBC and MySQL are up and running, we can move to the final step, namelyl, access the MySQL database through JDBC from Tomcat. For this I used the guide provided here within the Tomcat docs. For completeness (and to maintain the context of this example), the following are the steps required to set up the JNDI reference to the connection pool (managed from code built into Tomcat that uses the Apache Commons Pool library among other things):

  1. Copy the JDBC driver JAR file into TOMCAT_DIR/common/lib
  2. Edit TOMCAT_DIR/conf/server.xml and after the Context tag for the root dir (added at the beginning) add the following:
    <Context path="/testdb" docBase="APACHE_DIR/htdocs/testdb"
            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>10</value>
        </parameter>

        <!-- Maximum number of idle dB connections to retain in pool.
             Set to 0 for no limit.
             -->
        <parameter>
          <name>maxIdle</name>
          <value>5</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>test</value>
        </parameter>
        <parameter>
         <name>password</name>
         <value>testpwd</value>
        </parameter>

        <!-- Class name for mm.mysql JDBC 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/testdb?autoReconnect=true</value>
        </parameter>
      </ResourceParams>
    </Context>

    This sets up the JNDI resource and paths.
  3. Now edit TOMCAT_DIR/conf/web.xml and add the following after the default servlet:
      <resource-ref>
          <description>DB Connection</description>
          <res-ref-name>jdbc/TestDB</res-ref-name>
          <res-type>javax.sql.DataSource</res-type>
          <res-auth>Container</res-auth>
      </resource-ref>
    Finally, compile and package into a JAR the following java file (TestSQLLoad.java):
    import javax.naming.*;
    import javax.sql.*;
    import java.sql.*;

    public class TestSQLLoad
    {
      
     String user = "Not Connected";
     String pwd = "no pwd";
     int id = -1;
     
     public void init() {
      try{
       Context ctx = new InitialContext();
       if(ctx == null ) {
        throw new Exception("Boom - No Context");
       }

       DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");

       if (ds != null) {
        Connection conn = ds.getConnection();
        
        if(conn != null) {
         user = "Got Connection "+conn.toString();
         Statement stmt = conn.createStatement();
         String q = "select name, password, id from user";
         ResultSet rst = stmt.executeQuery(q);
         if(rst.next()) {
          user=rst.getString(1);
          pwd=rst.getString(2);
          id = rst.getInt(3);
         }
         conn.close();
        }
       }
      }catch(Exception e) {
       e.printStackTrace();
      }
     }

     public String getUser() {
      return user;
     }
     
     public String getPassword() {
      return pwd;
     }
     
     public int getID()
     {
      return id;
     }
    }

  4. Once you've got the JAR ready, drop it into TOMCAT_DIR/common/lib as well.
    Important note: Normally we would configure the JNDI resources and drop the JAR file into an independent web application, but I am placing it into the default web app and dropping everything into common/lib (not a good idea in general, except for the JDBC driver's library) to simplify the example. Quick and dirty, and yes, maybe a bit too dirty, but it's better not to be configuring a web app at the same time; all we need to know is that the JDBC driver, the JNDI reference, and the pool work properly.
  5. We're almost ready. Now create a directory testdb under APACHE_DIR/htdocs (or whatever your Apache document root is) and create a file test-db.jsp with the following contents:
    <html>
      <head>
        <title>DB Test</title>
      </head>
      <body>

      <%
        TestSQLLoad tst = new TestSQLLoad();
        tst.init();
      %>

      <h2>Results</h2>
        User <%= tst.getUser() %><br/>
        Pwd <%= tst.getPassword() %><br/>
        Id <%= tst.getID() %>

      </body>
    </html>

    You should now be able to access it through the following URL:
    http://localhost/testdb/test-db.jsp
And that's it! Phew!

Apache, Tomcat and MySQL are ready to go. Hope this helps, and as usual, comments/questions are welcome.

Categories: soft.dev
Posted by diego on February 11 2004 at 12:16 AM

Copyright © Diego Doval 2002-2011.
Powered by
Movable Type 4.37