The JSP Files - Parts 1 to 8: Tagged and Bagged

The JSP Files - Part 5: No Forwading Address

Toolbox

JSP offers a number of advantages over other server-side scripting languages - as you've already seen, performance is just one of them. And this performance edge becomes particularly important when you combine it with another important benefit - the ability to seamlessly connect to a variety of database servers.

By offering seamless database connectivity (okay, it's not as transparent as the database connectivity available in PHP, but it's still pretty good!) in combination with faster response times, JSP allows developers to build complex, scalable, data-driven Web applications while simultaneously enjoying short development cycles.

OK, 'nuff said. Let's cut to the chase.

In this article, we're going to demonstrate how to use JSP to connect to a database, extract data from it, and use that data to build a dynamic Web page. We'll be building a simple Web application in order to help make the process clearer; this should also help you quantify how much easier (or harder) JSP is to use, as compared to other server-side scripting languages you may be familiar with.

If you're planning on trying out the examples below (recommended), you'll need to download and install the mySQL database server, available at http://www.mysql.com/. mySQL is a fast, reliable, open-source database management system, which offers a fair amount of power at a price that should move you to tears - it's free!

We'll be assuming that you've installed and configured mySQL, and have the appropriate permissions to create and edit database tables.

Since all database interaction in Java takes place using a technology known as JDBC, or Java Database Connectivity, you'll also need a JDBC module that allows you to connect to the mySQL database server. We'll be assuming that you've downloaded the mm.mySQL JDBC module from "Slapping Together A JSP Development Environment".

If you're using a database other than mySQL, fear not - JSP supports all major databases, and you can use the techniques described over the next few pages to talk to other databases too. You'll probably need to consult your database vendor's manual or Web site for information on how to obtain the necessary software.

Finally, some knowledge of SQL would come in handy. In case you don't know SQL, don't worry - it's extremely simple, and a few minutes with the "Speaking SQL" tutorial will have you executing queries like an expert.

With all that out of the way, let's actually get our hands dirty.

Dumped!

If you're familiar with SQL, you know that there are four basic types of operations possible with a database:

• SELECT a record;
• INSERT a record;
• UPDATE a record;
• DELETE a record.

In order to demonstrate these operations, we're going to build a little application that requires each of the functions listed above - an address book which allows multiple users to store and view contact information online.

As always, one of the first things you have to think about when designing a data-driven application is the design of the database (duh!). For this application, we've decided to use a single table called "abook", which contains fields for different types of contact information - address, phone, fax, email address, and the like. Every user in the system has a unique login id, and each record in the database is "owned" by a specific user.

We've put together a "dump file", which lets you create the database tables and initial set of records quickly - we suggest that you import this data into your mySQL database server, as we'll be using it throughout this article.

To import the data, download the dump file and use this command at your mySQL prompt:

mysql> mysql -u username -p database < dumpfile

Or you could insert the contents manually - here is what you'll need:

#                      
# Table structure for table 'abook'                      
#                      
                     
DROP TABLE IF EXISTS abook;                      
CREATE TABLE abook (                      
  id int(11) unsigned NOT NULL auto_increment,                      
  uid varchar(255) NOT NULL,                      
  fname varchar(255) NOT NULL,                      
  lname varchar(255) NOT NULL,                      
  tel varchar(255),                      
  fax varchar(255),                      
  email varchar(255),                      
  addr text,                      
  company varchar(255),                      
  comment text,                      
  PRIMARY KEY (id)                      
);                      
                     
#                      
# Dumping data for table 'abook'                      
#                      
                     
INSERT INTO abook (id, uid, fname, lname, tel, fax, email,                      
addr, company,comment) VALUES ( '1', 'john', 'Bugs',                      
'Bunny', '7376222', '', 'bugs@somedomain.com', 'The Rabbit                      
Hole, Dark Woods, Somewhere On Planet Earth', '',                      
'Big-ears in da house!'); INSERT INTO abook (id, uid,                      
fname, lname, tel, fax, email, addr, company,                      
comment) VALUES ( '2', 'john', 'Elmer', 'Fudd', '', '7628739',                      
'fuddman@somedomain.com','', '', ''); INSERT INTO abook (id,                      
uid, fname, lname, tel, fax, email, addr, company,comment)                      
VALUES ( '3', 'joe', 'Peter', 'Parker', '162627 x34', '',                      
'webcrawler@somedomain.com', 'Your Friendly Neighbourhood                      
Newspaper', '', 'My spidey-sense is tingling!');                      
INSERT INTO abook (id, uid, fname, lname, tel, fax, email,                      
addr, company, comment) VALUES ( '4', 'bill',                      
'Clark', 'Kent', '1-800-SUPERMAN', '',                      
'superdude@somedomain.com', '', '', 'Is it a bird? Is                      
it a plane?');

This will create a table named "abook" with columns for different types of contact information; these records are owned by three mythical users, "bill", "john" and "joe".

Now check whether or not the data has been successfully imported with a SELECT query (the SELECT SQL statement is used to retrieve information from a database). Enter this at your mySQL command prompt:

mysql> select uid, fname, lname from abook;

which, in English, means "display the columns uid, fname and lname from the address book". Here's what you should see:
+------+-------+--------+                      
| uid  | fname | lname  |                      
+------+-------+--------+                      
| john | Bugs  | Bunny  |                      
| john | Elmer | Fudd   |                      
| joe  | Peter | Parker |                      
| bill | Clark | Kent   |                      
+------+-------+--------+                      
4 rows in set (0.00 sec)
Copyright Melonfire, 2000. All rights reserved.