java - 将 SQL 计数设置为 java 中的变量

Question

ResultSet rs = stm.executeQuery("SELECT count(*) FROM `hs` WHERE `username` ='"+c.playerName+"' LIMIT 1");
int rows = 0;

try
{
    rs.last();
    rows = rs.getRow();
    rs.beforeFirst();
} 
catch(Exception ex)
{
    ex.printStackTrace();
    return false;
}

if(rows > 0)
{
    query("UPDATE `hs` SET `overall_lvl` = '"+overallLVL+"',`overall_xp` = '"+overallXP+"',`attack_lvl` = '"+c.playerLevel[0]+"',`attack_xp` = '"+c.playerXP[0]+"',`defence_lvl` = '"+c.playerLevel[1]+"',`defence_xp` = '"+c.playerXP[1]+"',`strength_lvl` = '"+c.playerLevel[2]+"',`strength_xp` = '"+c.playerXP[2]+"',`ranged_lvl` = '"+c.playerLevel[3]+"',`ranged_xp` = '"+c.playerXP[3]+"',`prayer_lvl` = '"+c.playerLevel[4]+"',`prayer_xp` = '"+c.playerXP[4]+"',`magic_lvl` = '"+c.playerLevel[5]+"',`magic_xp` = '"+c.playerXP[5]+"',`cooking_lvl` = '"+c.playerLevel[6]+"',`cooking_xp` = '"+c.playerXP[6]+"',`woodcutting_lvl` = '"+c.playerLevel[7]+"',`woodcutting_xp` = '"+c.playerXP[7]+"',`fletching_lvl` = '"+c.playerLevel[8]+"',`fletching_xp` = '"+c.playerXP[8]+"',`fishing_lvl` = '"+c.playerLevel[9]+"',`fishing_xp` = '"+c.playerXP[9]+"',`firemaking_lvl` = '"+c.playerLevel[10]+"',`firemaking_xp` = '"+c.playerXP[10]+"',`crafting_lvl` = '"+c.playerLevel[11]+"',`crafting_xp` = '"+c.playerXP[11]+"',`smithing_lvl` = '"+c.playerLevel[12]+"',`smithing_xp` = '"+c.playerXP[12]+"',`mining_lvl` = '"+c.playerLevel[13]+"',`mining_xp` = '"+c.playerXP[13]+"',`herblore_lvl` = '"+c.playerLevel[14]+"',`herblore_xp` = '"+c.playerXP[14]+"',`agility_lvl` = '"+c.playerLevel[15]+"',`agility_xp` = '"+c.playerXP[15]+"',`thieving_lvl` = '"+c.playerLevel[16]+"',`thieving_xp` = '"+c.playerXP[16]+"',`slayer_lvl` = '"+c.playerLevel[17]+"',`slayer_xp` = '"+c.playerXP[17]+"',`farming_lvl` = '"+c.playerLevel[18]+"',`farming_xp` = '"+c.playerXP[18]+"',`runecrafting_lvl` = '"+c.playerLevel[19]+"',`runecrafting_xp` = '"+c.playerXP[19]+"',WHERE `username` = '"+c.playerName+"'");
}
else
{
    query("INSERT INTO `hs` SET `username` = '"+c.playerName+"', `overall_lvl` = '"+overallLVL+"',`overall_xp` = '"+overallXP+"',`attack_lvl` = '"+c.playerLevel[0]+"',`attack_xp` = '"+c.playerXP[0]+"',`defence_lvl` = '"+c.playerLevel[1]+"',`defence_xp` = '"+c.playerXP[1]+"',`strength_lvl` = '"+c.playerLevel[2]+"',`strength_xp` = '"+c.playerXP[2]+"',`ranged_lvl` = '"+c.playerLevel[3]+"',`ranged_xp` = '"+c.playerXP[3]+"',`prayer_lvl` = '"+c.playerLevel[4]+"',`prayer_xp` = '"+c.playerXP[4]+"',`magic_lvl` = '"+c.playerLevel[5]+"',`magic_xp` = '"+c.playerXP[5]+"',`cooking_lvl` = '"+c.playerLevel[6]+"',`cooking_xp` = '"+c.playerXP[6]+"',`woodcutting_lvl` = '"+c.playerLevel[7]+"',`woodcutting_xp` = '"+c.playerXP[7]+"',`fletching_lvl` = '"+c.playerLevel[8]+"',`fletching_xp` = '"+c.playerXP[8]+"',`fishing_lvl` = '"+c.playerLevel[9]+"',`fishing_xp` = '"+c.playerXP[9]+"',`firemaking_lvl` = '"+c.playerLevel[10]+"',`firemaking_xp` = '"+c.playerXP[10]+"',`crafting_lvl` = '"+c.playerLevel[11]+"',`crafting_xp` = '"+c.playerXP[11]+"',`smithing_lvl` = '"+c.playerLevel[12]+"',`smithing_xp` = '"+c.playerXP[12]+"',`mining_lvl` = '"+c.playerLevel[13]+"',`mining_xp` = '"+c.playerXP[13]+"',`herblore_lvl` = '"+c.playerLevel[14]+"',`herblore_xp` = '"+c.playerXP[14]+"',`agility_lvl` = '"+c.playerLevel[15]+"',`agility_xp` = '"+c.playerXP[15]+"',`thieving_lvl` = '"+c.playerLevel[16]+"',`thieving_xp` = '"+c.playerXP[16]+"',`slayer_lvl` = '"+c.playerLevel[17]+"',`slayer_xp` = '"+c.playerXP[17]+"',`farming_lvl` = '"+c.playerLevel[18]+"',`farming_xp` = '"+c.playerXP[18]+"',`runecrafting_lvl` = '"+c.playerLevel[19]+"',`runecrafting_xp` = '"+c.playerXP[19]+"'");

}

当我运行它时，它只会执行插入，而在第二次运行时它不会再次执行。我相信这与未正确转换为 int 的行有关。我该怎么做才能正确设置它？

Answer 1

您的代码应该是:

int rows; // No need to initialize
try (PreparedStatement stmt = conn.prepareStatement("SELECT count(*)" +
                                                     " FROM `hs`" +
                                                    " WHERE `username` = ?")) { // No need for LIMIT 1
    stmt.setString(1, c.playerName); // This prevents syntax errors and SQL Injection issues
    try (ResultSet rs = stmt.executeQuery()) {
        rs.next(); // SELECT count(*) always returns exactly 1 row
        rows = rs.getInt(1); // Get value of first column
    }
}

请(!)也为您的 INSERT 和 UPDATE 语句使用 PreparedStatement。