import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StudentDB {

	private Connection con;

	public StudentDB() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			String u = "mocull";
			String p = "lyaN8EVc";
			con = DriverManager.getConnection("jdbc:oracle:thin:@claros.cs.purdue.edu:1524:strep", u, p);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void getStudentsInDepartment(int department) {
		String query = "Select sname from Student where deptid=" + department;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String name = rs.getString("sname");
				System.out.println(name);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void getStudentsInDepartmentPrepared(int department) {
		String stmt = "Select sname from Student where deptid=?";
		try {
			PreparedStatement ps = con.prepareStatement(stmt);
			ps.setInt(1, department);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String sname = rs.getString("sname");
				System.out.println(sname);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
		}
	}

	public void getStudentsInClass(String cname) {
		String query = "Select snum from Enrolled where cname = '" + cname + "'";
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				int snum = rs.getInt("snum");
				String nestedQuery = "Select sname from Student where snum=" + snum;
				Statement stmtNested = con.createStatement();
				ResultSet rsNested = stmtNested.executeQuery(nestedQuery);
				while (rsNested.next()) {
					String sname = rsNested.getString("sname");
					System.out.println(sname);
				}
				rsNested.close();
				stmtNested.close();
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {

		}
	}

	public void enroll(int snum, String cname, String grade) {
		String update = "insert into Enrolled values(" + snum + ", '" + cname + "', '" + grade + "')";
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(update);
			stmt.close();
		} catch (SQLException e) {
		}
	}

	public static void main(String[] args) {
		StudentDB sdb = new StudentDB();

		sdb.getStudentsInDepartment(11);
		System.out.println("-----------------");

		sdb.getStudentsInDepartmentPrepared(11);
		System.out.println("-----------------");

		 sdb.getStudentsInClass("ENG40000");
		 System.out.println("-----------------");
		 sdb.enroll(191, "ENG40000", "A");
		 sdb.getStudentsInClass("ENG40000");
		 System.out.println("-----------------");

	}
}