Why does this MySQL not execute correctly?

Question

I'm trying to update tables when an event happens in a Minecraft Spigot (Bukkit) server. For some reason, I just can't figure out why this won't work. No matter what, the fields just won't change at all.

Here's the code doing the work:

public void processKill(final String killerName, final int killerKS, final String victimName) {
        try {
            Thread thread = new Thread() {
                @Override
                public void run() {
                    try (Connection c = du.makeNewConnection()) {
                       if (du.testWorkingConnection(c)) {
                           int best_killstreak = 0;
                           /* Process killstreak */
                           PreparedStatement killerKSSQL = c.prepareCall("SELECT best_killstreak FROM `player_data` WHERE name=?");
                           killerKSSQL.setString(1, killerName);
                           ResultSet killerKSRS = killerKSSQL.executeQuery();
                           best_killstreak = (killerKSRS.next()) ? killerKSRS.getInt(1) : 0;
                           /* Killer stuff */
                           PreparedStatement killerSQL = c.prepareStatement("UPDATE `player_data` SET kills=kills+1,deaths=deaths,points=points+?,best_killstreak=? WHERE name=?;");
                           killerSQL.setInt(1, config.getInt("points-per-kill"));
                           killerSQL.setInt(2, (best_killstreak < killerKS) ? killerKS : best_killstreak);
                           killerSQL.setString(3, killerName);
                           killerSQL.execute();
                           /* Victim stuff */
                           PreparedStatement victimSQL = c.prepareStatement("UPDATE `player_data` SET kills=kills,deaths=deaths+1,points=points-?,best_killstreak=best_killstreak WHERE name=?;");
                           victimSQL.setInt(1, config.getInt("minus-points-per-death"));
                           victimSQL.setString(2, victimName);
                           victimSQL.execute();
                       }
                    } catch (SQLException e) {
                        e.printStackTrace(System.err);
                        return;
                    }
                }
            };
            thread.start();
            thread.join();
        } catch (InterruptedException e) {
            e.printStackTrace(System.err);
        }
    }

Obviously I'm doing something terribly wrong, but I just don't know what. Usually I can figure out how to do things like this in web development, but this has me tearing my hair out.

If you need any extra information, I'd be happy to oblige, just ask. For now, can anyone see any drastic mistakes regarding why this won't work?

Thanks.

Are you sure your query is running at all? For example, du.testWorkingConnection(c) is returning true? — Adrian Pang, Mar 21 '14 at 00:08
@AdrianPang Yes, that returns fine because the initial row creation query uses that as well (different method) and it passes OK. TonyZhu Sorry, what? — , Mar 21 '14 at 00:25

Juned Ahsan · Accepted Answer · 2014-03-21T00:27:12.563

0

Use executeUpdate instead of execute to run the update queries. Although execute API documentation states that it should work for udpates but it does not most of the time.

Here is what you need to change

from

killerSQL.execute();

to

killerSQL.executeUpdate();

similarly the other one.

Also commmit add close your resultset,statements and connection.

edited Mar 21 '14 at 00:27

answered Mar 21 '14 at 00:12

Juned Ahsan

67,789
12
98
136

Thank you for the answer. I switched these but I have no change in the database. – Mar 21 '14 at 00:25
@jdersen Also commmit add close your resultset,statements and connection. – Juned Ahsan Mar 21 '14 at 00:27
Okay. I've used your suggestions. I added the manual commit, and I found a bug which was cancelling the query from being called. Thank you! I'm not even sure if the other stuff was necessary, but it sounds like a good practice, so I'll keep using them. +1d and accepted. Thank you! – Mar 21 '14 at 03:59

Why does this MySQL not execute correctly?

1 Answers1