http://nycjava.net articles

Back to Basics

Using Java Servlets, JSPs and JDBC with Tomcat and Eclipse

Summary
A review of the basics in web development with Java using Tomcat as the web server / servlet /JSP engine and Eclipse as the development environment. For those who are new to Java development, a simple JSP and servlet are shown using a basic user login form and JDBC to access a database. In this example we'll be using MySQL as the database although any database with the appropriate JDBC driver can be used. For the seasoned developer, how to effectively use the Eclipse environment to develop and debug will be illustrated including techniques in running Tomcat inside the debugger as well as using an Eclipse plugin, JFaceDbc, to manage and debug the database using an SQL front end from within the Eclipse IDE.  (January, 2004)
By Dario Laverde                   note: last updated 2/29/04 -  also see Back to Basics Part 2 (includes unit testing)


So perhaps you're intimidated by J2EE and would like to start from the basics. Or perhaps you'd like to revisit the basics to quickly develop a solution for a simple problem without using J2EE or web frameworks that may be overkill for the problem domain. Or perhaps you've been reading books like Bitter EJB or studying for your web components certification or maybe just a bit glassy-eyed by all the new J2EE 1.4 specs and frameworks out there.  Well here's a back to basics example problem, a simple web based user login with as simple as you can get servlet/JSP/JDBC solution. But for the advanced developers, I'll show you how to package it into a deployable war file for Tomcat 5.0 and in addition I'll show you how to debug with Tomcat running within Eclipse and demonstrate a database utility plugin for Eclipse called JFaceDbc, which is not to be confused with JSF (Java Server Faces).

In this example we will not use an 'MVC framework' such as Struts or its successor Java Server Faces (JSF). In fact we won't even use a controller servlet. The point here is to illustrate the simplest example of web based database access that you will be able to reproduce from memory at your next job interview (should you need to prove the basics)

Here's what we'll cover:

The approach here is not a tutorial of the basic concepts (there are plenty of resources and books out there) i.e I will not explain what a servlet is but rather I will instead focus on simply presenting a step by step template focusing on how to debug using Eclipse and Tomcat. For prerequisites please see the Resources at the bottom of tthis article.  So the "advanced" topics will include:


The HTML form

We'll start out with the form as an html page and later save it as a JSP page. As forms get more complicated one has to resort to an HTML editor and not all editors recognize JSP syntax and/or the .jsp extension. So adding the JSP elements and renaming the file is done manually after using the html editor to create the form (unless one has access to a  GUI JSP page editor)

<html>
<body>
<form method="post" name="login" action="login">
  Username: <input type="text" name="username"><br>
  Password: <input type="password" name="password"><br>
  <input type="submit" value="log in">
</from>
</body>
</html>

For now this is just an index.html page we'll add the JSP element below.


The Servlet

Example 1.  Here's the entire web app essentially:

package org.nycjava;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.*;
import java.sql.*;
import javax.naming.*;
import java.io.*;

public class Login extends HttpServlet {

    public void doPost(HttpServletRequest request, HttpServletResponse response)
                                   throws ServletException {
        Context initCtx=null;
        Context envCtx=null;
        DataSource dataSource=null;
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;

        // obtain the html form's parameters
        String username=request.getParameter("username");
        String password=request.getParameter("password");
        
        // the error message to display to client
        String errmsg="";
       
        // Here's the JNDI lookup to obtain our database source
        try {
            initCtx = new InitialContext();
            envCtx= (Context)initCtx.lookup("java:comp/env");
            dataSource= (DataSource)envCtx.lookup("jdbc/login");
           
// in general we'll either handle known errors or pass them up asServletExceptions
        } catch(NamingException ne) {
            System.err.println("db naming error: "+ne.getMessage());
            return;
        } catch(Exception e) {
            throw new ServletException(e.getMessage());
        }

// obtain a db connection from the connection pool provided by Tomcat's datasource
        try {
            conn = dataSource.getConnection();
        } catch (SQLException e) {
            System.err.println("db sql error: "+e.getMessage());
        } catch(Exception e) {
            throw new ServletException(e.getMessage());
        }

        // perfrom SQL query:
        try  {
            stmt = conn.createStatement();
          
            StringBuffer cmd=
                    new StringBuffer("select * from users where username='");
            cmd.append(username);
            cmd.append("'");

            rs = stmt.executeQuery(cmd.toString());

            if( rs.next() ) {
                if(!rs.getString("pwd").equals(password)) {
                    errmsg="wrong password";
                }
            }  else {
               errmsg="user not found";
            }
        }  catch (SQLException sqle) {
            System.err.println(sqle.getMessage());
        }  catch (Exception e) {
            throw new ServletException(e.getMessage());

        // always close and set references to null to return connection to pool
        }  finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqle) {
                    System.err.println(sqle.getMessage());
                }
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqle) {
                    System.err.println(sqle.getMessage());
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqle) {
                    System.err.println(sqle.getMessage());
                }
                conn = null;
            }
        }
       
        if(errmsg.length()>0) {
            HttpSession session = request.getSession();
            session.setAttribute("errmsg",errmsg);
            forward(request, response, "/index.jsp");
        } else {
            response.setContentType("text/html");
            try {           
                PrintWriter out = response.getWriter();
                out.println("<html><body>Success!</body></html>");
                out.close();
            } catch (IOException e) {
                throw new ServletException(e.getMessage());  
            }
        }
    }

// in case someone accesses this servlet with a 'get' simply redirect to form
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException {
        forward(request,response,"/index.jsp");
    }

    // convenience method to forward requests
    private void forward(HttpServletRequest request, HttpServletResponse response,  
                                                       String target) throws ServletException {
        try {
         RequestDispatcher disp= getServletContext().getRequestDispatcher(target);
         disp.forward(request, response);
        } catch(IOException ioe) {
             throw new ServletException(ioe.getMessage());  
        }
    }
}

The database connection uses the underlying servlet container's (Tomcat's) datasource which includes a connection pool.  Notice how one has to make sure the connection is freed (nulled) in order to a avoid a pool 'leak'. An HttpSession is used to set a result attribute (an appropriate error message) that we will pass to the JSP page which just so happens to be the input form page.

At this point we wil add the JSP tag to display the attribute we set in the servlet and place the error message right before the form and rename the page to index.jsp.  Upon success, we simply write out a "success" web page although we could have also forwarded the response to another jsp page. Below is the revised form.

<html>
<body>
<%  String msg=(String)session.getAttribute("errmsg");  if(msg!=null) out.println(msg); %>
<form method="post" name="login" action="login">
    Username: <input type="text" name="username"><br>
    Password: <input type="password" name="password"><br>
    <input type="submit" value="log in">
</from>
<script language="JavaScript">
<!--
document.login.username.focus()
//-->
</script>
</body>
</html>

If we had simply aded <%= session.getAttribute("errmsg") %> it would have displayed "null" when you first accessed the page, hence the check for null. For completion sake we also add the JavaScript necessary to add the default focus which every good form should have.

Note that Java code in a JSP page is not desirable hence the need for custom tags and JSTL, but we're back to basics for now.


The Web Application

The file hierarchy for the web application is as follows:

/index.jsp
/WEB-INF/classes/org/nycjava/Login.class
/WEB-INF/web.xml
/META-INF/context.xml

What looks new here to seasoned developers is the context.html file which is where the context and database connection is defined for our application in Tomcat where previously we had to edit the global server.xml configuration file.  Again, the web.xml file is where we define and configure servlets and references to the resources for our web application.

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>login</display-name>

  <servlet>
    <servlet-name>Login</servlet-name>
    <servlet-class>org.nycjava.Login</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>Login</servlet-name>
    <url-pattern>/login</url-pattern>
  </servlet-mapping>

  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>

  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/login</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>

<!-- used to force use of SSL (https)
  <security-constraint>
    <web-resource-collection>
      <web-resource-name>entire application</web-resource-name>
        <url-pattern>/*</url-pattern>
      </web-resource-collection>
    <user-data-constraint>
      <transport-guarantee>CONFIDENTIAL</transport-guarantee>
    </user-data-constraint>
  </security-constraint>
-->

</web-app>

context.xml

<Context path="/login" docBase="login" debug="0" reloadable="false" crossContext="true">

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

  <ResourceParams name="jdbc/login">

    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>

    <parameter>
      <name>maxActive</name>
      <value>100</value>
    </parameter>

    <parameter>
      <name>maxIdle</name>
      <value>30</value>
    </parameter>

    <parameter>
      <name>maxWait</name>
      <value>10000</value>
    </parameter>

    <parameter>
     <name>username</name>
     <value>login</value>
    </parameter>

    <parameter>
     <name>password</name>
     <value>password</value>
    </parameter>

    <parameter>
       <name>driverClassName</name>
       <value>com.mysql.jdbc.Driver</value>
    </parameter>

    <parameter>
      <name>url</name>
      <value>jdbc:mysql://localhost/login?autoReconnect=true</value>
    </parameter>

  </ResourceParams>

</Context>

Using MySQL and Eclipse

Create a project that wil set the output folder to WEB-INF/classes and add the external servlet-api.jar from the Tomcat lib folder to the project's classpath. Your servlet should compile without errors.

Note: For the standard Tomcat distribution it's the common/lib folder that I refer to when I mention Tomcat's lib folder. In this specific example I'm using the embedded distribution since there are less jar files and the distribution is much smaller and minimal (there's only one lib folder). If you do decide to use this embedded distribution you must add the commons-dbcp and commons-pool jar files to the lib folder to provide connection pooling (available online from http://jakarta.apache.org/commons or you can copy it from the full Tomcat distribution).

Creating the Database:

First, if you haven't already done so, start the MySQL server (bin/mysqld or bin/mysqladmin or bin/winmysqladmin). Also make sure you have the MySQL JDBC driver jar file installed in your Tomcat lib folder (e.g. mysql-connector-java-3.0.11-stable-bin.jar)

If you wish to use the command line instead of Eclipse, use the bin/mysql client to access the main database and if you haven't already, assign a password to root (it's blank by default). Run the SQL scripts (including with souirce code) in one of two ways; log into the MySQL server using the mysql client and use the source command (source [script file]) -or- redirect the script into the mysql client command:mysql -u[uername] -p[password] [database] < script.sql

For Eclipse, install the JFaceDbc plugin from http://jfacedbc.sf.net and you will have a new Eclipse Perspective (select it from the perspective toolbar, the left side tool bar). You can also open the perspective from the Window menu: Open Perspective->Other...

Find the Drivers pane (one of the tabbed panes in the upper left) and locate the MySQL driver named MMMySQL (Mark Matthews wrote it originally but now it's part of mysql.com)  Set the location of the driver jar file, which you should have copied into your Tomcat lib folder  Enter the driver class name as com.mysql.jdbc.Driver. (I also renamed the driver to MySQL as shown below).

Figure 1.



At this point you can now create an alias called "mysql" to access the database server from the aliases pane.. Then from the Connections pane (lower left) you will create a new connection and login as root in order to create a new database and a new user for that database. Select the correct alias for the database you are using and create a new connection to it. You can then create a new SQL editor window to run SQL commands (right-click on the connection) Enter the commands shown (or use script file available with source code) to create the database and the user.

create database login;
grant all privileges on login.* to "login"@"localhost" identified by "password";      (note: on linux you may have to use localhost.localdomain)

Figure 2.

We can then close this connection and create a new alias called login that will use the new user and new database that we just created. Again create a new connection and open an SQL editor where we can create the table and populate the first entry either by typing in the following or using an external .sql file. Also available with the plugin is the ability to interactively edit the tables and data from database structure view pane (bottom pane) by selecting a table and right-clicking on it.

use login;
create table if not exists users (
  id int auto_increment primary key,
  username varchar(100),
  pwd  varchar(100),
  role  int
);
insert into users values(null,'admin','password',1);

Figure 3.


From the main Eclipse menu, File->New->Other... we can create a schema ERD diagram by simply dragging the table(s) we want from the database structure view.

Figure 4.

Deploying to Tomcat:

Switch back to the Java perspective (some of the JFaceDbc views will remain but you can close them if you wish).

Copy the javac compiler from your JDK lib (the tools.jar file) to your Tomcat lib folder. This is needed for Tomcat to compile JSPs. Alternatively you can use IBM's Jikes compiler. (see Tomcat's global web.xml for how to use Jikes)

Running Tomcat inside Eclipse:

Create a new Run configuration and set the following:

Main Class: org.apache.catalina.startup.Bootstrap
Program Arguments: start
VM Arguments: -Dcatalina.home=/usr/jakarta-tomcat-5.0.16-embed (or where your TOMCAT_HOME is)
Classpath - User Entries:  add as external jar files all of Tomcat's jar files found in the lib folder(s).
     Note that for the standard distribution these are: common/lib,  server/lib, and bin  For the embedded distribution it's just the lib folder.

                         

Figures 5 and 6.

Go ahead and run Tomcat. Our initial deployment will occur on a live Tomcat server. For the embedded distribution, you will have to create a server.xml file under conf and set the standalone web server port. An example is found here.

Create and deploy the war file:

Select your login project in the Java perspective, right click and select Export... Now select jar file and from the Jar export window select the files and folders you wish to include (the hierarchy noted above). Now browse to the Tomcat webapps folder and save it as login.war To create the war file manually use the jar command:
 jar cvf  [tomcat /webapps]/login.war META-INF WEB-INF index.jsp

At this point you have just deployed your application and you can test it with a web browser. e.g. http://localhost:8080/login

Developing without deploying everytime:

Here's an interesting and not quite documented sidebar:

When Tomcat deploys your application and it has a context.xml file in the META-INF folder, it will copy and rename the file to login.xml and place it in the
 /conf/Catalina/localhost folder.  From then on it will use this as the context file for your web application.

To run and debug Tomcat while using your project files in your current working or development folder, simply edit that file and point the docBase to your development folder.

For example simply edit the first line of [tomcat_home]/conf/Catalina/localhost/login.xml

Context path="/login" docBase="c:\myprojects\login" debug="0" reloadable="false" crossContext="true">

Restart Tomcat and from now it will use your development folder instead of what's in webapps. In fact to avoid confusion, delete both the login.war file and the expanded folder in webapps. Now you can make changes to your application and have the changes reflected immediately while debugging. optionally you may want to install Tomcat's source code so that you can step into the web application server code as well.


Summary

Hopefully this will server as a re-introduction to those who are still fairly new to Java web development and as a refresher course with tips on how to be more productive for seasoned developers. Sometimes it's necessary to go back to basics every now and then to remind us what lies underneath all those frameworks and tools. A simple problem usually deserves a simple solution and not one that may be overkill. It can also serve to benchmark performance and complexity relative to using frameworks as the problem domain expands. Once one masters the basics one can very quickly develop prototypes before moving on to EJBs.

Recommended Exercises:

Copyright © 2004 Dario Laverde