Java and MySQL PreparedStatement: storing 5000 numbers in a table. what's the right way ?

Question

Hello.

I'm Writing a facebook application using the Java-facebook-api and MySQL 5.1.

I'm trying to find a faster way to store the friends list of a user who logs into my application.

When a user logs into my application the following happens:

1. deleting the old friends list of the logged-in user
2. gathering the user friends list into an ArrayList
3. In Java I'm doing a for loop for each number in the Array List and I Execute an insert to insert the new friend id to the friends table.

Is there a way to make this all scenario faster?

For example I thought about creating a stored procedure that receives the uid of the current user and an array of uids of friends, but i read and understood that stored procedures in MySQL 5.1 cannot receive arrays.

The friends list table is very simple:

id int(10) <- primary key
user_friends_uid bigint
timestamp TIMESTAMP

any help would be greatly appreciated.

thanks!

Answer 1

You can use JDBC batch updates:

    PreparedStatement stmt = getSql().prepareStatement(...);
    try {
        for (Row row: rows) {
            stmt.setLong(1, row.id);
            ...
            stmt.addBatch();
        }
        stmt.executeBatch();
    } finally {
        stmt.close();
    }