Solved SQL prepared statements issue

Discussion in 'Plugin Development' started by thomasb454, Mar 29, 2014.

Thread Status:
Not open for further replies.
  1. Offline


    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:

    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
  2. Offline


    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:
    1. 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:
    1. 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.)
    thomasb454 likes this.
  3. Offline


    Thanks for the reply, I found a fix myself:

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

    Would you suggest your method over mine?

    the getTokens method looks like this:

    1. public int getTokens(String username) {
    2. if(!checkConnected()) return -2;
    4. PreparedStatement query = null;
    5. ResultSet rs = null;
    7. try {
    8. query = connection.prepareStatement("SELECT tokens FROM tokens WHERE user = ?");
    9. query.setString(1, username);
    10. rs = query.executeQuery();
    11. if( {
    12. return rs.getInt("tokens");
    13. } else {
    14. return -1;
    15. }
    16. } catch (SQLException e) {
    17. e.printStackTrace();
    18. printErrors(e);
    19. } finally {
    20. try {
    21. if(query != null) query.close();
    22. if(rs != null) rs.close();
    23. } catch (SQLException e) {}
    24. }
    25. return -3;
    26. }
    27. }
  4. Offline


    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.
    thomasb454 likes this.
  5. Offline


    Ah - okay. For now, I'll just make it work and fix up the efficiency later. -Great help, thanks!
Thread Status:
Not open for further replies.

Share This Page