Chapter 32 Exercise 10, Introduction to Java Programming, Tenth Edition Y. Daniel LiangY.

32.10 (Record unsubmitted exercises) The following three tables store information on
students, assigned exercises, and exercise submission in LiveLab. LiveLab is an
automatic grading system for grading programming exercises.
create table AGSStudent (
username varchar(50) not null,
password varchar(50) not null,
fullname varchar(200) not null,
instructorEmail varchar(100) not null,
constraint pkAGSStudent primary key (username)
);
create table ExerciseAssigned (
instructorEmail varchar(100),
exerciseName varchar(100),
maxscore double default 10,
constraint pkCustomExercise primary key
(instructorEmail, exerciseName)
);
create table AGSLog (
username varchar(50), /* This is the student's user name */
exerciseName varchar(100), /* This is the exercise */
score double default null,
submitted bit default 0,
constraint pkLog primary key (username, exerciseName)
);
The AGSStudent table stores the student information. The ExerciseAssigned
table assigns the exercises by an instructor. The AGSLog table stores the grading
results. When a student submits an exercise, a record is stored in the AGSLog table.
However, there is no record in AGSLog if a student did not submit the exercise.
Write a program that adds a new record for each student and an assigned exercise
to the student in the AGSLog table if a student has not submitted the exercise.
The record should have 0 on score and submitted . For example, if the tables
contain the following data in AGSLog before you run this program, the AGSLog
table now contains the new records after the program runs.

import java.io.IOException;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class Exercise10 {

 public static void main(String[] args) throws MalformedURLException, IOException, ClassNotFoundException, SQLException {
  Class.forName("com.mysql.jdbc.Driver");
  Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook", "root", "root");
  Statement statement = connection.createStatement();
  
  ArrayList<AGSLog> logs = new ArrayList<>();
  ResultSet  rset = statement.executeQuery("select * from AGSLog");
  while(rset.next()) {
   logs.add(new AGSLog(rset.getString(1), rset.getString(2)));
  }
  
  rset = statement.executeQuery("select AGSStudent.username, ExerciseAssigned.exerciseName  from AGSStudent, ExerciseAssigned where AGSStudent.instructorEmail = ExerciseAssigned.instructorEmail;");
  while(rset.next()) {
   AGSLog agsLog = new AGSLog(rset.getString(1), rset.getString(2));
   if(!logs.contains(agsLog)) {
    String queryString = "insert into AGSLog (username, exerciseName) values (?, ?);";
    PreparedStatement preparedStatement = connection.prepareStatement(queryString);
    preparedStatement.setString(1, agsLog.username);
    preparedStatement.setString(2, agsLog.exerciseName);
    preparedStatement.executeUpdate(); 
   } 
  }  
 }
 
 static class AGSLog {
  String username;
  String exerciseName;
  
  public AGSLog(String username, String exerciseName) {
   this.username = username;
   this.exerciseName = exerciseName;
  }
  
  @Override
  public boolean equals(Object obj) {
   if (obj instanceof AGSLog) {
    AGSLog new_name = (AGSLog) obj;
    if((username.equals(new_name.username)) && (exerciseName.equals(new_name.exerciseName))) {
     return true;
    } else {
     return false; 
    }
   } else {
    return false;
   }
  }
 }
}