import java.sql.* ;
import java.util.Properties;

public class BathUniDatabase {

	public static void main(String[] args){
		BathUniDatabase db = new BathUniDatabase();
		Connection con;
		try {
			con = db.getConnection();
			db.viewStudentTable(con, "bathuni");
			System.out.println("\n---\n");
			// *** Edit Student Details ***
			//db.editStudentTable(con,"bathuni",1,"Michael");
			//db.editStudentTable(con,"bathuni",4,"Louise");
			//db.viewStudentTable(con, "bathuni");
			//System.out.println("\n---\n");
			// *** Enrol a Student ***
			//db.enrolStudent(con, "bathuni", "Maria");
			db.viewStudentTable(con, "bathuni");
			// *** Un-Enrol a Student ***
			db.unenrolStudent(con, "bathuni", 16);
			db.viewStudentTable(con, "bathuni");
		} catch (SQLException e) {
			System.err.println(e.toString());
		}
	}

	public Connection getConnection()
	    throws SQLException {

	    Properties connectionProps = new Properties();
	    connectionProps.put("user", "root");
	    connectionProps.put("password", "");
	    Connection conn = DriverManager.getConnection(
	                   "jdbc:mysql://localhost:3306/",
	                   connectionProps);

	    System.out.println("Connected to database");
	    return conn;
	}

	public void viewStudentTable(Connection con, String dbName) throws SQLException{
		Statement stmt = null;
		String query = "select * from " + dbName + ".student";
		ResultSet rs = null;
		try {
	        stmt = con.createStatement();
	        rs = stmt.executeQuery(query);
	        while (rs.next()) {
	            int studentID = rs.getInt("ID");
	            String name = rs.getString("studentname");
	            System.out.println(studentID + "\t" + name);
	        }
	    }
		catch (SQLException e ) {
	        System.err.println(e.toString());
	    }
		finally {
	        if (stmt != null){
	        	stmt.close();
	        }
	    }
	}

	public void editStudentTable(Connection con, String dbName, int ID, String name) throws SQLException{
		Statement stmt = null;
		String query = "update " + dbName + ".student set studentname = \"" + name + "\" where ID = " + ID;
		try {
	        stmt = con.createStatement();
	        stmt.executeUpdate(query);
	    }
		catch (SQLException e ) {
	        System.err.println(e.toString());
	    }
		finally {
	        if (stmt != null){
	        	stmt.close();
	        }
	    }
	}

	public void enrolStudent(Connection con, String dbName, String name) throws SQLException{
		Statement stmt = null;
		String query = "insert into " + dbName + ".student values(NULL,\"" + name + "\")";
		try {
	        stmt = con.createStatement();
	        stmt.executeUpdate(query);
	    }
		catch (SQLException e ) {
	        System.err.println(e.toString());
	    }
		finally {
	        if (stmt != null){
	        	stmt.close();
	        }
	    }
	}

	public void unenrolStudent(Connection con, String dbName, int ID) throws SQLException{
		Statement stmt = null;
		String query = "delete from " + dbName + ".student where ID = " + ID;
		try {
	        stmt = con.createStatement();
	        stmt.executeUpdate(query);
	    }
		catch (SQLException e ) {
	        System.err.println(e.toString());
	    }
		finally {
	        if (stmt != null){
	        	stmt.close();
	        }
	    }
	}
}