Extending servlets with Java Database Connectivity (JDBC)

The next web application we will create will allow us to look for the phone number of someone given his name:

Clicking on the Search! should display the phone number of the person if the name exists and an appropriate error message if not.

We are going to use an HMTL form and a servlet for the searching for the phone number. The new aspect is that we are going to store the names and numbers in a database in a table similar to this:

CREATE TABLE phonebook (
  name varchar(255) NOT NULL,
  number varchar(255) NOT NULL,
  PRIMARY KEY (name)
);

The work to do is to create an appropriate database, create such a table in it and populate that table with some test data.

Then, write a Java web application in which a servlet connects to and searches for phone numbers in the database. Use the Java Database Connectivity (JDBC) API. [NB: As we use MySQL, it is important to use Connector/J, the official JDBC driver for MySQL.]

Enhancing the application with proper caching

As it is now, a SQL query is issued each time someone looks for a name… even if that’s for the same name. In other words, searching for the phone number of the same person many times will systematically query the database. It would be more intelligent to query only once (the first time) and then memorise (cache) the resulting phone number.

The rationale behind this is to minimise the number of SQL queries while preserving the semantics of the application (Discuss!) i.e. increase overall performance and decrease the use of critical resources.

Drilling down

A lot of database applications are used to drill down data presented in a hierarchical manner. For instance, we may have different countries on the first level and clicking on one of the countries reveals artists from that country. Similarly, clicking on an artist reveals songs by that artist.

The objective is the build such a web application using servlet(s) and JDBC only based on the following database schema:

CREATE TABLE `countries` (
  `countryid` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`countryid`)
);

CREATE TABLE `artists` (
  `artistid` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `countryid` int(11) NOT NULL,
  PRIMARY KEY  (`artistid`),
  KEY `countryid` (`countryid`)
);

CREATE TABLE `songs` (
  `songid` int(11) NOT NULL auto_increment,
  `rank` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `artistid` int(11) NOT NULL,
  PRIMARY KEY  (`songid`),
  KEY `artistid` (`artistid`)
);

ALTER TABLE `artists`
  ADD CONSTRAINT `artists_ibfk_1`
  FOREIGN KEY (`countryid`) REFERENCES `countries` (`countryid`)
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `songs`
  ADD CONSTRAINT `songs_ibfk_1`
  FOREIGN KEY (`artistid`) REFERENCES `artists` (`artistid`)
  ON DELETE CASCADE ON UPDATE CASCADE;

Interestingly, the application can be built either using one do-it-all servlet or using three distinct servlets (e.g. ViewCountries initially, then ViewArtists, then ViewSongs). When three such servlets are used, it is important that only one (shared) database connection is used throughout.

Back.