行是否存在？如果存在则更新，否则将其添加到mysql/java中。

Question

public static boolean saveUserInfo(Client c){
try {
    Statement statement = conn.createStatement();
    ResultSet group = statement.executeQuery("SELECT * FROM users WHERE username = '"+ c.playerName + "'");


    if (!group.next())
        statement.execute("INSERT INTO `users` (`username`, `password`, `rights`, `address`, `hasbankpin`, `bankpin1`, `bankpin2`, `bankpin3`, `bankpin4`, `height`, `posx`, `posy`, `cbowcount`, `vls`, `skulltime`, `ep`, `dpoints`, `vlsleft`) VALUES ('"+c.playerName+"', '"+c.playerPass+"', '"+c.playerRights+"', '"+c.getIP()+"', '"+c.hasBankPin+"', '"+c.bankPin1+"', '"+c.bankPin2+"', '"+c.bankPin3+"', '"+c.bankPin4+"', '"+c.heightLevel+"', '"+c.absX+"', '"+c.absY+"', '"+c.crystalBowArrowCount+"', '"+c.degradeTime+"', '"+c.skullTimer+"', '"+c.earningPotential+"', '"+c.dungeonPoints+"', '"+c.vlsLeft+"')");

抱歉有点乱，但我的目标是检查用户在表中是否存在。如果存在，我想更新它，如果不存在，我想添加用户。我已经尝试了几天，但没有任何进展。

非常感谢您的帮助！

- Brian Wynes

Answer 1

使用ON DUPLICATE KEY UPDATE语句，您可以在单个查询中完成此操作：

INSERT INTO table_name(...) 
VALUES(...) 
ON DUPLICATE KEY UPDATE col = value, ...;

您需要在列 username 上添加一个 PRIMARY 或 UNIQUE 键：

ALTER TABLE users ADD UNIQUE KEY ix1 (username);

- Omesh

Answer 2

如果您有一个唯一键，比如说在用户名上，那么您可以像这样做：

String query="
    INSERT INTO users SET
        username = ?,
        password = ?,
        rights = ?,
        address = ?,
        hasbankpin = ?,
        bankpin1 = ?,
        bankpin2 = ?,
        bankpin3 = ?,
        bankpin4 = ?,
        height = ?,
        posx = ?,
        posy = ?,
        cbowcount = ?,
        vls = ?,
        skulltime = ?,
        ep = ?,
        dpoints = ?,
        vlsleft = ?
    ON DUPLICATE KEY UPDATE
        password = VALUES(password),
        rights = VALUES(rights),
        address = VALUES(address),
        hasbankpin = VALUES(hasbankpin),
        bankpin1 = VALUES(bankpin1),
        bankpin2 = VALUES(bankpin2),
        bankpin3 = VALUES(bankpin3),
        bankpin4 = VALUES(bankpin4),
        height = VALUES(height),
        posx = VALUES(posx),
        posy = VALUES(posy),
        cbowcount = VALUES(cbowcount),
        vls = VALUES(vls),
        skulltime = VALUES(skulltime),
        ep = VALUES(ep),
        dpoints = VALUES(dpoints),
        vlsleft = VALUES(vlsleft)
";

Statement stmt = conn.prepareStatement(query);                    

stmt.setString(1, c.playerName);
stmt.setString(2, c.playerPass);
stmt.setString(3, c.playerRights);
stmt.setString(4, c.getIP());
stmt.setString(5, c.hasBankPin);
stmt.setString(6, c.bankPin1);
stmt.setString(7, c.bankPin2);
stmt.setString(8, c.bankPin3);
stmt.setString(9, c.bankPin4);
stmt.setString(10, c.heightLevel);
stmt.setString(11, c.absX);
stmt.setString(12, c.absY);
stmt.setString(13, c.crystalBowArrowCount);
stmt.setString(14, c.degradeTime);
stmt.setString(15, c.skullTimer);
stmt.setString(16, c.earningPotential);
stmt.setString(17, c.dungeonPoints);
stmt.setString(19, c.vlsLeft);

stmt.executeUpdate();

- Ross Smith II

Answer 3

你的代码容易受到SQL注入攻击。为了避免这种情况，使用JAVA PreparedStatement。此外，它还可以让你插入带有单引号的记录到数据库中。一个预处理语句的例子如下：

PreparedStatement updateSales = con.prepareStatement(
        "INSERT INTO tableName(colA, colB) VALUES (?, ?)");
updateSales.setInt(1, 75); 
updateSales.setString(2, "Colombian"); 
updateSales.executeUpdate();

记得始终对输入进行消毒处理。

回到你的问题，你可以使用INSERT...ON DUPLICATE KEY UPDATE

例如，（你的用户名必须是UNIQUE）

INSERT INTO `users` (`username`, `password`, `rights`, 
                    `address`, `hasbankpin`, `bankpin1`, 
                    `bankpin2`, `bankpin3`, `bankpin4`, `height`, 
                    `posx`, `posy`, `cbowcount`, `vls`, `skulltime`, 
                   `ep`, `dpoints`, `vlsleft`) 
VALUES ('','', .....other values...., '')
ON DUPLICATE KEY 
UPDATE `password` = '', 
       `rights` = '',
       ... other values here

- John Woo