I am creating a social network in Java for my final paper and i need to list all the mutual followers of a user in a table through the listMutualFollowers() function.

I tried this:

public ArrayList listMutualFollowers(int id_user) {
        try {
            ArrayList data = new ArrayList();

            PreparedStatement ps = connection.prepareStatement("SELECT id_follower FROM followers WHERE id_user = "   id_user);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {

                PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM followers WHERE id_user = "   rs.getInt("id_follower"));
                ResultSet rs2 = ps2.executeQuery();

                while (rs2.next()) {
                    data.add(new Object[]{
                        getFollowerName(rs2.getInt("id_follower"))
                    });
                }
                ps2.close();
                rs2.close();
            }
            ps.close();
            rs.close();
            connection.close();

            return data;
        } catch (SQLException e) {
            e.getMessage();
            JOptionPane.showMessageDialog(null, "listMutualFollowers():"   e.getMessage());
            return null;
        }
    }

I was expecting this function to return the name of a user's mutual followers, but it returned a list with the user's own name on every line of the ArrayList. (Yes, I noticed my big logic error in the second PreparedStatement)

CodePudding user response：

I found a solution and I'm commenting in case anyone needs it in the future.

Here is the solution:

public ArrayList listMutualFollowers(int id_user) {
    try {
        ArrayList data = new ArrayList();

        PreparedStatement ps = connection.prepareStatement("SELECT id_follower
                                                            FROM followers a
                                                            WHERE id_follower IN (
                                                            SELECT id_follower
                                                            FROM followers b
                                                            WHERE a.id_user = b.id_user
                                                            ) and id_user = "   id_user);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {

            data.add(new Object[]{
                getFollowerName(rs.getInt("id_follower"))
            });
        }
        ps.close();
        rs.close();
        connection.close();

        return data;
    } catch (SQLException e) {
        e.getMessage();
        JOptionPane.showMessageDialog(null, "listMutualFollowers():"   e.getMessage());
        return null;
    }
}

names of variables and functions are a bit strange because I translated my own code from Portuguese to English to post here xd

CodePudding user response：

You'd probably be better off using a map.

public Map<Integer, List<Integer>> getFollowersForUser(int id_user) {
    Map<Integer, List<Integer>> followerMap = new HashMap<>();
    // query the followers for the given user ID
    PreparedStatement ps = connection.prepareStatement("SELECT id_follower FROM followers WHERE id_user = "   id_user);  // You can modify this query to suit your needs
    ResultSet rs = ps.executeQuery();
    List<Integer> followers = null;
    while (rs.next()) {
        int follower_id = rs.getInt("id_follower");
        followers.add(follower_id);
   }
    ps.close();
    rs.close();
    followerMap.put(id_user, followers);
    return followerMap;
}

Then, all you need to do is reuse this method to get the followers for each of the followers and merge the resulting map with the original.

Map<Integer, List<Integer>> original = getFollowersForUser(100); // result of original query

Iterator<Map.Entry<Integer, List<Integer>>> iterator = original.entrySet().iterator();
while (iterator.hasNext()) {
    Map.Entry<Integer, List<Integer>> entry = iterator.next();

    Map<Integer, List<Integer>> followerMap = null;
    for (Integer id : entry.getValue()) {
        followerMap = getFollowersForUser(id); // call for each follower
        original.putAll(followerMap); // merge the maps
    }
    System.out.println(original);
}

Assume the following:

1. User 100 has these followers: 101, 102, 103
2. User 101 has these followers: 201, 202, 100
3. User 102 has these followers: 301, 302, 303
4. User 103 has these followers: 401, 100, 403

Then, the resulting map will be as follows:

{100=[101, 102, 103], 101=[201, 202, 100], 102=[301, 302, 303], 103=[401, 100, 403]}

If you need to find which user follow back the original user (user ID: 100), you can do something like this:

public static List<Integer> getMutualFollowers(Map<Integer, List<Integer>> map, Integer value) {
        
    List<Integer> mutualFollowers = new ArrayList<>();
    Iterator<Map.Entry<Integer, List<Integer>>> iterator = map.entrySet().iterator();
    while (iterator.hasNext()) {
        Entry<Integer, List<Integer>> entry = iterator.next();
        List<Integer> followers = entry.getValue();
        if (followers.contains(value)) {
            int key = entry.getKey();
            if(map.get(value).contains(key)) {
                mutualFollowers.add(key);                   
            }
        }
    }
    return mutualFollowers;
}

Basically, look for the key that contain a given value, and then use the value as a key and find out if that entry contains a matching value. For example, if user 100 follows user 102, find out if user 102 follows user 100. If so, add it to the list.