java - SQL/Java : Upon attempting to save data to tables will not add to tables

Question

我的代码没有将任何数据保存到表中。我不确定为什么不是，我修复了我在上一篇文章中发布的错误。有什么理由不这样做？它不再给我任何错误。我拥有的表与查询兼容吗？我现在真的卡住了。

这是将其保存到表中的代码:

public boolean saveHighScore(Client c) throws SQLException {
long totallvlexp = (long) (c.playerXP[0]) + (c.playerXP[1]) + (c.playerXP[2]) + (c.playerXP[3]) + (c.playerXP[4]) + (c.playerXP[5]) + (c.playerXP[6]) + (c.playerXP[7]) + (c.playerXP[8]) + (c.playerXP[9]) + (c.playerXP[10]) + (c.playerXP[11]) + (c.playerXP[12]) + (c.playerXP[13]) + (c.playerXP[14]) + (c.playerXP[15]) + (c.playerXP[16]) + (c.playerXP[17]) + (c.playerXP[18]) + (c.playerXP[19]) + (c.playerXP[20]); 
int totallevell = (int) (c.getLevelForXP(c.playerXP[0]) + c.getLevelForXP(c.playerXP[1]) + c.getLevelForXP(c.playerXP[2]) + c.getLevelForXP(c.playerXP[3]) + c.getLevelForXP(c.playerXP[4]) + c.getLevelForXP(c.playerXP[5]) + c.getLevelForXP(c.playerXP[6]) + c.getLevelForXP(c.playerXP[7]) + c.getLevelForXP(c.playerXP[8]) + c.getLevelForXP(c.playerXP[9]) + c.getLevelForXP(c.playerXP[10]) + c.getLevelForXP(c.playerXP[11]) + c.getLevelForXP(c.playerXP[12]) + c.getLevelForXP(c.playerXP[13]) + c.getLevelForXP(c.playerXP[14]) + c.getLevelForXP(c.playerXP[15]) + c.getLevelForXP(c.playerXP[16]) + c.getLevelForXP(c.playerXP[17]) + c.getLevelForXP(c.playerXP[18]) + c.getLevelForXP(c.playerXP[19]) + c.getLevelForXP(c.playerXP[20]));
boolean result = true;
String updateSkillsSql = "UPDATE skills"
    + " SET `Attacklvl`=?,"
    + " `Attackxp`=?,"
    + " `Defencelvl`=?,"
    + " `Defencexp`=?,"
    + " `Strengthlvl`=?,"
    + " `Strengthxp`=?,"
    + " `Hitpointslvl`=?,"
    + " `Hitpointsxp`=?,"
    + " `Rangelvl`=?,"
    + " `Rangexp`=?,"
    + " `Prayerlvl`=?,"
    + " `Prayerxp`=?,"
    + " `Magiclvl`=?,"
    + " `Magicxp`=?,"
    + " `Cookinglvl`=?,"
    + " `Cookingxp`=?,"
    + " `Woodcuttinglvl`=?,"
    + " `Woodcuttingxp`=?,"
    + " `Fletchinglvl`=?,"
    + " `Fletchingxp`=?,"
    + " `Fishinglvl`=?,"
    + " `Fishingxp`=?,"
    + " `Firemakinglvl`=?,"
    + " `Firemakingxp`=?,"
    + " `Craftinglvl`=?,"
    + " `Craftingxp`=?,"
    + " `Smithinglvl`=?,"
    + " `Smithingxp`=?,"
    + " `Mininglvl`=?,"
    + " `Miningxp`=?,"
    + " `Herblorelvl`=?,"
    + " `Herblorexp`=?,"
    + " `Agilitylvl`=?,"
    + " `Agilityxp`=?,"
    + " `Thievinglvl`=?,"
    + " `Thievingxp`=?,"
    + " `Slayerlvl`=?,"
    + " `Slayerxp`=?,"
    + " `Farminglvl`=?,"
    + " `Farmingxp`=?,"
    + " `Runecraftlvl`=?,"
    + " `Runecraftxp`=?"
    + " WHERE `playerName`=?";


String updateSkillsoverallSql = "UPDATE skillsoverall"
+ " SET `lvl`=?,"
+ " `xp`=?"
+ " WHERE `playerName`=?";

String updatePlayerRightsSql = "UPDATE playerrights"
+ " SET `rank` = ?"
+ " WHERE `playerName`=?";

try(Connection con = DriverManager.getConnection("jdbc:mysql://localhost/highscores","root","root")) {
con.setAutoCommit(false);
    try(PreparedStatement pstmt = con.prepareStatement(updateSkillsSql);PreparedStatement pstmt2 = con.prepareStatement(updateSkillsoverallSql);PreparedStatement pstmt3 = con.prepareStatement(updatePlayerRightsSql);) {
        setParameters(pstmt,
                    c.playerLevel[0], c.playerXP[0],
                    c.playerLevel[1], c.playerXP[1],
                    c.playerLevel[2], c.playerXP[2],
                    c.playerLevel[3], c.playerXP[3],
                    c.playerLevel[4], c.playerXP[4],
                    c.playerLevel[5], c.playerXP[5],
                    c.playerLevel[6], c.playerXP[6],
                    c.playerLevel[7], c.playerXP[7],
                    c.playerLevel[8], c.playerXP[8],
                    c.playerLevel[9], c.playerXP[9],
                    c.playerLevel[10], c.playerXP[10],
                    c.playerLevel[11], c.playerXP[11],
                    c.playerLevel[12], c.playerXP[12],
                    c.playerLevel[13], c.playerXP[13],
                    c.playerLevel[14], c.playerXP[14],
                    c.playerLevel[15], c.playerXP[15],
                    c.playerLevel[16], c.playerXP[16],
                    c.playerLevel[17], c.playerXP[17],
                    c.playerLevel[18], c.playerXP[18],
                    c.playerLevel[19], c.playerXP[19],
                    c.playerLevel[20], c.playerXP[20],
                    c.playerName);

        setParameters(pstmt2,
            totallevell, totallvlexp,
                    c.playerName);
        setParameters(pstmt3,
            c.getPermission().getId(), c.playerName);

        pstmt.executeUpdate();
        pstmt2.executeUpdate();
        pstmt3.executeUpdate();
    } catch (Exception e) {
        System.out.println(String.format("There's a problem when saving the data of  player %s.", c.playerName));
        e.printStackTrace(System.out);
        con.rollback();
        result = false;
    }
    if (result) {
        con.commit();
        con.setAutoCommit(true);
    }
} catch (Exception e) {
    System.out.println(String.format("There's a problem when saving the data of  player %s.", c.playerName));
    e.printStackTrace(System.out);
    result = false;
    }
    return result;
}

private void setParameters(PreparedStatement pstmt, Object ... args) {
    int i = 0;
    try {
        for (Object arg : args) {
            pstmt.setObject(++i, arg);
        }
    } catch(Exception e) {
        e.printStackTrace();
    }
}
}

这是表格:

CREATE TABLE `skills` (
`playerName` varchar(15) NOT NULL default '',
`Attacklvl` double default NULL,
`Attackxp` double default NULL,
`Defencelvl` double default NULL,
`Defencexp` double default NULL,
`Strengthlvl` double default NULL,
`Strengthxp` double default NULL,
`Hitpointslvl` double default NULL,
`Hitpointsxp` double default NULL,
`Rangelvl` double default NULL,
`Rangexp` double default NULL,
`Prayerlvl` double default NULL,
`Prayerxp` double default NULL,
`Magiclvl` double default NULL,
`Magicxp` double default NULL,
`Cookinglvl` double default NULL,
`Cookingxp` double default NULL,
`Woodcuttinglvl` double default NULL,
`Woodcuttingxp` double default NULL,
`Fletchinglvl` double default NULL,
`Fletchingxp` double default NULL,
`Fishinglvl` double default NULL,
`Fishingxp` double default NULL,
`Firemakinglvl` double default NULL,
`Firemakingxp` double default NULL,
`Craftinglvl` double default NULL,
`Craftingxp` double default NULL,
`Smithinglvl` double default NULL,
`Smithingxp` double default NULL,
`Mininglvl` double default NULL,
`Miningxp` double default NULL,
`Herblorelvl` double default NULL,
`Herblorexp` double default NULL,
`Agilitylvl` double default NULL,
`Agilityxp` double default NULL,
`Thievinglvl` double default NULL,
`Thievingxp` double default NULL,
`Slayerlvl` double default NULL,
`Slayerxp` double default NULL,
`Farminglvl` double default NULL,
`Farmingxp` double default NULL,
`Runecraftlvl` double default NULL,
`Runecraftxp` double default NULL,
 index (playerName),

constraint fk_playername_rights
foreign key (playername) references players (playername)
on delete restrict
on update restrict 

) ENGINE=MyISAM;
ALTER TABLE skills ADD INDEX idx_playername (playername);

CREATE TABLE `playerrights` (
`playerName` varchar(15) NOT NULL default '',
`rank` int(2) default NULL,
index (playername),

constraint fk_playername_rights
foreign key (playername) references players (playername)
on delete restrict
on update restrict 

) ENGINE=MyISAM;
ALTER TABLE playerrights ADD INDEX idx_playername (playername);

CREATE TABLE `skillsoverall` (
`playerName` varchar(15) NOT NULL default '',
`lvl` int(11) default NULL,
`xp` BIGINT(11) default NULL,
index (playername),

constraint fk_playername_rights
foreign key (playername) references players (playername)
on delete restrict
on update restrict 

) ENGINE=MyISAM;
ALTER TABLE skillsoverall ADD INDEX idx_playerName (playerName);

Answer 1

^{将评论变成答案。}

您可能想要/需要将准备好的语句更改为 INSERT ... ON DUPLICATE KEY UPDATE .
但是，要使其正常工作，您需要一个 PRIMARY KEY/UNIQUE 索引。只要辅助表不超过每人最多一个条目，playerName 列将满足此要求。 (尽管从长远来看，数字 ID 可能是更好的选择……)

有关简单示例，请参阅 SQL Fiddle .
或者检查这个 SQL Fiddle ，它更接近您的问题域。请特别注意，尽管有两个 INSERT，但 Skills 中只存在一个记录。

请评论，如果这需要调整/进一步的细节。