JNK Java Connection

Preface
Installation
Example
Java Server Pages
Servlets
Deployment
References

Preface

I decided to write this tutorial because, at that time, I couldn't find a simple tutorial that demonstrates how one could use JSP and Servlets to connect to a DBMS and store/retrieve data, which is one of the most common things a dynamic web site author would do. Such a task is very simple in PHP or ASP, and it is easy in JSP too, but most books written about JSP & servlets simply confuse the reader with the details of the language instead of giving useful use case examples.
In this tutorial I assume that you are familiar with the Java programming language and JDBC (today of course you may use any of the ORM technologies like JDO, Hibernate or JPA etc.) and that you have some knowledge of Relational Databases. For the example in this tutorial I 'll be using MySQL RDBMS, and the Jakarta Tomcat Servlet container which is also a Web Container (JBoss works equally well). I 'll describe how to install each one of them in Windows and Linux operating systems, what to look after, and how to use them. You can also download the source code of the article and try it for yourself.

Installation

Jakarta Tomcat is the most famous and widely used servlet container or application server. It can also play the role of a standalone web server, or it can work in cooperation with other web servers such as Apache, IIS or Netscape. Above all, it's free!
It can be downloaded from Jakarta's web site http://jakarta.apache.org/tomcat. The most recent version, as of this writing, is 5.0.
Installation is similar for both Windows and Linux; once you download the tomcat5.zip or .exe file from the jakarta's web site, simply unzip it to a folder (e.g. C:\Program Files\WebServers\Tomcat5.0 in Windows and \usr\local\Tomcat5.0 in Linux) or follow the instructions of the installation wizard.
Tomcat accepts requests in port 8080. If this port is not used by another application, then you can easily start Tomcat by executing [Tomcat installation]\bin\startup.bat or [Tomcat installation]/bin/startup.sh. If you see no errors while starting this script, then you can verify that Tomcat is running by opening your favorite web browser and typing the URL: http://localhost:8080. Tomcat's starting web page should be loaded.
If for some reason the service fails to start, you can try to change the listening port. Open file [Tomcat installation]\conf\server.xml using your favorite text and/or xml editor, and search for the string "8080". Change it to another value, e.g. "8082". Save and exit, then try to run Tomcat again as we described previously.
To terminate the service simply execute [Tomcat installation]\bin\shutdown.bat or [Tomcat installation]/bin/shutdown.sh.

MySQL is one of the most famous RDBMSs and it is widely used for web applications. It can be downloaded from http://www.mysql.com. Even though version 5.0 is out, as of this writing, it is not stable yet, hence we 'll be using version 4.
Download the .zip file and unzip it to a temp folder. Execute Setup.exe in windows or follow the instructions on how to install in Linux (usually there is an install.sh in Linux; however please note that all modern Linux distributions come with mysql already installed; hence check if mysql service is already running or installed before trying to install another version of mysql). Follow the installation instructions. Download the JDBC/ODBC bridge .jar file too.
Once installation completes, execute winmysqladmin.exe in Windows, or winmysqladmin.sh in Linux. The first time you execute this service, it asks for a user name and password to act as administrator. After you provide them, the service normally starts. In Windows, however, it's good practice to create a shortut and locate it under Start | Programs | Startup and reboot after that. This way, MySQL admin service will run every time you start Windows.
If everything has gone smoothly, then next time you reboot Windows you 'll see a new service running on the services on the lower right part of your screen, i.e. a traffic light with the green light active (if the red light is active then something has been wrong with the service and you should check with mysql troubleshooting to find out what's wrong). A final word, please note that MySQL uses default port 3306.

We are almost ready to start. But before, we must notify Tomcat that we 'll be using MySQL. To do that, we must also download MySQL J/Connector from http://www.mysql.com and copy the downloaded jar (should be something like mysql-connector-java-3.x.x-stable-bin.jar) to [Tomcat installation]\shared\lib folder. This way, Tomcat will be able to find everything it needs to connect to MySQL database.

Example

I won't go into details on how to use MySQL or how to create databases in general, but only what is required by the example we 'll be developing together. We 'll create a Web PhoneBook application, where a user will be able to complete a form with phone book details (Lastname, Firstname, phone number), and then click on a button to save the values. The web application will store these values to a table Phones_table inside MySQL and will return a JSP page that contains all the records in that table. Let's begin.
Create a new database. (You can do that either from command line or by using one of the GUI interfaces to MySQL that exist, like Mascon from Scibit, or MyCC from MySQL, or MySQL Navigator or phpmyadmin). Otherwise, open a DOS (in Windows) or Shell (in Linux) prompt and start mysql by issuing the command mysql (or mysql -u root); note that mysql must be in your path to be able to execute this command; if it is not, then go inside [MySQL installation]\bin and run the command mysql). Up to MySQL version 4, you can create a database without issuing a username or password if you prefix the database name with the word 'test' (this has changed from version 4.1 and on however). Go on and create the database test_phonebook_db.

 mysql> create database test_phonebook_db

Next, create a new table, phones_table, which contains only three fields: lastname, firstname, phone, the first two of type string and the last one of type integer (I know, phone should also be of type string, but leave it integer for this example in order to demonstrate how you can store a datatype other than string). You could also create another field id to behave as the key, but we 'll not complicate things for this simple tutorial. Hence, leave your table without any key.

mysql> create table phones_table
(
 lastname varchar(45) not null,
 firstname varchar(35) not null,
 phone integer
);

mysql> select * from phones_table;
+-----------+-----------+-------+
| lastname | firstname | phone |
+-----------+-----------+-------+
| | | |
+-----------+-----------+-------+
0 rows in set (0.00 sec)

Java Server Pages

Well, I think that the best way to describe JSP elements and how they can be used to access the data from the database, is to see how we can do this using a Java application first. Below, we list a Java application that does the job we want, i.e. it uses JDBC to retrieve all the entries from the phones_table and display them to a JTextArea.

/* PhoneBook.java -- Displays all phonebook entries from a MySQL database table.
 * @ John Kostaras 2/10/2004
 */

import javax.swing.*;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.io.*;

public class PhoneBook extends JFrame { 
 // Declarations
 private static final String dbURL = "jdbc:mysql://localhost:3306/test_phonebook_db"; 
 private static final String driver = "com.mysql.jdbc.Driver";
 private Connection conn = null;
 private Statement stat = null;
 private ResultSet rs = null;
 private Object rsValue = null;
 private int counter;
 private String output = new String();

  public PhoneBook() { 
 this.getContentPane().setLayout(new BorderLayout());
	JTextArea txtaResults = new JTextArea();
	this.getContentPane().add(txtaResults, BorderLayout.CENTER);
	txtaResults.setText(getDBResults());
 addWindowListener(new WindowAdapter() {
 public void windowClosing(WindowEvent e) {
 System.exit(0);
 }
 });

 }

 private static String convertUTF8ToAnsi(String someString) 
 throws java.io.UnsupportedEncodingException {
 byte b[] = getBytesFromString(someString);
 return new String(b, "UTF-8");
 }
 
 private static byte[] getBytesFromString(String someString) {
 byte b[] = new byte[someString.length()];

 for (int f = 0; f < someString.length(); f++)
 b[f] = (byte) (someString.charAt(f));
 return b;
 } 

 private String getDBResults() {
 try {
 //1. initialise the JDBC-ODBC bridge providing MySQL's driver as a parameter 
Class.forName(driver).newInstance();
//2. initialise the connection to the database, 
// providing the URL, username and password (if any) 
conn = DriverManager.getConnection(dbURL,"", "");
//3 create an SQL statement and execute it
stat = conn.createStatement();
rs = stat.executeQuery("SELECT * FROM phones_table");
} catch (Exception e) {
System.out.print("Unable do make connection to phonebook");
System.out.print(e);
}
//4. process the results (stored in an instance of a ResultSet)
counter = 0;
try {
	while (rs.next()) {
	output += "Entry no. " + (++counter) ;
	output += "\nLastName: "+(((rsValue = 
 rs.getObject("lastname"))==null || rs.wasNull()) ?
	"" : convertUTF8ToAnsi(rsValue.toString()));
	output += "\nFirstName: "+(((rsValue =
	rs.getObject("firstname"))==null || rs.wasNull()) ?
	"" : convertUTF8ToAnsi(rsValue.toString()));
	output += "\nPhone Number: "+(((rsValue =
	rs.getObject("phone"))==null || rs.wasNull()) ?
	"" : convertUTF8ToAnsi(rsValue.toString()));
	output += "\n\n";
	}
	//5. release any resources
	rs.close();
	stat.close();
	conn.close();
 } catch (SQLException sqle) {
	sqle.printStackTrace();
 } catch (UnsupportedEncodingException uee) {
	uee.printStackTrace();
 }
 return output;
 }

 public static void main(String[] args) {
	Frame f = new TestDBObject();
	f.setSize(450, 260);
	f.show();  
 } 
}

The code is rather straightforward for those who have worked with JDBC before. The method of interest is getDBResults.The (standard for any database connection) steps are like so:

initialise the JDBC-ODBC bridge providing MySQL's driver as a parameter

initialise the connection to the database, providing the URL, username and password (if any)

create an SQL statement and execute it

process the results (stored in an instance of a ResultSet)

release any resources

The two methods convertUTF8ToAnsi() and getBytesFromString()allow you to retrieve the data that are stored in the fields of a MySQL table in UTF-8 format. I believe that you 'll find these methods very useful in your own projects, especially if you deal with internationalized projects (i.e. with languages other than English). Of course, these methods are not needed if you have correctly setup MySQL's collation to be either utf8_unicode_ci or utf8_general_ci.
Now let's see how the above listing is translated to JSP.

/* PhoneBook.jsp -- Displays all phonebook entries from a MySQL database table.
 * @ John Kostaras 2/10/2004
 */

<%@page language="java" 
	import="java.sql.*" %>
<!-- Declarations -->
<%!
	final static String dbURL = "jdbc:mysql://localhost:3306/test";
 final static String driver = "com.mysql.jdbc.Driver";
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	Object rsValue = null;
	int counter;
%>  
<%!
 private static String convertUTF8ToAnsi(String someString) 
 throws java.io.UnsupportedEncodingException {
 byte b[] = getBytesFromString(someString);
 return new String(b, "UTF-8");
 }
 
 private static byte[] getBytesFromString(String someString) {
 byte b[] = new byte[someString.length()];

 for (int f = 0; f < someString.length(); f++)
 b[f] = (byte) (someString.charAt(f));
 return b;
 } 
%>

<!-- Scriplets -->
<%
	try {
	Class.forName(driver).newInstance();
	conn = DriverManager.getConnection(dbURL,"", "");
	stat = conn.createStatement();
	rs = stat.executeQuery("SELECT * FROM phones");
	} catch (Exception e) {
	out.print("Unable do make connection to guestbook");
	out.print(e);
	}
%>

<html>
 <head>
 <title>PhoneBook Entries</title>
 </head>
 <body>
 <body bgcolor="#FFFFFF" text="#000000">
 <h3>Phonebook Entries <br>
 </h3>
 <%@ page contentType="text/html; charset=utf-8" %>
 <br>
 <% 
 counter = 0;
 while (rs.next()) { %>
 <h4>Entry no. <%= ++counter %> </h4>
 <table width="75%" border="1">
 <tr> 
 <td width="21%"><b>LastName:</b></td>
 <td width="79%"><%=(((rsValue = rs.getObject("LASTNAME"))==null || rs.wasNull()) ?
 "" : convertUTF8ToAnsi(rsValue.toString()))%>
 </td>
 </tr>
 <tr> 
 <td width="21%"><b>FirstName:</b></td>
 <td width="79%"><%=(((rsValue = rs.getObject("FIRSTNAME"))==null || rs.wasNull()) ? 
 "" : convertUTF8ToAnsi(rsValue.toString()))%>
 </td>
 </tr>
<td width="21%"><b>Phone:</b></td>
 <td width="79%"><%=(((rsValue = rs.getObject("PHONE"))==null || rs.wasNull()) ? 
 "" : convertUTF8ToAnsi(rsValue.toString()))%>
</td>
 </tr>
 </table>
 <br>
 <br>
 <% } %>

</body>
 </html>
 <%
 rs.close();
 stat.close();
 conn.close();
 %>

You can notice the similarities quite easily. As in the .java file, you start by declaring any import statements that you 'll need. In a similar manner, you declare a web page as .jsp by adding the following lines on top, using the directive <%@ %>:

<%@page language="java" 
 import="java.sql.*" %>

JSP directives provide global information about a JSP page. The syntax of a JSP directive is as follows:
<%@ directive {attribute="value"} %>
Three possible directives are currently defined by the JSP specification: page, include, and taglib.
You can also meet this tag in the declaration of the following statement where we tell the server that the resulting web page should be of type html with character set UTF-8. Don't forget to set this if you want to see your web page correctly.

<%@page contentType="text/html; charset=utf-8" %>

JSP code is defined by the <% %> tag. As we 'll see below, this is called scriplet tag.

Then, there is the declaration part, where you declare your attributes of the class. You normally declare attributes (and/or methods that are used by the jsp or the resulting servlet) using the declaration tag: <%! %>.Then follows the main part of the java class. You can write normal Java code in a JSP file by including it inside the scriplet tag: <% %> and mix it up with HTML tags. Finally, you can use a third type of tag, the expression tag <%= %> to return the value of a java method or statement in the position specified by the tag. Note, that you normally can do the same things with both the scriplet and expression tags, hence you can see them to be used interchangeably in JSP pages.
See! It was not difficult after all; rather straightforward I 'd say for someone who knows java and JDBC. S/he can the easily convert his/her code to JSP.

Servlets

Servlets contain the code that is executed in the server side. They normally contain all the business logic of the web application. Servlets have a three-part life cycle: initialisation - via the init() method, service - via the service() method and destruction - via the destroy() method. A servlet that handles HTTP requests extends from javax.servlet.http.HttpServlet.
The code for the servlet is shown in the following listing.

/* PhoneBookServlet.java -- Displays all phonebook entries from a MySQL database table.
 * @ John Kostaras 15/10/2004
 */  
package phonebook;

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

public class PhoneBookServlet extends HttpServlet {

 final String dbURL = "jdbc:mysql://localhost:3306/test";
 final String driver = "com.mysql.jdbc.Driver";
 private Connection conn = null;
 private Statement stat = null;
 private ResultSet rs = null;
 private int counter;
 private Object rsValue;

 private static String convertUTF8ToAnsi(String someString) 
 throws java.io.UnsupportedEncodingException {
 byte b[] = getBytesFromString(someString);
 return new String(b, "UTF-8");
 }
 
 private static byte[] getBytesFromString(String someString) {
 byte b[] = new byte[someString.length()];

 for (int f = 0; f < someString.length(); f++)
 b[f] = (byte) (someString.charAt(f));
 return b;
 }

 public void init() {
 try {
 Class.forName(driver).newInstance();
 conn = DriverManager.getConnection(dbURL,"", "");
 stat = conn.createStatement(); 
 } catch (Exception e) {
 System.err.print("Unable to make connection ");
 System.err.print(e);
 } 
 }

 public void doGet(HttpServletRequest request, HttpServletResponse response) 
 throws ServletException, IOException {
 try {
 rs = stat.executeQuery("SELECT * FROM phones");
 } catch (SQLException sqle) {
 sqle.printStackTrace();
 }
 response.setContentType("text/html; charset=utf-8");
 PrintWriter out = response.getWriter();
 String docType =
 "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " +
 "Transitional//EN\">\n";
 out.println(docType +
 "<HTML>\n" +
 "<HEAD><TITLE>PhoneBook entries</TITLE></HEAD>\n"); 
 counter = 0; 
 try { 
 while (rs.next()) { 
 out.println("<h4>Entry no. " + (++counter) + "</h4>"); 
 out.println("<table width=\"75%\" border=\"1\">");
 out.println("<tr>\n"+
 "<td width=\21%\"><b>LastName:</b></td>\n"+
 "<td width=\"79%\">"); 
 out.println((((rsValue = rs.getObject("LASTNAME"))==null || rs.wasNull())?
 "":convertUTF8ToAnsi(rsValue.toString())));
 out.println("</td>\n</tr>\n" + 
 "<tr>\n"+ 
 "<td width=\21%\"><b>FirstName:</b></td>\n"+
 "<td width=\"79%\">");
 out.println((((rsValue = rs.getObject("FIRSTNAME"))==null || rs.wasNull())?
 "":convertUTF8ToAnsi(rsValue.toString())));
 out.println("</td>\n"+
 "</tr>\n"+
 "<td width=\21%\"><b>Phone:</b></td>\n"+
 "<td width=\"79%\">");
 out.println((((rsValue = rs.getObject("PHONE"))==null || rs.wasNull())?
 "":convertUTF8ToAnsi(rsValue.toString())));
 out.println("</td>\n" +
 "</tr>\n" +
 "</table>\n"+
 "<br>\n<br>");
 }
 } catch (SQLException sqle) {
 sqle.printStackTrace();
 }
 out.println("</body>\n"+
 "</html>"); 
 }

 public void destroy() {
 try { 
 rs.close();
 stat.close();
 conn.close();
 } catch (SQLException sqle) {
 sqle.printStackTrace();
 }
 }
}

Pay attention on the init() and destroy() methods which are used to allocate and release resources (in our case, database resources). The most important method is doGet(), which accepts an HttpServletRequest and an HttpServletResponse as parameters. These two classes simply represent a request by the client and the response by the server (the result of the servlet) to this request. In other words, the request is what comes as input to the servlet from the client (e.g. form data) and the response is what the servlet returns back to the client.

This method gets the Writer from the response (i.e. the resulting page where it will print the result) in the variable out, and constructs the resulting html page using out.println() statements. You can see how error prone it is to produce an html page from a servlet, hence the usefulness of JSPs.

Deployment

By itself a Servlet is not a full Java application anymore. Since Servlet specification 2.3, servlets are part of a web application module that a container manages, most often packaged as a web archive or .war file. Using a Servlet to generate dynamic respones involves both creating the Servlet and deploying it for use in the Web Application. Each web application has its own configuration files and resources. The most important configuration file is called the deployment descriptor and it must be named web.xml. To deploy your servlet as part of a web application you have to do the following:
Create a new directory called e.g. phonebook inside Tomcat's webapps directory. Inside it create a new directory called WEB-INF. Create two more folders called classes and lib inside WEB-INF. contains all the servlets you create for your web app. It is good practice to put your servlet classes inside packages. We have declared package classesphonebook for our servlet (see previous listing). So create a new folder phonebook inside classes and put your PhoneBook.class[*] servlet inside it. lib contains any jar files that your web application depends upon. If you haven't copied the MySQL Connector/J jar file inside /shared/lib, then you have to copy it inside the WEB-INF/lib folder. Note that whatever files you put inside WEB-INF are not visible by any HTTP client. Finally, copy phonebook.jsp inside /phonebook folder (not the one inside WEB-INF/classes) in order to be visible by the clients.
We are not done yet. You need to create the deployment descriptor web.xml file and save it inside WEB-INF. Add the following code inside it:

<?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>
 <servlet>
 <servlet-name>PhoneBook</servlet-name>
 <servlet-class>phonebook.PhoneBookServlet</servlet-class>
 </servlet>
 
 <servlet-mapping>
 <servlet-name>PhoneBook</servlet-name>
 <url-pattern>/PhoneBookServlet</url-pattern>
 </servlet-mapping>
</web-app>

The main tag <web-app> denotes that this is the deployment descriptor for a web application. You define the web app's servlets inside such a deployment descriptor. For each servlet, you need to add two tags, <servlet> and <servlet-mapping>. The first tag (<servlet>) links the servlet class to a name (you can choose anything you like for the value of the <servlet-name> tag). The second tag (<servlet-mapping>) connects the previously defined name with a URL.
Your project has now the following directory structure:

phonebook\
          phonebook.jsp
          WEB-INF\
                web.xml
                                    lib\
                classes\
                        phonebook\
                                    PhoneBook.class

Create a .war file of the above structure or simply copy the root phonebook directory inside Tomcat's webapps folder. Restart Tomcat, open a web browser and try what we 've done so far. Type:

http://localhost:8080/phonebook/phonebook.jsp to test your JSP and then
http://localhost:8080/phonebook/PhoneBookServlet to test your servlet.

Notice that the name of your web application is the name of the .war file or of the root folder, i.e. phonebook.
If this doesn't work, then you need to do one more thing; you need to inform the Tomcat container that a new Web application exists. Usually, if you add a new web application (like phonebook we just described) under /webapps, Tomcat 5 automatically recognizes it as a new web app and there is nothing more to do. However, other times it does not, hence you need to do one more thing (especially if your web app has other folders inside it than the WEB-INF folder; e.g. /phonebook/entries). You can inform the Tomcat container using either of the following two methods:

1. Add the following line under <host> tag in /conf/server.xml file:

2. Create a new phonebook.xml file, that contains the above line, under /webapps (see [4] p.21).

Restart Tomcat and try again.
You can download the source code of the article, save it into your Tomcat's installation webapps folder, restart Tomcat and try it for yourself. Good luck with your web applications!

References

Hall, M., Brown L. (2001), Core Web Programming, Second Edition, Prentice Hall PTR, http://www.corewebprogramming.com.
Hall, M., (2000), Core Servlets and JavaServer Pages, Prentice Hall, http://www.coreservlets.com.
Hall, M., (2002), More Servlets and JavaServer Pages, Prentice Hall, http://www.moreservlets.com.
Goodwill, J. (2002), Apache Jakarta Tomcat, APress, ISBN: 1893115364, http://www.virtuas.com.
Falkner J., Jones K. (2003), Servlets and Java Server Pages, Addison-Wesley, http://www.jspbook.com.
CNET Networks (2004), Quickstart tool - JavaServer Pages, http://builder.com.com.
Davis S. & Marrs T. (2005), JBoss at Work: A Practical Guide, O' Reilly, ISBN: 0-596-00734-5.

* In order to compile your servlet, you need to add j2ee.jar or servlet.jar to your classpath. These jars are contained in J2EE framework which you need to download from Sun's web site.

Creator: John N. Kostaras - email [email protected]

Last modification: 19 January 2008

URL: http://www.geocities.com/jnkjavaconnection/webapp.html
URL: http://jkost.ergoway.gr/jnkjavaconnection/webapp.html