Wednesday, June 27, 2012

How to create a web app using Java, MySQL, JDBC, Servlets, and Tomcat

If you ask developers what technologies are used to build modern web applications today, you would probably get something along the lines of PlaySpring and Hibernate.  These tools are great because they hide all the lower level implementation details such as servlets and the actual SQL itself, but as a developer, it is beneficial to understand what goes on beneath these layers of abstraction.  To fulfill my own curiosity, I created a simple web application using Java, MySQL, JDBC, Servlets, and Tomcat.  The rest of this post describes what I did.

Step 1: Download MySQL
The first step is to download MySQL and get it up and running.  Follow the installation instructions, start up the server, and make sure you can log in using "mysql -u root -p" in the terminal.   This link provides a good resource for a refresher on SQL.

Step 2: Download Connector/J
Connector/J can be downloaded here.  All we really need is mysql-connector-java-5.1.20-bin.jar in the src/lib folder.   This jar file is the JDBC driver that lets us communicate with MySQL using Java code.

Step 3: Establish a connection to MySQL using JDBC
Create a new Java project and add mysql-connector-java-5.1.20-bin.jar to the build path.  This link provides a good tutorial on JDBC.

Step 4: Download Tomcat
Tomcat 7 can be downloaded here.  Unpack and follow the instructions in RUNNING.txt.  Once Tomcat is running, you should be able to see the default web page at http://localhost:8080.   Add mysql-connector-java-5.1.20-bin.jar to apache-tomcat-7/lib.

Step 5: Understand the directory structure of a Java web application
Java web applications should have the following directory structure

Directory structure of a Java web app

The root folder is the name of your Java project. Under the root folder, there should be another folder called WEB-INF.  Under the WEB-INF folder, there should be a classes folder, a lib folder, and a web.xml file.  Java source files and compiled .class files belong in the classes folder.  Libraries belong in the lib folder.  The web.xml file is used by Tomcat to map a URL to a servlet.

Step 6: Write a simple Java application using JDBC and servlets
Create a new Java project using the directory structure above.  If you don't know where to begin with JDBC and servlets, make sure Tomcat is running and go to http://localhost:8080/examples/servlets.  Look at the code samples and execute them.  Don't forget to add apache-tomcat-7/lib/servlet-api.jar to your project build path.

I created a to-do list web application.  It queries a JDBC/MySQL backed database for a list of items, displays it in a table, and lets you add or delete items.  The full source code is available at my Github repository here.

Screenshot of my web app
Step 7: Configure web.xml
Configure web.xml to map the URL to your servlet class.  Below is my sample web.xml file.

When a client visits http://localhost:8080/simple-servlet/example, the request is handled by the application server.   Tomcat scans for the servlet-mapping containing the "/example" URL pattern, looks up the servlet-name which is "todo", matches the servlet-name to the right servlet , and routes the HTTP request to TodoServlet.class.  Then TodoServlet.class executes its logic and returns a response back to the client through Tomcat.

Step 8: Package web application into WAR file and deploy on Tomcat
First, compile all source files.  In a terminal/console, navigate to your project's root directory.  Then type in "jar cvf simple-servlet.war .".  This creates a WAR (Web application ARchive) which is simply a jar file with a .war extension.  Drop this war file into your apache-tomcat-7/webapps directory and test by going to http://localhost:8080/simple-servlet/example.

That's it!  Your database-backed web application is up and ready to go.

Note: Creating HTML in servlets is actually not a good practice.  Ideally, you should use some type of view technology like JavaServer Pages or JavaServer Faces to move data between the view and logic layers.  Also, the database login username and password should be passed in to the program as parameters instead of being hardcoded.   For the purpose of keeping it quick and simple, I did not follow these guidelines in my project.


  1. interesting piece of information, I had come to know about your web-page from my friend pramod, jaipur,i have read atleast eight posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards Guidewire training in hyderabad

  2. I have been reading out a lot of your articles.
    I will certainly bookmark your Blog.
    Free Hybris Training
    Devops Training