CODEWITHSUNDEEP
javamysql
user2375197
user2375197

Reputation: 3

MySQL PreparedStatement on duplicate key update

The following code does NOT produce any errors, but it also doesn't seem to do anything.

I'm trying to save a player's stats into the database, updating them if he/she is already in there. Table 'playerstats' has columns [name, kills, deaths, heals, caps, streak, mvps]. 'name' is the primary key.

After executing, the table remains the same like nothing happened. What am I doing wrong?

for (String pName : pNames) {

    PlayerStats pStats = getStats(pName);

    PreparedStatement pstmt = null;
    String updateString = "INSERT INTO darkagedb.playerstats VALUES (?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE name = ?, kills = kills + ?, deaths = deaths + ?, heals = heals + ?, caps = caps + ?, streak = GREATEST(streak,?), mvps = mvps + ?";

    try {
        conn.setAutoCommit(false);

        pstmt = conn.prepareStatement(updateString);
        pstmt.setString(1, pStats.name);
        pstmt.setInt(2, pStats.kills);
        pstmt.setInt(3, pStats.deaths);
        pstmt.setInt(4, pStats.heals);
        pstmt.setInt(5, pStats.caps);
        pstmt.setInt(6, pStats.streak);
        pstmt.setInt(7, pStats.mvps);
        pstmt.setString(8, pStats.name);
        pstmt.setInt(9, pStats.kills);
        pstmt.setInt(10, pStats.deaths);
        pstmt.setInt(11, pStats.heals);
        pstmt.setInt(12, pStats.caps);
        pstmt.setInt(13, pStats.streak);
        pstmt.setInt(14, pStats.mvps);
        conn.commit();
        System.out.println("saved stats for " + pStats.name);
        saved++;

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
        if (pstmt != null)
            pstmt.close();
        conn.setAutoCommit(true);
        } catch (SQLException e) {
        e.printStackTrace();
    }
}


    }

Upvotes: 0

Views: 1861

Answers (1)

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

    pstmt.setInt(14, pStats.mvps);
    pstmt.executeUpdate(); // You have to add this line
    conn.commit();

Currently you do not tell the Prepared Statement to do anything at all. It has to be executed in order to have any result.

Upvotes: 1

Related Questions