Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.*;
- import java.util.Random;
- import java.util.Scanner;
- public class Exercise2 {
- private static String url = "jdbc:postgresql://dbteach2/";
- private static String username = "lxe307";
- private static String pass = "wrumelib";
- static Scanner scanny = new Scanner(System.in);
- public static void main(String[] args) throws SQLException {
- try {
- Class.forName("org.postgresql.Driver");
- } catch (ClassNotFoundException ex) {
- System.out.println("Driver not found");
- }
- System.out.println("PostgreSQL driver registered.")
- ;
- Connection conn = null;
- try {
- conn = DriverManager.getConnection(url, username, pass);
- } catch (SQLException ex) {
- ex.printStackTrace();
- }
- if (conn != null) {
- System.out.println("Database accessed!");
- } else {
- System.out.println("Failed to make connection");
- }
- try
- {
- PreparedStatement dropTable = conn.prepareStatement(
- "DROP TABLE IF EXISTS Student CASCADE; DROP TABLE IF EXISTS Lecturer CASCADE;" +
- "DROP TABLE IF EXISTS Module CASCADE;DROP TABLE IF EXISTS Marks CASCADE;"+
- "DROP TABLE IF EXISTS StudentContact CASCADE; DROP TABLE IF EXISTS NextOfKinContact CASCADE; " +
- "DROP TABLE IF EXISTS Titles CASCADE;DROP TABLE IF EXISTS Type CASCADE;");
- dropTable.executeUpdate();
- System.out.println("Tables dropped");
- try {
- conn.setAutoCommit(false);
- }
- catch (SQLException ex){
- ex.printStackTrace();
- }
- PreparedStatement createTitlesT = conn.prepareStatement(
- "CREATE TABLE Titles("+
- "titleID INTEGER NOT NULL UNIQUE,"+
- "titleString CHAR(30), CHECK (titleString ='Mr' OR "+
- "titleString ='Mrs' OR "+
- "titleString = 'Ms' OR "+
- "titleString ='Miss' OR "+
- "titleString ='Dr' OR "+
- "titleString ='Master' ),"+
- "PRIMARY KEY(titleID)"+
- ");"
- );
- createTitlesT.executeUpdate();
- System.out.println("Titles Table created");
- String titlesString = "Insert into Titles VALUES (1, 'Mr');"+
- "Insert into Titles VALUES (2, 'Mrs');"+
- "Insert into Titles VALUES (3, 'Ms');"+
- "Insert into Titles VALUES (4, 'Miss');"+
- "Insert into Titles VALUES (5, 'Dr');"+
- "Insert into Titles VALUES (6, 'Master');";
- PreparedStatement addTitles = conn.prepareStatement(titlesString);
- addTitles.executeUpdate();
- PreparedStatement createTypeT = conn.prepareStatement(
- "CREATE TABLE Type("+
- "typeID INTEGER NOT NULL UNIQUE,"+
- "typeString CHAR(30),"+
- "PRIMARY KEY(typeID)"+
- ");"
- );
- createTypeT.executeUpdate();
- System.out.println("Type Table created");
- String typeString = "Insert into Type VALUES (0, 'sit');"+
- "Insert into Type VALUES (1, 'resit');"+
- "Insert into Type VALUES (2, 'repeat');";
- PreparedStatement addType = conn.prepareStatement(typeString);
- addType.executeUpdate();
- PreparedStatement createStudentT = conn.prepareStatement(
- "CREATE TABLE Student("+
- "studentID INTEGER NOT NULL UNIQUE,"+
- "titleID INTEGER NOT NULL,"+
- "forename CHAR(20),"+
- "familyName CHAR(30),"+
- "dateOfBirth DATE,"+//maybe check constraints???
- "PRIMARY KEY(studentID),"+
- "FOREIGN KEY(titleID) REFERENCES Titles(titleID)"+
- ");"
- );
- createStudentT.executeUpdate();
- System.out.println("Student Table created");
- Random randy = new Random();
- String addStudents="";
- for(int i=1;i<101;i++) {
- addStudents = addStudents+"Insert into Student VALUES (" + i + ", " +
- ((Integer)(1+randy.nextInt(5))).toString() + ", '" + RandomName.getForename() +
- "', '" + RandomName.getSurname() + "', '10-09-1994');";
- }
- PreparedStatement addStudentT = conn.prepareStatement(addStudents);
- addStudentT.executeUpdate();
- PreparedStatement createLecturerT = conn.prepareStatement(
- "CREATE TABLE Lecturer("+
- "lecturerID INTEGER NOT NULL UNIQUE,"+
- "titleID INTEGER NOT NULL,"+
- "foreName CHAR(20),"+
- "familyName CHAR(30),"+
- "PRIMARY KEY(lecturerID),"+
- "FOREIGN KEY(titleID) REFERENCES Titles(titleID)"+
- ");"
- );
- createLecturerT.executeUpdate();
- System.out.println("Lecturer Table created");
- String addLecturers="";
- for(int a=1; a<6; a++) {
- addLecturers = addLecturers+"Insert into Lecturer VALUES (" + a + ", " +
- ((Integer)(1+randy.nextInt(5))).toString() + ", '" + RandomName.getForename() +
- "', '" + RandomName.getSurname() + "');";
- }
- PreparedStatement addLecturerT = conn.prepareStatement(addLecturers);
- addLecturerT.executeUpdate();
- PreparedStatement createModuleT = conn.prepareStatement(
- "CREATE TABLE Module("+
- "moduleID INTEGER NOT NULL UNIQUE,"+
- "moduleName CHAR(50),"+
- "moduleDescription CHAR(50),"+
- "lecturerID INTEGER NOT NULL,"+
- "PRIMARY KEY(moduleID),"+
- "FOREIGN KEY(lecturerID) REFERENCES Lecturer(lecturerID)"+
- ");"
- );
- createModuleT.executeUpdate();
- System.out.println("Module Table created");
- String addModules="";
- for(int i=1; i<101; i++) {
- addModules = addModules+"Insert into Module VALUES (" + i + ", 'Module Name "
- + i + "', 'Module Description " + i + "', '" +
- ((Integer)(1+randy.nextInt(5))).toString() + "');";
- }
- PreparedStatement addModuleT = conn.prepareStatement(addModules);
- addModuleT.executeUpdate();
- PreparedStatement createMarksT = conn.prepareStatement(
- "CREATE TABLE Marks("+
- "year CHAR(10),"+
- "mark INTEGER,"+
- "notes CHAR(100),"+
- "typeID INTEGER NOT NULL,"+
- "moduleID INTEGER NOT NULL,"+
- "studentID INTEGER NOT NULL,"+
- "FOREIGN KEY(moduleID) REFERENCES Module(moduleID),"+
- "FOREIGN KEY(studentID) REFERENCES Student(studentID)"+
- ");"
- );
- createMarksT.executeUpdate();
- System.out.println("Marks Table created");
- String addMarks="";
- for(int b=1; b<101; b++) {
- addMarks = addMarks+"Insert into Marks VALUES ('2013-14 ', " +
- ((Integer)(1+randy.nextInt(100))).toString() +
- ", 'Notes '," + ((Integer)(randy.nextInt(3))).toString() +
- ", " + ((Integer)(1+randy.nextInt(100))).toString() +
- ", " + ((Integer)(b)).toString() + ");";
- }
- PreparedStatement addMarksT = conn.prepareStatement(addMarks);
- addMarksT.executeUpdate();
- PreparedStatement alterMarksT = conn.prepareStatement(
- "ALTER TABLE Marks ADD CONSTRAINT marks_Key UNIQUE (studentID, moduleID);");
- alterMarksT.executeUpdate();
- PreparedStatement createStudentContactT = conn.prepareStatement(
- "CREATE TABLE StudentContact("+
- "studentID INTEGER NOT NULL UNIQUE,"+
- "eMailAddress CHAR(50),"+
- "postalAddress CHAR(90),"+
- "FOREIGN KEY(studentID) references Student(studentID)"+
- ");"
- );
- createStudentContactT.executeUpdate();
- System.out.println("StudentContact table created");
- String addStuCon="";
- for(int c=1; c<101; c++) {
- addStuCon=addStuCon+"Insert into StudentContact VALUES (" + c
- + ", 'eMailAddress " + c + "', 'postalAddress " + c + "');";
- }
- PreparedStatement addStuConT = conn.prepareStatement(addStuCon);
- addStuConT.executeUpdate();
- PreparedStatement createNextOfKinContactT = conn.prepareStatement(
- "CREATE TABLE NextOfKinContact("+
- "studentID INTEGER NOT NULL UNIQUE,"+
- "eMailAddress CHAR(50),"+
- "postalAddress CHAR(90),"+
- "FOREIGN KEY(studentID) REFERENCES Student(studentID)"+
- ");"
- );
- createNextOfKinContactT.executeUpdate();
- System.out.println("NextOfKinContact table created");
- String addNextKin="";
- for(int d=1;d<101;d++) {
- addNextKin=addNextKin+"Insert into NextOfKinContact VALUES (" + d
- + ", 'eMailAddress " + d + "', 'postalAddress " + d + "');";
- }
- PreparedStatement addNextKinT = conn.prepareStatement(addNextKin);
- addNextKinT.executeUpdate();
- try {
- conn.commit();
- conn.setAutoCommit(true);
- }
- catch (SQLException ex) {
- ex.printStackTrace();
- }
- }
- catch (SQLException e)
- {
- e.printStackTrace();
- }
- //Scanner s = new Scanner(System.in);
- String userChoice = "0"; //user's input
- int option = 0; //what the program will do
- do {
- System.out.println("Menu");
- System.out.println("Press 1 to register new student");
- System.out.println("Press 2 to add marks");
- System.out.println("Press 3 to view student transcript");
- System.out.println("Press 4 to view report");
- System.out.println("Press 5 to quit");
- System.out.println("");
- userChoice = scanny.nextLine();
- System.out.println(userChoice);
- if(userChoice.equals("1"))
- {
- option=1;
- System.out.println("1 entered");
- }
- else if(userChoice.equals("2"))
- {
- option=2;
- System.out.println("2 entered");
- }
- else if(userChoice.equals("3"))
- {
- option=3;
- System.out.println("3 entered");
- }
- else if(userChoice.equals("4"))
- {
- option=4;
- System.out.println("4 entered");
- }
- else if(userChoice.equals("5"))
- {
- option=5;
- System.out.println("5 entered");
- }else {
- option=0;
- }
- if(option== 1)
- {
- System.out.println("A new student is being registered");
- newStudent(conn);
- }
- else if (option== 2)
- {
- System.out.println("Marks are being added");
- addMark(conn);
- }
- else if (option== 3)
- {
- System.out.println("A transcript is being produced");
- produceTranscript(conn);
- }
- else if (option== 4)
- {
- System.out.println("A report is being produced");
- produceReport(conn);
- }
- } while(option != 5);
- }
- public static boolean validYear(String year)
- {
- boolean valid = true;
- if(year.length() != 7) //checks length of input is 7, e.g. 2014-15
- {
- valid = false;
- }
- else
- {
- String f1 = year.substring(0, 4); //stores first 4 elements, e.g. 2014
- String f2 = year.substring(2, 4); //stores decade date of first year, e.g. 14
- String s1 = year.substring(5, 7); //stores decade date of first year, e.g. 15
- try
- {
- int if1 = Integer.parseInt(f1);
- int if2 = Integer.parseInt(f2);
- int is1 = Integer.parseInt(s1);
- if(if1 < 1900 || if1 > 2014 || is1 != if2 + 1)
- {
- valid = false;
- }
- }
- catch (NumberFormatException e)
- {
- valid = false;
- }
- }
- return valid;
- }
- public static boolean vDate(String date)
- {
- boolean valid = true;
- String day = date.substring(8, 10);
- String month = date.substring(5, 7);
- String year = date.substring(0, 4);
- int iyear = Integer.parseInt(year);
- int imonth = Integer.parseInt(month);
- int iday = Integer.parseInt(day);
- if(iday < 1 || (month.equals("02") && iday > 28) || imonth < 1 || imonth > 12 || iyear < 1900 || iyear > 2015 || ((!month.equals("09"))||(!month.equals("04"))
- ||(!month.equals("06"))||(!month.equals("11")))&& iday >30 || iday > 31)
- {
- valid = false;
- }
- return valid;
- }
- public static boolean isInteger(String i)
- {
- boolean isint = true;
- try {Integer.parseInt(i);}
- catch (NumberFormatException e) {isint = false;}
- return isint;
- }
- public static void addMark(Connection conn) throws SQLException
- {
- System.out.println("Enter the student's ID:");
- String studentID = scanny.nextLine();
- while(!isInteger(studentID))
- {
- System.out.println("Please enter a valid student ID in integer form:");
- studentID = scanny.nextLine();
- }
- Statement stmt = conn.createStatement();
- ResultSet students = stmt.executeQuery("SELECT * FROM Student WHERE studentID = " + studentID + ";");
- while(!students.next())
- {
- System.out.println("Student does not exist. Please enter a valid student ID:");
- studentID = scanny.nextLine();
- while(!isInteger(studentID))
- {
- System.out.println("Please enter a valid student ID in integer form:");
- studentID = scanny.nextLine();
- }
- students = stmt.executeQuery("SELECT * FROM Student WHERE studentID = " + studentID + ";");
- }
- System.out.println("Enter the module ID:");
- String moduleID = scanny.nextLine();
- while(!isInteger(moduleID))
- {
- System.out.println("Please enter a valid module ID in integer form:");
- moduleID = scanny.nextLine();
- }
- ResultSet modules = stmt.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
- while(!modules.next())
- {
- System.out.println("Module does not exist. Please enter a valid module ID:");
- moduleID = scanny.nextLine();
- while(!isInteger(moduleID))
- {
- System.out.println("Please enter a valid module ID in integer form:");
- moduleID = scanny.nextLine();
- }
- modules = stmt.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
- }
- System.out.println("Enter the year in the format 2010-11:");
- String year = scanny.nextLine();
- while(!validYear(year))
- {
- System.out.println("Please enter a valid year in the form 2010-11:");
- year = scanny.nextLine();
- }
- System.out.println("Enter the type ID:");
- String typeID = scanny.nextLine();
- while(!isInteger(typeID))
- {
- System.out.println("Please enter a valid type ID in integer form:");
- typeID = scanny.nextLine();
- }
- ResultSet types = stmt.executeQuery("SELECT * FROM Type WHERE typeID = " + typeID + ";");
- while(!types.next())
- {
- System.out.println("Type does not exist. Please enter a valid type ID:");
- typeID = scanny.nextLine();
- while(!isInteger(typeID))
- {
- System.out.println("Please enter a valid type ID in integer form:");
- typeID = scanny.nextLine();
- }
- types = stmt.executeQuery("SELECT * FROM Type WHERE typeID = " + typeID + ";");
- }
- System.out.println("Enter the mark:");
- String mark = scanny.nextLine();
- while(!isInteger(mark) || Integer.parseInt(mark) < 0 || Integer.parseInt(mark) > 100)
- {
- System.out.println("Please enter an integer mark between 0 and 100:");
- mark = scanny.nextLine();
- }
- System.out.println("Enter any notes you wish to make:");
- String notes = scanny.nextLine();
- if(notes.length() > 100)
- {
- System.out.println("Please do not exceed 100 characters:");
- notes = scanny.nextLine();
- }
- int n = -1;
- System.out.println("Insert into Marks VALUES ('" + year + "', " + mark + ", '" + notes + "', " + typeID + ", " + moduleID + ", " + studentID + ");");
- n = stmt.executeUpdate("Insert into Marks VALUES ('" + year + "', " + mark + ", '" + notes + "', " + typeID + ", " + moduleID + ", " + studentID + ");");
- System.out.println("Mark recorded");
- }
- public static void newStudent(Connection conn) throws SQLException
- {
- System.out.println("Enter the student's forename:");
- String forename = scanny.nextLine();
- while(forename.trim().isEmpty() || forename.length() > 20 || forename.matches(".*\\d.*"))
- {
- System.out.println("Forename cannot be null, contain integer values or exceed 20 characters. Please enter it again:");
- forename = scanny.nextLine();
- }
- System.out.println("Enter the student's family name:");
- String surname = scanny.nextLine();
- while(surname.trim().isEmpty() || surname.length() > 20 || surname.matches(".*\\d.*"))
- {
- System.out.println("Family name cannot be null, contain integer values or exceed 20 characters. Please enter it again:");
- surname = scanny.nextLine();
- }
- System.out.println("Enter the student's date of birth in the format YYYY-MM-DD");
- String dob = scanny.nextLine();
- while(!dob.matches("([0-9]{4})-([0-9]{2})-([0-9]{2})") || !vDate(dob))
- {
- System.out.println("Date of birth must be in the format YYYY-MM-DD, and must not be in the future. Please enter it again:");
- dob = scanny.nextLine();
- }
- System.out.println("Enter the student's corresponding title ID:");
- String titleID = scanny.nextLine();
- while(!isInteger(titleID))
- {
- System.out.println("Please enter a valid title ID in integer form:");
- titleID = scanny.nextLine();
- }
- PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Titles WHERE titleID = " + titleID + ";");
- ResultSet titles = conn.prepareStatement("SELECT * FROM Titles WHERE titleID = " + titleID + ";").executeQuery();
- while(!titles.next())
- {
- System.out.println("Title does not exist. Please enter a valid title ID:");
- titleID = scanny.nextLine();
- while(!isInteger(titleID))
- {
- System.out.println("Please enter a valid title ID in integer form:");
- titleID = scanny.nextLine();
- }
- titles = stmt.executeQuery("SELECT * FROM Titles WHERE titleID = " + titleID + ";");
- }
- ResultSet maxID = conn.prepareStatement("SELECT MAX(studentID) FROM Student;").executeQuery();
- int max=0;
- while(maxID.next()) {
- max = maxID.getInt("max");
- }
- stmt = conn.prepareStatement("INSERT INTO Student VALUES(" + (max+1) + ", " + titleID + ", '" + forename + "', '" + surname + "', '" + dob + "');");
- stmt.execute();
- //int n = -1;
- //int Year = Integer.parseInt(dobYear)
- //int Month = Integer.parseInt(month);
- //int Day = Integer.parseInt(day);
- //String dateofBirth = Year+"-"+Month+"-"+Day;
- /*System.out.println("Insert into Student VALUES(" + (max+1) + ", " + titleID + ", '" + forename + "', '" + surname + "', '" + dob + "');");
- stmt.execute("INSERT INTO Student VALUES(" + (max+1) + ", " + titleID + ", '" + forename + "', '" + surname + "', '" + dob + "');");
- stmt.execute("INSERT INTO StudentContact VALUES (" + (max+1) + ", 'student" + (max+1) + "@bham.student.com', 'student" + (max+1) + " street');");
- stmt.execute("INSERT INTO NextOfKinContact VALUES (" + (max+1) + ", 'nextOfKin" + (max+1) + "@bham.kin.com', 'nextOfKin" + (max+1) + " street');");
- /*String SQLcommand1 = "INSERT INTO Student ( studentID, titleID, forename, surname, dob) " +
- "VALUES ("+ (max+1)+", "+ titleID + ", '" + forename + "', '" + surname + "', '" + dob + "');";*/
- System.out.println("Student added");
- }
- public static void produceTranscript(Connection conn) throws SQLException
- {
- System.out.println("Please enter a student's ID to receive their transcript:");
- String studentID = scanny.nextLine();
- while(!isInteger(studentID))
- {
- System.out.println("Please enter a valid student ID in integer form:");
- studentID = scanny.nextLine();
- }
- Statement stmt = conn.createStatement();
- ResultSet students = stmt.executeQuery("SELECT * FROM Student WHERE studentID = " + studentID + ";");
- while(!students.next())
- {
- System.out.println("Student does not exist. Please enter a valid student ID:");
- studentID = scanny.nextLine();
- while(!isInteger(studentID))
- {
- System.out.println("Please enter a valid student ID in integer form:");
- studentID = scanny.nextLine();
- }
- students = stmt.executeQuery("SELECT * FROM Student WHERE studentID = " + studentID + ";");
- }
- int titleID = students.getInt("titleID");
- String forename = students.getString("forename").trim();
- String surname = students.getString("familyName").trim();
- String dob = students.getString("dateOfBirth");
- ResultSet titles = stmt.executeQuery("SELECT * FROM Titles WHERE titleID = " + titleID + ";");
- titles.next();
- String title = titles.getString("titleString").trim();
- Statement marksstatement = conn.createStatement();
- ResultSet marks = marksstatement.executeQuery("SELECT * FROM Marks WHERE studentID = " + studentID + ";");
- if(marks.next())
- {
- String year = marks.getString("year").trim();
- int moduleID = marks.getInt("moduleID");
- int mark = marks.getInt("mark");
- String notes = marks.getString("notes");
- int typeID = marks.getInt("typeID");
- ResultSet modules = stmt.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
- modules.next();
- String moduleName = modules.getString("moduleName").trim();
- ResultSet types = stmt.executeQuery("SELECT * FROM Type WHERE typeID = " + typeID + ";");
- types.next();
- String type = types.getString("typeString");
- System.out.println("Student ID: " + studentID);
- System.out.println(title + ". " + forename + " " + surname);
- System.out.println("Born on " + dob);
- System.out.println();
- System.out.println(year + ":");
- System.out.println(moduleName + ", " + moduleID);
- System.out.println("Mark: " + mark);
- System.out.println("Type: " + type);
- while(marks.next())
- {
- year = marks.getString("year").trim();
- moduleID = marks.getInt("moduleID");
- mark = marks.getInt("mark");
- notes = marks.getString("notes");
- typeID = marks.getInt("typeID");
- modules = stmt.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
- modules.next();
- moduleName = modules.getString("moduleName").trim();
- types = stmt.executeQuery("SELECT * FROM Type WHERE typeID = " + typeID + ";");
- types.next();
- type = types.getString("typeString");
- System.out.println();
- System.out.println(year + ":");
- System.out.println(moduleName + ", " + moduleID);
- System.out.println("Mark: " + mark);
- System.out.println("Type: " + type);
- }
- }
- else
- {
- System.out.println("Student ID: " + studentID);
- System.out.println(title + ". " + forename + " " + surname);
- System.out.println("Born on " + dob);
- System.out.println();
- System.out.println("No marks found");
- }
- }
- public static void produceReport(Connection conn) throws SQLException
- {
- System.out.println("Enter a year in the form 2010-11:");
- String year = scanny.nextLine();
- while(!validYear(year))
- {
- System.out.println("Please enter a valid year in the form 2010-11:");
- year = scanny.nextLine();
- }
- System.out.println("Enter a module ID:");
- String moduleID = scanny.nextLine();
- while(!isInteger(moduleID))
- {
- System.out.println("Please enter a valid module ID in integer form:");
- moduleID = scanny.nextLine();
- }
- Statement mark = conn.createStatement();
- ResultSet marks = mark.executeQuery("SELECT * FROM Marks WHERE year = '" + year + "' AND moduleID = " + moduleID + ";");
- if(!marks.next())
- {
- System.out.println("No marks exist from the given year and module.");
- }
- else
- {
- Statement module = conn.createStatement();
- ResultSet modules = module.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
- modules.next();
- String moduleName = modules.getString("moduleName");
- String lecturerID = modules.getString("lecturerID");
- Statement lecturer = conn.createStatement();
- ResultSet lecturers = lecturer.executeQuery("SELECT * FROM Lecturer WHERE lecturerID = " + lecturerID + ";");
- lecturers.next();
- String titleID = lecturers.getString("titleID");
- String foreName = lecturers.getString("foreName");
- String familyName = lecturers.getString("familyName");
- Statement noOfStudent = conn.createStatement();
- ResultSet noOfStudents = noOfStudent.executeQuery("SELECT COUNT (DISTINCT studentID) FROM Marks WHERE year ='" + year + "' AND moduleID = " + moduleID + ";");
- noOfStudents.next();
- int number = Integer.parseInt(noOfStudents.getString("count"));
- Statement marksum = conn.createStatement();
- ResultSet marksums = marksum.executeQuery("SELECT SUM(mark) FROM Marks WHERE year = '" + year + "' AND moduleID = " + moduleID + ";");
- marksums.next();
- String sum = marksums.getString("sum");
- int average = Integer.parseInt(sum) / number;
- Statement fail = conn.createStatement();
- ResultSet fails = fail.executeQuery("SELECT COUNT(mark) FROM Marks WHERE year = '" + year + "' AND moduleID = " + moduleID + " AND mark < 40;");
- fails.next();
- int failures = Integer.parseInt(fails.getString("count"));
- float percentage = (((float)failures / (float)number) * 100);
- Statement title = conn.createStatement();
- ResultSet titles = title.executeQuery("SELECT * FROM Titles WHERE titleID = " + titleID + ";");
- titles.next();
- String lectTitle = titles.getString("titleString");
- System.out.println();
- System.out.println("Module ID: " + moduleID);
- System.out.println("Module name: " + moduleName);
- System.out.println("Lecturer: " + lectTitle.trim() + ". " + foreName.trim() + " " + familyName.trim());
- System.out.println("Number of students with marks recorded: " + number);
- System.out.println("Average mark: " + average);
- System.out.println("Number of failures: " + failures);
- System.out.println("Percentage of failures: " + percentage + "%");
- System.out.println();
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement