Lab: 05


Objective

To implement an application allowing us to familiarize ourselves with JDBC and interaction with relational databases.

Your task is to create a catalog of countries, which has functions to list the names in the catalog and to show detailed information of a city in a particular country. The data is stored in a relational database. You are free to choose whether to write a console based application or use develop a GUI.

Please note that you are required to use Log4j.

Resources

Java JDBC API is in Java package java.sql. One should get to know at least the following classes and interfaces:

There is a chapter JDBC Basics in the Java Tutorial, that has good instructions about using the JDBC API. Especially the following subchapters are useful for this assingment:

The DBMS MySQL is deployed at maggie2.niit.edu.pk. You will there be required to use the MySQL Driver. The database is handled with SQL (Structured Query Language). If you do not have any previous experience from SQL, you should read the SQL Introduction . This assignment will focus only on queries done in the database, not adding or modifying information in the database.

Implementation

Create a DBManager class, which seeks information from the database. The database manager will contact the database via the JDBC driver. The class should include a method that returns a list of Countries that is sorted either according to names or polulation. In addtion the class should include a method that shows detailed information of the country.

The tables were created as follows:

 CREATE TABLE `City` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Country` (
`Code` char(3) NOT NULL default '',
`Name` char(52) NOT NULL default '',
`Continent` enum('Asia','Europe','North America','Africa' ,'Oceania','Antarctica','South America') NOT NULL default 'Asia',
`Region` char(26) NOT NULL default '',
`SurfaceArea` float(10,2) NOT NULL default '0.00',
`IndepYear` smallint(6) default NULL,
`Population` int(11) NOT NULL default '0',
`LifeExpectancy` float(3,1) default NULL,
`GNP` float(10,2) default NULL,
`GNPOld` float(10,2) default NULL,
`LocalName` char(45) NOT NULL default '',
`GovernmentForm` char(45) NOT NULL default '',
`HeadOfState` char(60) default NULL,
`Capital` int(11) default NULL,
`Code2` char(2) NOT NULL default '',
PRIMARY KEY (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL default '',
`Language` char(30) NOT NULL default '',
`IsOfficial` enum('T','F') NOT NULL default 'F',
`Percentage` float(4,1) NOT NULL default '0.0',
PRIMARY KEY (`CountryCode`,`Language`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The connection to the database (java.sql.Connection) should be created with a method that returns an existing connection or, if one doesn't exist, creates a new one and returns it. The connection should be closed at least in the finalize() method.

Use java.sql.Statement in methods which lists all the names in the database. In the method that shows detailed information of a specified Country use java.sql.PreparedStatement. When using PreparedStatement, leave a question mark (?) to the SQL sentence on code's place and fill the sentence with setXxx() method before the inquiry.

All the classes that are carried out must belong to the dbmanager package.

In addition to the above referred, execute one of the following:

  1. The database manager has a thread that checks out every ten seconds if the database connection has been used. If the connection has not been used in 30 seconds, it will be closed.
  2. The database manager stores the last retrieval into the memory. When trying to find the information of a specific country you first check the memory. If the information is not found from the memory, it should be queried from the database. When information is queried from the database, it is added into the memory cache and returned to the user. (The usage of so-called "cache" is often wise, because database queries may be time-consuming.) Use java.util.Hashtable or java.util.HashMap as data structure for saving country information.

Instructions for submitting your work

  1. You are required to demonstrate your implementation during the lab.
  2. Create an executable java archive (jar) of your implementation, zip it along with your project folder and submit it online.

Last updated: 30th October, 2007

Last updated: 30th October, 2007