Solved SQL prepared statements issue

Hi, I'd just like to point out, I'm a noob.

I'm attempting to create a method to add tokens onto a users curent token account.

Code so far:

http://pastebin.com/Ue7x59QS

This kinda does what I need, however, it SETS the users tokens, rather than updating them. So if I 'add a token', let's say 4 then add 8 - I'll just have 8 tokens, not the 12 I should have. Is this because I'm not getting the users tokens before hand then adding on to that value? If you could give an example that would be great as I'm a noob. :L

 

Yes, it is. Line 14 is pretty clear, you tell the database to update the token of the record in question with the new, initially given token. What you want to to is incrementing it with the new token:

Code:SQL

UPDATE tokens SET tokens = tokens + ? WHERE USER = ?;

However, you should be able to simplify the whole operation by using INSERT ... ON DUPLICATE KEY. If you have a proper unique key (I assume that would be the user column) the database will insert the given data if the key does not yet exist. If it exists it will execute the operation given after the 'ON DUPLICATE KEY'. It should be as simple as:

Code:SQL

INSERT INTO my_table (USER, tokens) VALUES (?, ?) ON DUPLICATE KEY UPDATE tokens = tokens + VALUES(2);

(Not really sure if 'VALUES (2)' works, if not you would naturally set the token two times.)

 

Thanks for the reply, I found a fix myself:

Code:java

                    update = connection.prepareStatement("UPDATE tokens SET tokens = ? WHERE user = ?");
                    int token = getTokens(username);
                    update.setInt(1, token + tokens);
                    update.setString(2, username);

Would you suggest your method over mine?


the getTokens method looks like this:

Code:java

    public int getTokens(String username) {
        if(!checkConnected()) return -2;
 
        PreparedStatement query = null;
        ResultSet rs = null;
 
        try {
            query = connection.prepareStatement("SELECT tokens FROM tokens WHERE user = ?");
            query.setString(1, username);
            rs = query.executeQuery();
            if(rs.next()) {
                return rs.getInt("tokens");
            } else {
                return -1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            printErrors(e);
        } finally {
            try {
                if(query != null) query.close();
                if(rs != null) rs.close();
            } catch (SQLException e) {}
        }
        return -3;
    }
}

 

Absolutely. It is a lot simpler to read and less messy to begin with, but the main difference is that your solution needs to do things within Java that can easily be done by the database itself.

The problem lies in the nature of I/O situations: each database call is slow. If you are lucky it is fast enough so that it has no visible effect on the server's side, in bad situations it may hold the server. Therefore database calls should be reduced to the absolute minimum (plus be executed in a separate thread, but that is a different story) - which means let the database handle everything that is possible.

Looking at your code you use three database calls (first selecting the COUNT(*), then getting the current tokens and last updating the tokens) when you could do everything with one call.

 

Ah - okay. For now, I'll just make it work and fix up the efficiency later. -Great help, thanks!