JDBC-无法获取值并删除mysql数据库中的表 [英] JDBC- cant retrive value and delete table in mysql database

问题描述

我将JDBC与wamp和phpmyadmin一起使用.创建帐户后，我想检索该帐户的ID.表帐户我有ID，名称，余额. 它生成一个SQLException:Column'id'not found

Im using JDBC with wamp and phpmyadmin. When an account is created i want to retrieve the id of that account. the table account i have id, name, balance. its generates an SQLException:Column 'id' not found

sql = "INSERT INTO account (name, balance) VALUES ('" + name + "', " + 0 + ");";
        stmt.executeUpdate(sql);
        sql = "SELECT MAX(id) from account";
         ResultSet id = stmt.executeQuery(sql);
          while(id.next()){
          id_user  = id.getInt("id");
        }
        System.out.println(id_user);

当我想删除现有帐户时，它会生成此执行:java.sql.SQLException:无法使用executeQuery()发出数据操作语句. 代码是:

And when i want ot remove an existing account it generates this execption: java.sql.SQLException: Can not issue data manipulation statements with executeQuery(). the code is:

sql = "SELECT * FROM account WHERE id=" + id + "; ";
        ResultSet rs = stmt.executeQuery(sql);
        while(rs.next()){
          id_user  = rs.getInt("id");
        }
        if (id_user==id) {
            sql = "DELETE FROM contas WHERE id=" + id + "; ";
            stmt.executeQuery(sql);
            result = true;
        } else {
            result = false;
        }

推荐答案

我知道您在做一些不同的事情，但有一些插入的小技巧:

Java具有数据库独立的方式来获取INSERT的生成键. 与在多用户环境中事后或事前使用MAX相比，这要安全得多.

Java has a database independent way to fetch the generated keys of an INSERT. That is a lot safer than taking the MAX afterwards or before, in a multi-user environment.

错误ID的情况:

1. 第一次插入
2. 第二次插入
3. 第一次选择
4. 第二次选择

还可以使用PreparedStatement进行安全性(SQL注入)和转义(如果名称包含单引号或反斜杠等).

Also use a PreparedStatement, for security (SQL injection) and escaping (if name contains single quote or backslash or so.

try-withresources总是关闭事物，即使返回或异常也是如此.

And try-withresources always closes things, even on return or exception.

String sql = "INSERT INTO account (name, balance) VALUES (?, 0)";
try (PreparedStatement stmt = conn.prepareStatement(sql,
        Statement.RETURN_GENERATED_KEYS)) {
    stmt.setString(1, name);
    int updateCount = stmt.executeUpdate(); // 1
    try (ResultSet id = stmt.getGeneratedKeys()) {
        id_user = 0;
        if (id.next()) { // 'if' as just 1 row inserted.
            id_user = id.getInt(1); // 1 key per row.
        }
    }
    System.out.println(id_user);
}

这篇关于JDBC-无法获取值并删除mysql数据库中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！