I decided to write this tutorial because I didn'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 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 Server. 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.
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.
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.
Otherwise, open a DOS (in Windows) or Command (in Linux) prompt and start mysql
by issuing the command mysql (or mysql
-u user); 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)
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). 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 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.
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. classes
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 phonebook
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:
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.
You are ready now. Restart your 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.
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:
<Context path="/phonebook" docBase="phonebook" debug="0" reloadable="true" />
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!
Creator: Hemanth. B - emailhemanth.balaji@gmail.com