import java.sql.*;
import java.io.File;

/*
 This class demonstrates how to write MDB files by using JDBC API.
*/

public class WriteTest {
  public static void main(String[] args) {
    try {
      // delete an MDB database
      new File("testdb2.mdb").delete();

      // load the driver into memory
      Class.forName("jstels.jdbc.mdb.MDBDriver");

      // create a connection and create an MDB database.
      Connection conn = DriverManager.getConnection("jdbc:jstels:mdb:" + args[0]);

      // create a Statement object to execute the query with
      Statement stmt = conn.createStatement();

      // create the data tables
      stmt.execute("CREATE TABLE products(prodid INTEGER, description STRING(30))");
      stmt.execute("CREATE TABLE regions(id INTEGER, regionname STRING(30))");
      stmt.execute("CREATE TABLE prices(prodid INTEGER, regionid INTEGER, stdprice FLOAT, minprice FLOAT, startdate DATETIME, enddate DATETIME)");

      // insert the records into the tables
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(100860,'Motherboard')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(100861,'Flat Monitor')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(100870,'Processor 5 GHZ')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(100871,'Printer')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(100890,'Digital Camera')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(101860,'Memory Card 1GB')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(101863,'Video Accelerator')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(102130,'Scanner')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(200376,'Network Card')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(200380,'Flash Card')");
      stmt.execute(
	  "INSERT INTO products(prodid, description) VALUES(300001,'LCD Monitor')");
      stmt.execute("INSERT INTO regions(id, regionname) VALUES(100,'Europe')");
      stmt.execute(
	  "INSERT INTO regions(id, regionname) VALUES(101,'North America')");
      stmt.execute(
	  "INSERT INTO regions(id, regionname) VALUES(102,'South America')");
      stmt.execute("INSERT INTO regions(id, regionname) VALUES(103,'Africa')");
      stmt.execute("INSERT INTO regions(id, regionname) VALUES(104,'Asia')");
      stmt.execute(
	  "INSERT INTO regions(id, regionname) VALUES(105,'Australia')");
      stmt.execute(
	  "INSERT INTO regions(id, regionname) VALUES(106,'Grenlandia')");
      stmt.execute("INSERT INTO regions(id, regionname) VALUES(107,'Arctica')");
      stmt.execute(
	  "INSERT INTO regions(id, regionname) VALUES(108,'Antarctida')");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100860,101,130,124,to_date('01-01-2002','dd-MM-yyyy'),to_date('21-01-2002','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100860,102,132,125.6,to_date('01-01-2002','dd-MM-yyyy'),to_date('21-01-2002','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100860,103,135,128,to_date('01-06-2003','dd-MM-yyyy'),to_date('01-07-2003','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100861,105,239,231.2,to_date('01-01-2002','dd-MM-yyyy'),to_date('10-01-2002','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100861,107,242,233.6,to_date('01-05-2003','dd-MM-yyyy'),to_date('01-06-2003','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100861,106,245,236,to_date('12-01-2002','dd-MM-yyyy'),to_date('12-03-2002','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100871,101,154,153.2,to_date('01-08-2002','dd-MM-yyyy'),to_date('01-09-2002','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100890,108,445,440.5,to_date('01-09-2002','dd-MM-yyyy'),to_date('01-10-2002','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(100890,105,449.7,446.4,to_date('03-11-2002','dd-MM-yyyy'),to_date('03-12-2002','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(101863,102,98.0,99.1,to_date('01-12-2002','dd-MM-yyyy'),to_date('11-01-2003','dd-MM-yyyy'))");
      stmt.execute("INSERT INTO prices(prodid, regionid, stdprice, minprice, startdate, enddate) VALUES(102130,103,178.9,182.5,to_date('01-04-2002','dd-MM-yyyy'),to_date('01-05-2002','dd-MM-yyyy'))");

      // test inserted data by using an SQL query
      ResultSet rs = stmt.executeQuery(
	  "SELECT  prod.description AS \"Product Name\", regs.regionname AS \"Region Name\", " +
	  "minprice AS \"Min Price\", stdprice AS \"Standard Price\", " +
	  "to_string(startdate, \'dd MMMMM yyyy\' ) AS \"Start Date\", " +
	  "to_string(enddate, \'dd MMMMM yyyy\') AS \"End Date\" FROM prices ps " +
	  "JOIN regions regs ON ps.regionid = regs.id JOIN products prod " +
	  "ON prod.prodid = ps.prodid ORDER BY \"Product Name\"");

      // read the data and put it to the console
      for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {
	System.out.print(rs.getMetaData().getColumnName(j) + "\t");
      }
      System.out.println();

      while (rs.next()) {
	for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {
	  System.out.print(rs.getObject(j) + "\t");
	}
	System.out.println();
      }

      // close the objects
      rs.close();
      stmt.close();
      conn.close();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}
