CS3223 - No JDBC

ChangeMajor.java

package tests;

import simpledb.tx.Transaction;
import simpledb.plan.Planner;
import simpledb.server.SimpleDB;

/* This is a version of the StudentMajor program that
 * accesses the SimpleDB classes directly (instead of
 * connecting to it as a JDBC client). 
 * 
 * These kind of programs are useful for debugging
 * your changes to the SimpleDB source code.
 */
public class ChangeMajor {
   public static void main(String[] args) {
      // analogous to the driver
      SimpleDB db = new SimpleDB("studentdb");

      // analogous to the connection
      Transaction tx = db.newTx();
      Planner planner = db.planner();

      // analogous to the statement
      String cmd = "update STUDENT set MajorId=30 "
            + "where SName = 'amy'";
      planner.executeUpdate(cmd, tx);
      tx.commit();

      System.out.println("Amy is now a drama major.");
   }
}

CreateStudentDB.java

package tests;

import simpledb.tx.Transaction;
import simpledb.plan.Planner;
import simpledb.server.SimpleDB;

/* This is a version of the StudentMajor program that
 * accesses the SimpleDB classes directly (instead of
 * connecting to it as a JDBC client). 
 * 
 * These kind of programs are useful for debugging
 * your changes to the SimpleDB source code.
 */
public class CreateStudentDB {
   public static void main(String[] args) {
      // analogous to the driver
      SimpleDB db = new SimpleDB("studentdb");

      // analogous to the connection
      Transaction tx = db.newTx();
      Planner planner = db.planner();

      String cmd = "create table STUDENT(SId int, SName varchar(10), MajorId int, GradYear int)";
      planner.executeUpdate(cmd, tx);
      System.out.println("Table STUDENT created.");

      cmd = "insert into STUDENT(SId, SName, MajorId, GradYear) values ";
      String[] studvals = { "(1, 'joe', 10, 2021)",
            "(2, 'amy', 20, 2020)",
            "(3, 'max', 10, 2022)",
            "(4, 'sue', 20, 2022)",
            "(5, 'bob', 30, 2020)",
            "(6, 'kim', 20, 2020)",
            "(7, 'art', 30, 2021)",
            "(8, 'pat', 20, 2019)",
            "(9, 'lee', 10, 2021)" };
      for (int i = 0; i < studvals.length; i++)
         planner.executeUpdate(cmd + studvals[i], tx);

      System.out.println("STUDENT records inserted.");

      cmd = "create table DEPT(DId int, DName varchar(8))";

      planner.executeUpdate(cmd, tx);

      System.out.println("Table DEPT created.");

      cmd = "insert into DEPT(DId, DName) values ";
      String[] deptvals = { "(10, 'compsci')",
            "(20, 'math')",
            "(30, 'drama')" };
      for (int i = 0; i < deptvals.length; i++)
         planner.executeUpdate(cmd + deptvals[i], tx);

      System.out.println("DEPT records inserted.");

      cmd = "create table COURSE(CId int, Title varchar(20), DeptId int)";
      planner.executeUpdate(cmd, tx);

      System.out.println("Table COURSE created.");

      cmd = "insert into COURSE(CId, Title, DeptId) values ";
      String[] coursevals = { "(12, 'db systems', 10)",
            "(22, 'compilers', 10)",
            "(32, 'calculus', 20)",
            "(42, 'algebra', 20)",
            "(52, 'acting', 30)",
            "(62, 'elocution', 30)" };
      for (int i = 0; i < coursevals.length; i++)
         planner.executeUpdate(cmd + coursevals[i], tx);

      System.out.println("COURSE records inserted.");

      cmd = "create table SECTION(SectId int, CourseId int, Prof varchar(8), YearOffered int)";
      planner.executeUpdate(cmd, tx);

      System.out.println("Table SECTION created.");

      cmd = "insert into SECTION(SectId, CourseId, Prof, YearOffered) values ";
      String[] sectvals = { "(13, 12, 'turing', 2018)",
            "(23, 12, 'turing', 2019)",
            "(33, 32, 'newton', 2019)",
            "(43, 32, 'einstein', 2017)",
            "(53, 62, 'brando', 2018)" };
      for (int i = 0; i < sectvals.length; i++)
         planner.executeUpdate(cmd + sectvals[i], tx);

      System.out.println("SECTION records inserted.");

      cmd = "create table ENROLL(EId int, StudentId int, SectionId int, Grade varchar(2))";
      planner.executeUpdate(cmd, tx);

      System.out.println("Table ENROLL created.");

      cmd = "insert into ENROLL(EId, StudentId, SectionId, Grade) values ";
      String[] enrollvals = { "(14, 1, 13, 'A')",
            "(24, 1, 43, 'C' )",
            "(34, 2, 43, 'B+')",
            "(44, 4, 33, 'B' )",
            "(54, 4, 53, 'A' )",
            "(64, 6, 53, 'A' )" };
      for (int i = 0; i < enrollvals.length; i++)
         planner.executeUpdate(cmd + enrollvals[i], tx);

      System.out.println("ENROLL records inserted.");

      tx.commit();
   }
}

FindMajors.java

package tests;

import simpledb.tx.Transaction;
import simpledb.plan.Plan;
import simpledb.plan.Planner;
import simpledb.query.*;
import simpledb.server.SimpleDB;
import java.util.Scanner;

/* This is a version of the StudentMajor program that
 * accesses the SimpleDB classes directly (instead of
 * connecting to it as a JDBC client). 
 * 
 * These kind of programs are useful for debugging
 * your changes to the SimpleDB source code.
 */
public class FindMajors {
   public static void main(String[] args) {
      try {
         System.out.print("Enter a department name: ");
         Scanner sc = new Scanner(System.in);
         String major = sc.next();
         sc.close();
         System.out.println("Here are the " + major + " majors");
         System.out.println("Name\tGradYear");

         // analogous to the driver
         SimpleDB db = new SimpleDB("studentdb");

         // analogous to the connection
         Transaction tx = db.newTx();
         Planner planner = db.planner();

         String qry = "select sname, gradyear "
               + "from student, dept "
               + "where did = majorid "
               + "and dname = '" + major + "'";
         Plan p = planner.createQueryPlan(qry, tx);

         // analogous to the result set
         Scan s = p.open();

         while (s.next()) {
            String sname = s.getString("sname");
            int gradyear = s.getInt("gradyear");
            System.out.println(sname + "\t" + gradyear);
         }
         s.close();
         tx.commit();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

StudentMajor.java

package tests;

import simpledb.tx.Transaction;
import simpledb.plan.Plan;
import simpledb.plan.Planner;
import simpledb.query.*;
import simpledb.server.SimpleDB;

/* This is a version of the StudentMajor program that
 * accesses the SimpleDB classes directly (instead of
 * connecting to it as a JDBC client). 
 * 
 * These kind of programs are useful for debugging
 * your changes to the SimpleDB source code.
 */

public class StudentMajor {
   public static void main(String[] args) {
      try {
         // analogous to the driver
         SimpleDB db = new SimpleDB("studentdb");

         // analogous to the connection
         Transaction tx  = db.newTx();
         Planner planner = db.planner();
         
         // analogous to the statement
         String qry = "select SName, DName "
               + "from DEPT, STUDENT "
               + "where MajorId = DId";
         Plan p = planner.createQueryPlan(qry, tx);
         
         // analogous to the result set
         Scan s = p.open();
         
         System.out.println("Name\tMajor");
         while (s.next()) {
            String sname = s.getString("sname"); //SimpleDB stores field names
            String dname = s.getString("dname"); //in lower case
            System.out.println(sname + "\t" + dname);
         }
         s.close();
         tx.commit();
      }
      catch(Exception e) {
         e.printStackTrace();
      }
   }
}