public String deleteFriendship(Profile friend1, Profile friend2) throws SQLException { String r = "Friendship deleted!"; try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); stmt.execute( "DELETE FROM Friends WHERE " + "(Friend1 IN (SELECT ProfileID FROM Profiles " + "WHERE Login='******') AND " + "Friend2 IN (SELECT ProfileID FROM Profiles " + "WHERE Login='******')) " + "OR (Friend2 IN (SELECT ProfileID FROM Profiles " + "WHERE Login='******') AND " + "Friend1 IN (SELECT ProfileID FROM Profiles " + "WHERE Login='******'))"); stmt.close(); // System.out.println("Requête executée"); } catch (SQLException e) { // System.err.println(e.toString()); r = e.toString(); throw e; } return r; }
public ArrayList<String> getContactList(Profile profile) { ArrayList<String> contacts = new ArrayList<String>(); try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); ResultSet result = stmt.executeQuery( "SELECT login FROM Profiles WHERE ProfileID IN " + "(SELECT DISTINCT Friend2 FROM Friends WHERE Friend1 IN " + "(SELECT ProfileID FROM Profiles " + "WHERE (Login='******' AND isAccepted=TRUE)) UNION " + "(SELECT DISTINCT Friend1 FROM Friends " + "WHERE Friend2 IN (SELECT ProfileID FROM Profiles " + "WHERE (Login='******' AND isAccepted=TRUE))))"); while (result.next()) { // if result of the query is not empty contacts.add(result.getString(1)); } stmt.close(); } catch (SQLException e) { System.err.println(e.toString()); } return contacts; }
public String insertProfile(Profile profile) throws SQLException { String r = "New profile inserted!"; if (this.getProfileID(profile) == -1) { try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); // String login = (firstName, lastName, password)).createLogin(); if (profile.getEmail() != null) { stmt.execute( "INSERT INTO Profiles (FirstName, LastName, Login, Password, Email, IsAdmin) " + "VALUES ('" + profile.getFirstName() + "', '" + profile.getLastName() + "', " + "'" + profile.getLogin() + "', '" + profile.getPassword() + "', " + "'" + profile.getEmail() + "', " + profile.isAdmin() + ")"); } else { stmt.execute( "INSERT INTO Profiles (FirstName, LastName, Login, Password, IsAdmin) " + "VALUES ('" + profile.getFirstName() + "', '" + profile.getLastName() + "', " + "'" + profile.getLogin() + "', '" + profile.getPassword() + "', " + profile.isAdmin() + ") "); } stmt.close(); // System.out.println("Requête executée"); } catch (SQLException e) { // System.err.println(e.toString()); r = e.toString(); throw e; } } return r; }
public void incrementNbConnections(Profile profile) { try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); stmt.execute( "UPDATE Profiles SET NbConnections=(SELECT NbConnections FROM Profiles " + "WHERE Login='******')+1 WHERE Login='******'" + ""); stmt.close(); // System.out.println("Requête executée"); } catch (SQLException e) { } }
public void setAdmin(Profile profile, boolean isAdmin) { try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); stmt.execute( "UPDATE Profiles SET isAdmin = " + isAdmin + " WHERE Login='******'"); stmt.close(); } catch (SQLException e) { System.err.println(e.toString()); } }
public String insertPendingFriendship(Profile friend1, Profile friend2) throws SQLException { String r = "New friendship inserted!"; try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); stmt.execute( "INSERT INTO Friends (Friend1,Friend2) VALUES " + "((select ProfileID from Profiles where Login='******') , (select ProfileID from Profiles where Login='******'))"); stmt.close(); // System.out.println("Requête executée"); } catch (SQLException e) { // System.err.println(e.toString()); r = e.toString(); throw e; } return r; }
public void setViewedConversation(Profile profile1, Profile profile2) { int profile1ID = this.getProfileID(profile1); int profile2ID = this.getProfileID(profile2); if (profile1ID != -1 && profile2ID != -1) { // Check if profile1 and profile2 exist try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); stmt.execute( "UPDATE Messages SET IsDelivered = true " + " WHERE MessageID IN " + "((SELECT Messages.MessageID " + "FROM Messages, Profiles " + "WHERE (Profiles.ProfileID = Messages.Sender " + "AND Profiles.Login ='******'" + "AND Messages.IsDelivered = false" + ") " + "AND (Receiver IN (SELECT ProfileID FROM Profiles " + "WHERE Login = '******'))) " + "UNION (SELECT Messages.MessageID " + "FROM Messages, Profiles " + "WHERE (Profiles.ProfileID = Messages.Sender " + "AND Profiles.Login ='******'" + "AND Messages.IsDelivered = false " + ") " + "AND (Receiver IN (SELECT ProfileID FROM Profiles " + "WHERE Login = '******'))) " + "ORDER BY MESSAGEID)"); stmt.close(); } catch (SQLException e) { System.err.println(e.toString()); } } }
public String setEmail(Profile profile, String email) throws SQLException { String r = "Email address modified!"; try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); stmt.execute( "UPDATE Profiles SET Email='" + email + "' WHERE Login='******'"); stmt.close(); // System.out.println("Requête executée"); } catch (SQLException e) { // System.err.println(e.toString()); r = e.toString(); throw e; } return r; }
public String deleteProfile(Profile profile) throws SQLException { String r = "Profile deleted!"; int profileID = this.getProfileID(profile); try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); stmt.execute("DELETE FROM Friends WHERE Friend1=" + profileID + " OR Friend2=" + profileID); stmt.execute("DELETE FROM Files WHERE Sender=" + profileID + " OR Receiver=" + profileID); stmt.execute("DELETE FROM Messages WHERE Sender=" + profileID + " OR Receiver=" + profileID); stmt.execute("DELETE FROM Profiles WHERE Login='******'"); stmt.close(); // System.out.println("Requête executée"); } catch (SQLException e) { // System.err.println(e.toString()); r = e.toString(); throw e; } return r; }
public String setStatus(Profile profile, Profile.Status status) throws SQLException { String r = "Status modified!"; try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); stmt.execute( "UPDATE Profiles SET Status=" + status.getValue() + " WHERE Login='******'"); stmt.close(); // System.out.println("Requête executée"); } catch (SQLException e) { // System.err.println(e.toString()); r = e.toString(); throw e; } return r; }
public int getProfileID(Profile profile) { int res = -1; // -1 indique que le profile n'existe pas try { stmt = conn.createStatement(); ResultSet result = stmt.executeQuery( "SELECT PROFILEID FROM Profiles WHERE " + "Login='******'"); if (result.next()) { // if result of the query is not empty // System.out.println("This profile exist !"); // result.first(); //Take the first result because both profiles haven't the same login res = Integer.parseInt(result.getString(1)); // The first row is PROFILEID // System.out.println("ID of " + profile.getLogin() + " : " + res); } else { // System.out.println("WARNING : This profile doesn't exist ! -> return -1 value"); } stmt.close(); } catch (SQLException e) { System.err.println(e.toString()); } return res; }
public ArrayList<File> getNotDownloadedFiles(Profile profile) { ArrayList<File> files = new ArrayList<File>(); try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); ResultSet result = stmt.executeQuery( "SELECT Filename, Sender, Receiver FROM Files WHERE IsDelivered = false AND Receiver IN (SELECT profileID FROM Profiles WHERE Login='******')"); while (result.next()) { // if result of the query is not empty files.add( new File( result.getString(1), this.getProfile(result.getInt(2)), this.getProfile(result.getInt(3)))); } stmt.close(); } catch (SQLException e) { System.err.println(e.toString()); } return files; }
public boolean isAdmin(Profile profile) { boolean res = false; try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); ResultSet result = stmt.executeQuery( "SELECT * FROM Profiles WHERE " + "Login='******' AND IsAdmin=" + true); if (result.next()) { // if result of the query is not empty res = true; } stmt.close(); } catch (SQLException e) { System.err.println(e.toString()); } return res; }
public ArrayList<Message> getUndeliveredConversation(Profile profile1, Profile profile2) { ArrayList<Message> conversation = new ArrayList<Message>(); int profile1ID = this.getProfileID(profile1); int profile2ID = this.getProfileID(profile2); Profile sender = null; Profile receiver = null; if (profile1ID != -1 && profile2ID != -1) { // Check if profile1 and profile2 exist try { // creates a SQL Statement object in order to execute the SQL insert command stmt = conn.createStatement(); ResultSet result = stmt.executeQuery( "(SELECT Profiles.Firstname, Profiles.Lastname, Profiles.Login, " + "Profiles.password, " + "Messages.Receiver, Messages.Text, " + "Messages.DateMsg,Messages.TimeMsg, Messages.MessageID " + "FROM Messages, Profiles " + "WHERE (Profiles.ProfileID = Messages.Sender " + "AND Profiles.Login ='******'" + "AND Messages.IsDelivered = false) " + "AND (Receiver IN (SELECT ProfileID FROM Profiles " + "WHERE Login = '******'))) " + "UNION (SELECT Profiles.Firstname, " + "Profiles.Lastname, Profiles.Login, Profiles.password, " + "Messages.Receiver, Messages.Text, " + "Messages.DateMsg,Messages.TimeMsg, Messages.MessageID " + "FROM Messages, Profiles " + "WHERE (Profiles.ProfileID = Messages.Sender " + "AND Profiles.Login ='******'" + "AND Messages.IsDelivered = false " + ") " + "AND (Receiver IN (SELECT ProfileID FROM Profiles " + "WHERE Login = '******'))) " + "ORDER BY DATEMSG, TIMEMSG, MESSAGEID"); while (result.next()) { // if result of the query is not empty if (result.getInt(5) == profile1ID) { receiver = profile1; sender = profile2; } else if (result.getInt(5) == profile2ID) { receiver = profile2; sender = profile1; } else { return null; } conversation.add( new Message( sender, receiver, result.getString(6), result.getDate(7), result.getTime(8), true)); } stmt.close(); } catch (SQLException e) { System.err.println(e.toString()); } } return conversation; /* */ }