import java.util.*;
import java.sql.*;
/**
* The FiatData class cleans out any existing copies and makes
* new copies of certain tables in the database to which it connects.
*/
public class FiatData {
private static void dropAllTables (Statement stmt) throws SQLException {
stmt.executeUpdate( "DROP TABLE IF EXISTS EXTRAINFO" );
stmt.executeUpdate( "DROP TABLE IF EXISTS PCSYSTEM" );
stmt.executeUpdate( "DROP TABLE IF EXISTS PCCASE" );
stmt.executeUpdate( "DROP TABLE IF EXISTS PWRSPLY" );
stmt.executeUpdate( "DROP TABLE IF EXISTS MAINBOARD" );
stmt.executeUpdate( "DROP TABLE IF EXISTS CPU" );
stmt.executeUpdate( "DROP TABLE IF EXISTS MEMORY" );
stmt.executeUpdate( "DROP TABLE IF EXISTS VIDEO" );
stmt.executeUpdate( "DROP TABLE IF EXISTS SOUND" );
stmt.executeUpdate( "DROP TABLE IF EXISTS ETHER" );
stmt.executeUpdate( "DROP TABLE IF EXISTS FLOPPY" );
stmt.executeUpdate( "DROP TABLE IF EXISTS HARDDRIVE" );
stmt.executeUpdate( "DROP TABLE IF EXISTS CDDRIVE" );
stmt.executeUpdate( "DROP TABLE IF EXISTS KEYBOARD" );
stmt.executeUpdate( "DROP TABLE IF EXISTS MOUSE" );
stmt.executeUpdate( "DROP TABLE IF EXISTS MONITOR" );
stmt.executeUpdate( "DROP TABLE IF EXISTS PRINTER" );
stmt.executeUpdate( "DROP TABLE IF EXISTS SPEAKERS" );
}
private static void makeExtraInfo(Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE extrainfo " +
"( tname VARCHAR(15)," +
" classname VARCHAR(25)," +
" PRIMARY KEY (tname))" );
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('video', 'Video card' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('speakers', 'External Speakers' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('sound', 'Sound Card' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('pwrsply', 'Power Supply' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('printer', 'Printer' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('pcsystem', 'PC System' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('pccase', 'System enclosure' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('mouse', 'Pointing Device' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('monitor', 'Monitor' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('cddrive', 'Optical Drive' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('cpu', 'Processor' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('ether', 'LAN card' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('floppy', 'Floppy Drive' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('harddrive', 'Hard Disk' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('keyboard', 'Keyboard' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('mainboard', 'System main board' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('memory', 'On-board Memory' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('tabwidth', '4' )");
stmt.executeUpdate( "INSERT INTO extrainfo VALUES " +
"('costtab', '50' )");
}
private static void makePCSystem (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE pcsystem " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" fkpccase VARCHAR(10)," +
" fkkeyboard VARCHAR(10)," +
" fkmouse VARCHAR(10)," +
" fkmonitor VARCHAR(10)," +
" fkprinter VARCHAR(10)," +
" fkspeakers VARCHAR(10)," +
" PRIMARY KEY (prikey))" );
stmt.executeUpdate( "INSERT INTO pcsystem VALUES " +
"('SYS-1', 'Entry level system', '24.99', " +
"'ENC-1', 'KB-1', 'MM-1', 'MON-1', 'PRN-1', " +
"'SPK-1' )" );
stmt.executeUpdate( "INSERT INTO pcsystem VALUES " +
"('SYS-2', 'Business system', '24.99', " +
"'ENC-2', 'KB-2', 'MM-2', 'MON-2', 'PRN-3', " +
"'SPK-2' )" );
stmt.executeUpdate( "INSERT INTO pcsystem VALUES " +
"('SYS-3', 'Moby system', '24.99', " +
"'ENC-3', 'KB-2', 'MM-2', 'MON-3', 'PRN-2', " +
"'SPK-3' )" );
}
private static void makePCCase (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE pccase " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" fkpwrsply VARCHAR(10)," +
" fkmainboard VARCHAR(10)," +
" fkfloppy VARCHAR(10)," +
" fkharddrive VARCHAR(10)," +
" fkcddrive VARCHAR(10)," +
" PRIMARY KEY (prikey))" );
stmt.executeUpdate( "INSERT INTO pccase VALUES " +
"('ENC-1', 'Basic ATX Mid-tower', '19.99', " +
"'PS-1', 'MB-1', 'FD-1', 'HD-1', 'CD-1' )" );
stmt.executeUpdate( "INSERT INTO pccase VALUES " +
"('ENC-2', 'Universal Mid-tower', '29.97', " +
"'PS-2', 'MB-2', 'FD-1', 'HD-1', 'CD-2' )" );
stmt.executeUpdate( "INSERT INTO pccase VALUES " +
"('ENC-3', 'Gamer showoff case', '144.99', " +
"'PS-3', 'MB-3', 'FD-2', 'HD-2', 'CD-2' )" );
}
private static void makePwrSply (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE pwrsply " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO pwrsply VALUES " +
"('PS-1', 'Generic 300W', '19.99' )");
stmt.executeUpdate( "INSERT INTO pwrsply VALUES " +
"('PS-2', 'ForAthlon 400W', '22.99' )");
stmt.executeUpdate( "INSERT INTO pwrsply VALUES " +
"('PS-3', 'Gamer 600W', '79.99' )");
}
private static void makeMainboard (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE mainboard " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" fkcpu VARCHAR(10)," +
" fkmemory VARCHAR(10)," +
" fkvideo VARCHAR(10)," +
" fksound VARCHAR(10)," +
" fkether VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO mainboard VALUES " +
"('MB-1', 'Entry level MB', '84.99', 'CPU-1', " +
"'MEM-1', 'VID-1', 'AUD-1', 'NET-1' )");
stmt.executeUpdate( "INSERT INTO mainboard VALUES " +
"('MB-2', 'Business MB', '136.99', 'CPU-2', " +
"'MEM-2', 'VID-2', 'AUD-2', 'NET-1' )");
stmt.executeUpdate( "INSERT INTO mainboard VALUES " +
"('MB-3', 'Power User MB', '136.99', 'CPU-2', " +
"'MEM-3', 'VID-3', 'AUD-2', 'NET-2' )");
}
private static void makeCpu (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE cpu " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO cpu VALUES " +
"('CPU-1', 'PIII 866 Coppermine', '39.95' )");
stmt.executeUpdate( "INSERT INTO cpu VALUES " +
"('CPU-2', 'Athlon XP 2400', '74.99' )");
stmt.executeUpdate( "INSERT INTO cpu VALUES " +
"('CPU-3', 'Celeron 335', '99.99' )");
}
private static void makeMemory (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE memory " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO memory VALUES " +
"('MEM-1', '512 MB', '99.99' )");
stmt.executeUpdate( "INSERT INTO memory VALUES " +
"('MEM-2', '1 GB', '199.99' )");
stmt.executeUpdate( "INSERT INTO memory VALUES " +
"('MEM-3', '2 GB', '349.99' )");
}
private static void makeVideo (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE video " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO video VALUES " +
"('VID-1', 'NoName PCI', '12.95' )");
stmt.executeUpdate( "INSERT INTO video VALUES " +
"('VID-2', 'Radeon 128MB', '49.99' )");
stmt.executeUpdate( "INSERT INTO video VALUES " +
"('VID-3', 'RenderMonster 384MB', '1859.99' )");
}
private static void makeSound (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE sound " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO sound VALUES " +
"('AUD-1', 'SoundBlaster 16 PCI', '15.99' )");
stmt.executeUpdate( "INSERT INTO sound VALUES " +
"('AUD-2', 'SoundBlaster Audigy 2', '69.99' )");
}
private static void makeEther (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE ether " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO ether VALUES " +
"('NET-1', 'DLink 10/100', '4.99' )");
stmt.executeUpdate( "INSERT INTO ether VALUES " +
"('NET-2', 'Trendware 10/100/1000', '29.99' )");
}
private static void makeFloppy (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE floppy " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO floppy VALUES " +
"('FD-1', 'Sony 3.5 1.44 Meg', '17.99' )");
stmt.executeUpdate( "INSERT INTO floppy VALUES " +
"('FD-2', 'Mitsumi All-Media', '34.99' )");
}
private static void makeHardDrive (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE harddrive " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO harddrive VALUES " +
"('HD-1', 'Hitachi 80 GB', '64.99' )");
stmt.executeUpdate( "INSERT INTO harddrive VALUES " +
"('HD-2', 'Maxtor 160 GB', '104.99' )");
}
private static void makeCDDrive (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE cddrive " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO cddrive VALUES " +
"('CD-1', 'CD-RW 52X', '24.99' )");
stmt.executeUpdate( "INSERT INTO cddrive VALUES " +
"('CD-2', 'Pioneer DVD RW', '85.99' )");
}
private static void makeKeyboard (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE keyboard " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO keyboard VALUES " +
"('KB-1', 'Generic Crap', '7.99' )");
stmt.executeUpdate( "INSERT INTO keyboard VALUES " +
"('KB-2', 'Avant Prime', '148.99' )");
}
private static void makeMouse (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE mouse " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO mouse VALUES " +
"('MM-1', 'IBM 3-button', '5.99' )");
stmt.executeUpdate( "INSERT INTO mouse VALUES " +
"('MM-2', 'optical scrollmouse', '9.99' )");
}
private static void makeMonitor (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE monitor " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO monitor VALUES " +
"('MON-1', 'XGA 17 CRT', '49.99' )");
stmt.executeUpdate( "INSERT INTO monitor VALUES " +
"('MON-2', 'Viewsonic E90FB', '229.99' )");
stmt.executeUpdate( "INSERT INTO monitor VALUES " +
"('MON-3', 'Sony 19-inch LCD', '599.99' )");
}
private static void makePrinter (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE printer " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO printer VALUES " +
"('PRN-1', 'HP Deskjet 3520', '39.99' )");
stmt.executeUpdate( "INSERT INTO printer VALUES " +
"('PRN-2', 'Epson Stylus Photo R800', '349.99' )");
stmt.executeUpdate( "INSERT INTO printer VALUES " +
"('PRN-3', 'Brother HL-1435', '149.99' )");
}
private static void makeSpeakers (Statement stmt) throws SQLException {
stmt.executeUpdate( "CREATE TABLE speakers " +
"( prikey VARCHAR(10)," +
" name VARCHAR(30)," +
" owncost VARCHAR(10)," +
" PRIMARY KEY (prikey))");
stmt.executeUpdate( "INSERT INTO speakers VALUES " +
"('SPK-1', 'Logitech X-120', '9.99' )");
stmt.executeUpdate( "INSERT INTO speakers VALUES " +
"('SPK-2', 'JBL Duet', '44.99' )");
stmt.executeUpdate( "INSERT INTO speakers VALUES " +
"('SPK-3', 'Klipsch Promedia Ultra 5.1', '349.99' )");
}
/** Creates a clean set of database tables.
* @param args the command line arguments
*/
public static void main(String args[]) {
Connection con = null;
String jdbcURL = "jdbc:mysql:///task2?user=root";
String driverName = "com.mysql.jdbc.Driver";
try { // load the driver
Class.forName(driverName);
}
catch( Exception e ) { // problem loading driver,
// driver class doesn't exist?
e.printStackTrace( );
return;
}
try {
con = DriverManager.getConnection(jdbcURL);
Statement stmt = con.createStatement();
System.out.println("Connection successful!");
try {
dropAllTables(stmt);
makeExtraInfo(stmt);
makePCSystem(stmt);
makePCCase(stmt);
makePwrSply(stmt);
makeMainboard(stmt);
makeCpu(stmt);
makeMemory(stmt);
makeVideo(stmt);
makeSound(stmt);
makeEther(stmt);
makeFloppy(stmt);
makeHardDrive(stmt);
makeCDDrive(stmt);
makeKeyboard(stmt);
makeMouse(stmt);
makeMonitor(stmt);
makePrinter(stmt);
makeSpeakers(stmt);
}
catch (SQLException e){
System.out.println("Oops! - " + e);
}
}
catch( SQLException e ) {
e.printStackTrace( );
}
finally {
if( con != null ) {
try { con.close( ); }
catch( SQLException e ) {
e.printStackTrace( );
}
}
}
}
} // end class