public ArrayList<Account> getAccountList() { Connection connect = null; try { ArrayList<Account> accounts = new ArrayList<>(); connect = DBConnection.getConnection(); String sql = "SELECT * FROM account" + "ORDER BY name desc"; ResultSet result = Utility.queryOperation(connect, sql); Account temp = new Account(); while (result.next()) { temp.setName(result.getString("name")); temp.setEmail(result.getString("email")); temp.setSecondaryEmail(result.getString("secondaryEmail")); temp.setTypeAccount(result.getString("typeAccount")); temp.setPassword(result.getString("password")); temp.setAdmin(result.getBoolean("isAdministrator")); accounts.add(temp); } return accounts; } catch (SQLException ex) { Logger.getLogger(AccountManager.class.getName()).log(Level.SEVERE, null, ex); } finally { DBConnection.releaseConnection(connect); } return null; }
public ArrayList<Account> searchUser(String search, String type) throws SQLException { Connection connect = null; ArrayList<Account> accounts; String sql = "SELECT * from account WHERE " + "name LIKE '%" + search + "%'" + "AND typeAccount = '" + type + "'"; String sql2 = "SELECT * from account WHERE " + "typeAccount ='" + type + "'"; try { connect = DBConnection.getConnection(); accounts = new ArrayList<>(); if (search.isEmpty()) { ResultSet result = Utility.queryOperation(connect, sql2); Account temp = new Account(); while (result.next()) { temp.setName(result.getString("name")); temp.setEmail(result.getString("email")); temp.setSecondaryEmail(result.getString("secondaryEmail")); temp.setTypeAccount(result.getString("typeAccount")); temp.setPassword(result.getString("password")); temp.setAdmin(result.getBoolean("isAdministrator")); accounts.add(temp); } } else { ResultSet result = Utility.queryOperation(connect, sql); Account temp = new Account(); while (result.next()) { temp.setName(result.getString("name")); temp.setEmail(result.getString("email")); temp.setSecondaryEmail(result.getString("secondaryEmail")); temp.setTypeAccount(result.getString("typeAccount")); temp.setPassword(result.getString("password")); temp.setAdmin(result.getBoolean("isAdministrator")); accounts.add(temp); } } } finally { DBConnection.releaseConnection(connect); } return accounts; }
public void changeType(Account pAccount, String newType) throws SQLException, ConnectionException, NullAccountException, EmailException { String demotionSql = "DELETE FROM " // cancella vecchie info + pAccount.getTypeAccount() + "WHERE fkAccount = '" + testEmail(pAccount.getSecondaryEmail()) + "'"; String toProfessorSql = "INSERT INTO professor " // se nuovo professor + "(fkAccount,link,department)" + "VALUES ('" + testEmail(pAccount.getSecondaryEmail()) + "'," + "'" + "null" + "'," + "'" + "null" + "'"; String toPhdSql = "INSERT INTO phdstudent " + "(fkAccount,telephone,link,deparment,researchInterest,fkCycle" + "fkCurriculum, fkProfessor )" // nuovo dottorando + "VALUES ('" + testEmail(pAccount.getSecondaryEmail()) + "'," + "'" + "null" + "'," + "'" + "null" + "'," + "'" + "null" + "'," + "'" + "null" + "'," + "'" + "null" + "'," + "'" + "null" + "'," + "'" + "null" + "'"; String changeTypeSql = "UPDATE account" // aggiorna il tipo + "set typeAccount = '" + newType + "' WHERE email = '" + pAccount.getEmail(); Connection connect = null; try { connect = DBConnection.getConnection(); pAccount = testAccount(pAccount); if (newType.equals("phdstudent") && pAccount.getTypeAccount().equals("basic")) { Utility.executeOperation(connect, toPhdSql); // diventa un dottorando Utility.executeOperation(connect, changeTypeSql); // cambia tipo in account } else if (newType.equals("phdstudent") && pAccount.getTypeAccount().equals("professor")) { Utility.executeOperation(connect, demotionSql); // perde info phd Utility.executeOperation(connect, toPhdSql); // nuove info prof Utility.executeOperation(connect, changeTypeSql); } else if (newType.equals("professor") && pAccount.getTypeAccount().equals("basic")) { Utility.executeOperation(connect, toProfessorSql); Utility.executeOperation(connect, changeTypeSql); } else if (newType.equals("professor") && pAccount.getTypeAccount().equals("phdstudent")) { Utility.executeOperation(connect, demotionSql); Utility.executeOperation(connect, toProfessorSql); Utility.executeOperation(connect, changeTypeSql); } else if (newType.equals("basic")) { Utility.executeOperation(connect, demotionSql); Utility.executeOperation(connect, changeTypeSql); } } finally { DBConnection.releaseConnection(connect); } }