Lab: 06


Objective

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

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:

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 .

Implementation

Extending the implementation of the previous lab (i.e. bullet 1), accomplish the tasks listed below (i.e. bullet 2 and 3).

  1. Create a DBManager class, which seeks information from the database. The database manager will contact the database via the JDBC driver.
    1. The class should include a method that returns a list of Countries.
      • This list should be sorted either according to names or polulation.
    2. In addtion the class should include a method that shows detailed information of the country.
      • Use prepared statements for the implemetation
  2. The database manager should have a thread that checks out every ten seconds if the database connection has been used.
  3. The database manager should stores the last retrieval into the memory.
  4. Add information regarding the following cities using transactions.
    1. Quetta
      1. District: Quetta
      2. Polulation: 565137
      3. Country: Pakistan
    2. Faisalabad
      1. District: Faisalabad
      2. Population: 2008861
      3. Country: Pakistan
    3. Toba Tek Singh
      1. District: Toba Tek Singh
      2. Population: 1621593
      3. Country: Pakistan
  5. Explore Java Documentation for Batch update and Scrollable update.

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;

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: 19th November, 2007

_blank">online.

Last updated: 19th November, 2007