[Jay0830]

Background:
that say I have a website,a use can login from facebook account.
when a user login from facebook account first time, my website will get and store from facebook into my database.
and check if your friend in facebook also register my website

Question:
if your friend in facebook also in my website, then insert friendship into database

If the user has 100 friends, then I need to check my database 100 times and insert 100 times?

        for (List<User> myFeedConnectionPage : myFriends) {
            for (User friend : myFeedConnectionPage) {
                sql = "SELECT * FROM user WHERE fbID='" + friend.getId() + "'";
                
                ResultSet rs = select(sql);
                if (rs.next()) {
                    //save friend to friendship
                    friendList = friendList + "," + rs.getString("ID");
                }
            }
        }

I am wondering if there has a better way to write it.

Thanks

[TheITNinja]

Generally you should avoid "select *" due to performance issuses.

You will need to write dynamic SQL thuss:

SELECT * FROM user WHERE fbID IN (id1,id2...idn)

You will need to dynamically create the last part (id1 to idn). Basically each id is seperated via a comma.


A better way would be to use a sub query:

SELECT * FROM user WHERE fbID IN 

(
   select FriendId from X where UserId = @UserId
)

I don't know the schema so I've just used X, but you get the general Idea

[Jay0830]

Thanks TheITNinja,
I didn't know IN statement. this is really useful.

in second method that you mentioned sub query.
FriendID is another table, right?

So If I have only one tale "user" then I can't use sub query?

[TheITNinja]

Jay0830, on 15 April 2012 - 03:27 AM, said:

Thanks TheITNinja,
I didn't know IN statement. this is really useful.

in second method that you mentioned sub query.
FriendID is another table, right?

So If I have only one tale "user" then I can't use sub query?

Well the assumption is that in the sub-query, we are trying to get all the "FriendId(s)" of the given user. Again I don't know your database schema. However I am assuming that there has to be a table that holds all friends a person has.