private void UpdateStudentProfile(String studentNo) throws Exception {
try (IDatabaseConnection connection = DatabaseConnectionService.createDatabaseConnection()) {
// Address table needs to be updated before updating the student table due to foreign key
// constraint
UpdateStudentAddress(connection);
try (IStatementExecutor stmtExecutor =
DatabaseConnectionService.createStatementExecutor(connection)) {
String updateStudentProfileInfo =
String.format(
"UPDATE Students "
+ "SET homePhone = '%s',"
+ "workPhone = '%s',"
+ "sex = '%s',"
+ "nationality = '%s',"
+ "street = '%s',"
+ "city = '%s',"
+ "pincode = '%s'"
+ "WHERE studentNo = '%s'",
homePhoneTextBox.getText(),
workPhoneTextBox.getText(),
sexTextBox.getText(),
nationalityTextBox.getText(),
streetTextBox.getText(),
cityTextBox.getText(),
pincodeTextBox.getText(),
studentNo);
stmtExecutor.executeUpdate(updateStudentProfileInfo);
}
}
}
private void PopulateStudentProfilePage(String studentNo) throws Exception {
try (IDatabaseConnection connection = DatabaseConnectionService.createDatabaseConnection()) {
try (IStatementExecutor stmtExecutor =
DatabaseConnectionService.createStatementExecutor(connection)) {
String student = String.format("SELECT * FROM Students WHERE studentNo ='%s' ", studentNo);
try (IQueryResultSet resultSet = stmtExecutor.executeQuery(student)) {
resultSet.moveToFirstRow();
SetRowValuesToTextboxControls(resultSet);
}
}
}
}
private void UpdateStudentAddress(IDatabaseConnection connection) throws Exception {
try (IStatementExecutor stmtExecutor =
DatabaseConnectionService.createStatementExecutor(connection)) {
String updateStudentAddressInfo =
String.format(
"INSERT INTO Addresses (street,city,pincode)"
+ " SELECT '%s','%s','%s' FROM DUAL"
+ " WHERE NOT EXISTS"
+ "(SELECT * FROM Addresses WHERE street='%s' AND city='%s' AND pincode='%s') ",
streetTextBox.getText(),
cityTextBox.getText(),
pincodeTextBox.getText(),
streetTextBox.getText(),
cityTextBox.getText(),
pincodeTextBox.getText());
stmtExecutor.executeUpdate(updateStudentAddressInfo);
}
}