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:

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.