java - 如何使用Javafx应用程序更改MySql数据库的特定字段的值？

Question

我正在开发一个需要访问用户数据库的项目。我创建了一个 javafx 面板，它显示数据库中的所有用户及其在表中的数据。我想创建一种方法，将所选用户状态的值从用户更改为管理员，反之亦然，但无论我做什么，我都无法让它正常工作。这是该方法的代码:

private void grantAdminRights() {
        admninrightsButton.setOnAction(new EventHandler<ActionEvent>() {

            @Override
            public void handle(ActionEvent admnrights) {
                try {
                    Connection con = DbUtil.getInstance().getConnection();
                    User selectedUser = userTable.getSelectionModel().getSelectedItem();
                    Integer selectedUserID = selectedUser.getUserID();
                    String grantAdminRights = "UPDATE `androidshop`.`userdatabase` SET `status`='admin' WHERE `userID`="+selectedUserID +"";
                    String grantUserRights = "UPDATE `androidshop`.`userdatabase` SET `status`='user' WHERE `userID`="+selectedUserID +"";
                    String statusValidation = selectedUser.getStatus();

                    if (statusValidation.equals("user"))
                    {
                        selectedUser.setStatus("admin");
                        PreparedStatement ps = con.prepareStatement(grantAdminRights);
                        ps.setInt(1, selectedUser.getUserID());
                        ps.executeUpdate();
                    } 
                    else if (statusValidation.equals("admin")) 
                    {
                        selectedUser.setStatus("user");
                        PreparedStatement ps = con.prepareStatement(grantUserRights);
                        ps.setInt(1, selectedUser.getUserID());
                        ps.executeUpdate();
                    } 
                    else {
                        System.out.println("Invalid field value");
                    }
                } 
                catch (SQLException e) {
                    e.printStackTrace();
                }


                }

        });
    }

用户类的内容:

public class User {
    public int userID;
    public String username;
    public String password;
    public String status;
    public String firstname;
    public String lastname;
    public String shippingadress;
    public String preferredpayment;
    public String purchaseditems;

    public enum PreferredPayment {
        CreditCard, PayPal, OnReceipt;
    }

    public int getUserID() {
        return userID;
    }

    public void setUserID(int userID) {
        this.userID = userID;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getShippingadress() {
        return shippingadress;
    }

    public void setShippingadress(String shippingadress) {
        this.shippingadress = shippingadress;
    }

    public String getPreferredpayment() {
        return preferredpayment;
    }

    public void setPreferredpayment(String preferredpayment) {
        this.preferredpayment = preferredpayment;
    }

    public String getPurchaseditems() {
        return purchaseditems;
    }

    public void setPurchaseditems(String purchaseditems) {
        this.purchaseditems = purchaseditems;
    }
public User(int userID, String status, String username, String firstname, String lastname, String shippingadress, String preferredpayment, String purchaseditems) {
        this.userID = userID;
        this.username = username;
        this.status = status;
        this.firstname = firstname;
        this.lastname = lastname;
        this.shippingadress = shippingadress;
        this.preferredpayment = preferredpayment;
        this.purchaseditems = purchaseditems;
    }
}

用数据填充表格的方法(正常工作):

public void buildData() {
     data = FXCollections.observableArrayList();
     try {
         Connection con = DbUtil.getInstance().getConnection();
         String sql = "SELECT * FROM userdatabase";
         ResultSet rs = con.createStatement().executeQuery(sql);
         while(rs.next()) {
             data.add(new User(rs.getInt(1), rs.getString(2),  rs.getString(3), rs.getString(5), rs.getString(6),  rs.getString(7), rs.getString(8), rs.getString(9)));
         }
     } catch (SQLException ex){
         ex.printStackTrace();   
     }

        idColumn.setCellValueFactory(new PropertyValueFactory<User, Integer>("userID"));
        statusColumn.setCellValueFactory(new PropertyValueFactory<User, String>("status"));
        usernameColumn.setCellValueFactory(new PropertyValueFactory<User, String>("username"));
        firstNameColumn.setCellValueFactory(new PropertyValueFactory<User, String>("firstname"));
        lastNameColumn.setCellValueFactory(new PropertyValueFactory<User, String>("lastname"));
        adressColumn.setCellValueFactory(new PropertyValueFactory<User, String>("shippingadress"));
        paymentColumn.setCellValueFactory(new PropertyValueFactory<User, String>("preferredpayment"));
        itemsColumn.setCellValueFactory(new PropertyValueFactory<User, String>("purchaseditems"));

        userTable.setItems(null);
        userTable.setItems(data);
    }

显示的表格内容:

[表格内容]https://imgur.com/a/Bdoxg

如何让 grantAdminRights 方法按预期工作？我非常感谢有关代码的任何反馈和提示。

感谢所有抽出时间回答的人。

Answer 1

我对该方法进行了以下更改，现在它按预期工作:

private void grantAdminRights() {
            admninrightsButton.setOnAction(new EventHandler<ActionEvent>() {

                @Override
                public void handle(ActionEvent admnrights) {
                    try {
                        Connection con = DbUtil.getInstance().getConnection();
                        User selectedUser = userTable.getSelectionModel().getSelectedItem(); 
                        String grantAdminRights = "UPDATE `androidshop`.`userdatabase` SET `status`='admin' WHERE `userID`= ?";
                        String grantUserRights = "UPDATE `androidshop`.`userdatabase` SET `status`='user' WHERE `userID`= ?";
                        String statusValidation = selectedUser.getStatus();

                        if (selectedUser != null && statusValidation.equals("user"))
                        {
                            selectedUser.setStatus("admin");
                            PreparedStatement ps = con.prepareStatement(grantAdminRights);
                            ps.setInt(1, selectedUser.getUserID());
                            ps.executeUpdate();
                        } 
                        else if (selectedUser != null && statusValidation.equals("admin")) 
                        {
                            selectedUser.setStatus("user");
                            PreparedStatement ps = con.prepareStatement(grantUserRights);
                            ps.setInt(1, selectedUser.getUserID());
                            ps.executeUpdate();
                        } 
                        else {
                            System.out.println("Invalid field value");
                            System.out.println(statusValidation);
                        }
                    } 
                    catch (SQLException e) {
                        e.printStackTrace();
                    }


                    }

            });
        }

感谢大家提供的建议，使我能够实现这一目标。