Our professor gave us example code that we would just have to substitute(yes, he said we can copy code) since the class doesn't know SQL, and the official DB course is next semester.The gui and save to file has been finished except for the db part.
I have a List<person>, that has objects of types Student and Faculty (both extend Person). There is a method save() in my Database class that inserts data into SQL. My struggle is figuring out how to add both Faculty and Student to the database, would it require 2 different databases?
The person (faculty or student) is added from another class by invoking a method in this Database class.
public void save() throws SQLException {
String checkSql = "select count(*) as count from people where id=?";
PreparedStatement checkStmt = con.prepareStatement(checkSql);
String insertSql = "insert into faculty (id, name, address, phone, "
+ "employment, credits, full_time, fac_category, room, pay) values (?, ?, ?, ?, ?, ?, ?)";
String updateSql = "update faculty set name=?, address=?, phone=?,"
+ " employment=?, credits=?, full+time=?";
PreparedStatement updateStatement = con.prepareStatement(updateSql);
for(Person person: people) {
int id = person.getId();
String name = person.getName();
String address = person.getAddress();
String phone = person.getPhone();
String emp =
person.EmploymentType().toString();
int credits = person.getCredits();
boolean fullTime = person.isFullTime();
checkStmt.setInt(1, id);
ResultSet checkResult = checkStmt.executeQuery();
checkResult.next();
int count = checkResult.getInt(1);
if(count == 0) {
System.out.println("Inserting person with ID " + id);
int col = 1;
insertStatement.setInt(col++, id);
insertStatement.setString(col++, name);
insertStatement.setString(col++, emp);
insertStatement.setString(col++, address);
insertStatement.setString(col++, phone);
insertStatement.setString(col++, emp);
insertStatement.setInt(col++, credits);
insertStatement.executeUpdate();
} else {
System.out.println("Updating person with ID " + id);
int col = 1;
updateStatement.setInt(col++, id);
updateStatement.setString(col++, name);
updateStatement.setString(col++, emp);
updateStatement.setString(col++, address);
updateStatement.setString(col++, phone);
updateStatement.setString(col++, emp);
updateStatement.setInt(col++, credits);
updateStatement.executeUpdate();
updateStatement.close();
insertStatement.close();
checkStmt.close();
}
}
}