database - Tableview 在编辑时更新数据库

Question

所以我想要发生的事情是让 TableView 在编辑后更新数据库中的数据。我想在这里使用 SetOnEditCommit 方法。单元格编辑确实有效，但它永远不会更新，也没有错误。首先，如果这种方法真的有效(可能不是)，我有点无能为力，因为很难找到这个特定事物的一些来源。我发现的资源并没有真正帮助。因此，如果有人知道为什么它不更新，或者在这里提供替代选项，那就太好了。

提到的部分:

    columnType.setOnEditCommit(new EventHandler<TableColumn.CellEditEvent<UserDetails, String>>() {
            @Override
            public void handle(TableColumn.CellEditEvent<UserDetails, String> event) {
              updataData();
            }
        });

        tableview.setItems(null);
        tableview.setItems(data);

    }


    public void updataData() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://37.128.148.113:3306/FYS", "FYS", "Kcj8g87~");
            Statement con = connection.createStatement();
            //connection
            TablePosition pos = tableview.getSelectionModel().getSelectedCells().get(0);
            int row = pos.getRow();
            TableColumn col = pos.getTableColumn();
             String data1 = (String) col.getCellObservableValue(row).getValue();
            //cell
            UserDetails row1 = tableview.getSelectionModel().getSelectedItem();
            c1 = row1.getId();
            //row
            //tableview variables
            con.execute("UPDATE gevonden_bagage SET  type = 'data1' WHERE koffer_id = 'c1' ");
            //Query       
        } catch (SQLException ex) {
            System.err.println("Error" + ex);
        }
    }
//get connection, get celldata, get id data from first row, update cell with selected id

完整的 Controller 类:

package simple;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.Button;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TablePosition;

import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.control.cell.TextFieldTableCell;

/**
 *
 * @author admin
 */
public class FXMLUserController extends SimpleController implements Initializable {

    @FXML
    public TableView<UserDetails> tableview;
    @FXML
    public TableColumn<UserDetails, String> columnId;
    @FXML
    public TableColumn<UserDetails, String> columnType;
    @FXML
    public TableColumn<UserDetails, String> columnKleur;
    @FXML
    public TableColumn<UserDetails, String> columnLuchthaven;
    @FXML
    public TableColumn<UserDetails, String> columnKenmerken;
    @FXML
    public TableColumn<UserDetails, String> columnStatus;
    @FXML
    public TableColumn<UserDetails, String> columnDatum;
    @FXML
    private Button btnLoad;
    //declare observable list for database data
    private ObservableList<UserDetails> data;
    private DbConnection dc;
    String c1;

    @FXML

//strings for getRow method
    @Override
    public void initialize(URL url, ResourceBundle rb) {
        dc = new DbConnection();
        loadDataFromDatabase();
    }

    @FXML
    public void loadDataFromDatabase() {
        try {
            Connection conn = dc.Connect();
            data = FXCollections.observableArrayList();
            // Execute query and store result in a resultset
            ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM gevonden_bagage");
            while (rs.next()) {
                //get strings
                data.add(new UserDetails(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
                        rs.getString(6), rs.getString(7)));
            }

        } catch (SQLException ex) {
            System.err.println("Error" + ex);
        }

        //Set cell values to tableview.
        tableview.setEditable(true);
        tableview.getSelectionModel().setCellSelectionEnabled(true);

        columnType.setCellFactory(TextFieldTableCell.forTableColumn());
        columnKleur.setCellFactory(TextFieldTableCell.forTableColumn());
        columnLuchthaven.setCellFactory(TextFieldTableCell.forTableColumn());
        columnKenmerken.setCellFactory(TextFieldTableCell.forTableColumn());
        columnStatus.setCellFactory(TextFieldTableCell.forTableColumn());
        columnDatum.setCellFactory(TextFieldTableCell.forTableColumn());
//makes columns editable
        columnId.setCellValueFactory(new PropertyValueFactory<>("id"));
        columnType.setCellValueFactory(new PropertyValueFactory<>("type"));
        columnKleur.setCellValueFactory(new PropertyValueFactory<>("kleur"));
        columnLuchthaven.setCellValueFactory(new PropertyValueFactory<>("luchthaven"));
        columnKenmerken.setCellValueFactory(new PropertyValueFactory<>("kenmerken"));
        columnStatus.setCellValueFactory(new PropertyValueFactory<>("status"));
        columnDatum.setCellValueFactory(new PropertyValueFactory<>("datum"));

    columnType.setOnEditCommit(new EventHandler<TableColumn.CellEditEvent<UserDetails, String>>() {
            @Override
            public void handle(TableColumn.CellEditEvent<UserDetails, String> event) {
              updataData();
            }
        });

        tableview.setItems(null);
        tableview.setItems(data);

    }


    public void updataData() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://37.128.148.113:3306/FYS", "FYS", "Kcj8g87~");
            Statement con = connection.createStatement();
            //connection
            TablePosition pos = tableview.getSelectionModel().getSelectedCells().get(0);
            int row = pos.getRow();
            TableColumn col = pos.getTableColumn();
             String data1 = (String) col.getCellObservableValue(row).getValue();
            //cell
            UserDetails row1 = tableview.getSelectionModel().getSelectedItem();
            c1 = row1.getId();
            //row
            //tableview variables
            con.execute("UPDATE gevonden_bagage SET  type = 'data1' WHERE koffer_id = 'c1' ");
            //Query       
        } catch (SQLException ex) {
            System.err.println("Error" + ex);
        }
    }
//get connection, get celldata, get id data from first row, update cell with selected id
    @FXML
    public void getRow() {

        TablePosition pos = tableview.getSelectionModel().getSelectedCells().get(0);
        int row = pos.getRow();
        TableColumn col = pos.getTableColumn();
// this gives the value in the selected cell:
        String data1 = (String) col.getCellObservableValue(row).getValue();
        System.out.println(data1);
//CURRENTLY UNUSED METHOD
    }

}

模型类:

import javafx.beans.property.SimpleStringProperty;
import javafx.beans.property.StringProperty;

/**
 *
 * @author admin
 */
public class UserDetails {

    private final StringProperty id;
    private final StringProperty type;
    private final StringProperty kleur;
    private final StringProperty luchthaven;
    private final StringProperty kenmerken;
    private final StringProperty status;
    private final StringProperty datum;

    //Default constructor
    public UserDetails(String id, String type, String kleur, String luchthaven, String kenmerken, String status, String datum) {
        this.id = new SimpleStringProperty(id);
        this.type = new SimpleStringProperty(type);
        this.kleur = new SimpleStringProperty(kleur);
        this.luchthaven = new SimpleStringProperty(luchthaven);
        this.kenmerken = new SimpleStringProperty(kenmerken);
        this.status = new SimpleStringProperty(status);
        this.datum = new SimpleStringProperty(datum);

    }

    //getters
    public String getId() {
        return id.get();
    }

    public String getType() {
        return type.get();
    }

    public String getKleur() {
        return kleur.get();
    }

    public String getLuchthaven() {
        return luchthaven.get();
    }

    public String getKenmerken() {
        return kenmerken.get();
    }

    public String getStatus() {
        return status.get();
    }

    public String getDatum() {
        return datum.get();
    }

    //setters
    public void setId(String value) {
        id.set(value);
    }

    public void setType(String value) {
        type.set(value);
    }

    public void setKleur(String value) {
        kleur.set(value);
    }

    public void setLuchthaven(String value) {
        luchthaven.set(value);
    }

    public void setKenmerken(String value) {
        kenmerken.set(value);
    }

    public void setStatus(String value) {
        status.set(value);
    }

    public void setDatum(String value) {
        datum.set(value);
    }

    //property values
    public StringProperty idProperty() {
        return id;
    }

    public StringProperty typeProperty() {
        return type;
    }

    public StringProperty kleurProperty() {
        return kleur;
    }

    public StringProperty luchthavenProperty() {
        return luchthaven;
    }

    public StringProperty kenmerkenProperty() {
        return kenmerken;
    }

    public StringProperty statusProperty() {
        return status;
    }

    public StringProperty datumProperty() {
        return datum;
    }
}

Answer 1

来自TableView documentation :

By default the TableColumn edit commit handler is non-null, with a default handler that attempts to overwrite the property value for the item in the currently-being-edited row. It is able to do this as the Cell.commitEdit(Object) method is passed in the new value, and this is passed along to the edit commit handler via the CellEditEvent that is fired. It is simply a matter of calling TableColumn.CellEditEvent.getNewValue() to retrieve this value.

It is very important to note that if you call TableColumn.setOnEditCommit(javafx.event.EventHandler) with your own EventHandler, then you will be removing the default handler. Unless you then handle the writeback to the property (or the relevant data source), nothing will happen.

所以问题是，通过在 columnType 上设置 onEditCommit，您删除了实际更新 中的 typeProperty 的默认处理程序UserDetails 实例。因此

String data1 = (String) col.getCellObservableValue(row).getValue();

给出旧值，您对数据库的更新不会改变任何东西。

此外，您在创建 SQL 语句的方式上存在错误。您正在将 WHERE 子句中的 id 设为文字值 'c1'(而不是变量 c1 中包含的值，并且类似地设置type 的值改为文字值 'data1'，而不是变量 data1 中的值。

这是一个修复程序，以及对代码的一些简化和一些避免 SQL injection attacks 的更好实践。 :

columnType.setOnEditCommit(event -> {
    UserDetails user = event.getRowValue();
    user.setType(event.getNewValue());
    updateData("type", event.getNewValue(), user.getId());
});

然后

private void updateData(String column, String newValue, String id) {

    // btw it is way better to keep the connection open while the app is running,
    // and just close it when the app shuts down....

    // the following "try with resources" at least makes sure things are closed:

    try (
        Connection connection = DriverManager.getConnection("jdbc:mysql://37.128.148.113:3306/FYS", "FYS", "Kcj8g87~");
        PreparedStatement stmt = connection.prepareStatement("UPDATE gevonden_bagage SET "+column+" = ? WHERE koffer_id = ? ");
    ) {

        stmt.setString(1, newValue);
        stmt.setString(2, id);
        stmt.execute();
    } catch (SQLException ex) {
        System.err.println("Error");
        // if anything goes wrong, you will need the stack trace:
        ex.printStackTrace(System.err);
    }
}