/*
 * This program produces class lists for the course sections in the
 * studentDB database.  
 */

// You need to import the java.sql package to use JDBCimport java.sql.*;
import java.sql.*;

public class ClassLists {
  public static void main(String[] args) {
    // Connect to the database
    // You can put a database name before the ? in the connection URL.
    Connection conn = null;
    String url = "jdbc:mysql://sql.cs.oberlin.edu/studentdb?";
    String parms = "user=alice&password=tiger&useSSL=false";
    
    try {
      conn = DriverManager.getConnection(url+parms);
	
      // Create a PreparedStatement
      PreparedStatement stmt =
	conn.prepareStatement("select * from course,section,enroll,student"+
	   " where cid=courseid and sectid=sectionid and studentid=sid"+
			      " order by cid,sectid,studentid");

      // Get data from the student database
      ResultSet rset = stmt.executeQuery();

      // Iterate through the result set and print the report
      int ct = 0;
      int oldcid = -1;
      int oldsectid = -1;
      while (rset.next () && ct<100){
	  int cid = rset.getInt("cid");
	  /* if it's a new course, print the course title */
	  if(oldcid!=cid){
	      System.out.println(rset.getString("title")+":");
	      oldcid = cid;
	      oldsectid = -1;
	  }
	  int sectid = rset.getInt("sectid");
	  /* if it's a new section, print the section number */
	  if(oldsectid!=sectid){
	      System.out.println("\tsection "+sectid+":");
	      oldsectid = sectid;
	  }
	  System.out.println("\t\t"+rset.getString("sid")+" "+rset.getString("sname"));
	++ct;
      }

      // close the ResultSet
      if(rset != null)
	rset.close();
      // close the connection
      if (conn != null)
	conn.close();
    }
    catch(SQLException e) {
      e.printStackTrace();
    }
    catch(Exception ee){
      ee.printStackTrace();
    }
  }
}