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); } }