javafx - Tableview update database on edit

Question

So the thing that i want to happen, is making the tableview update the data in the database after editing it. I wanted to use the SetOnEditCommit method here. The cell editing does work, but it never gets updated, with no error either. In the first place im a bit clueless if this method is actually efficient (probably not), since its hard to find some sources for this specific thing. And the sources that i found weren't really helpful. So it would be nice if someone had an idea as to why it doesn't update, or maybe provide an alternate option here.

The mentioned part:

    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

full controller class:

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
    }

}

Model class:

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

From the 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.

So the problem is that by setting the onEditCommit on columnType, you remove the default handler that actually updates typeProperty in the UserDetails instance. Consequently

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

gives the old value, and your update to the database won't change anything.

Additionally, you have errors in the way you create the SQL statement. You are making the id in the WHERE clause the literal value 'c1' (instead of the value contained in the variable c1, and similarly setting the value of type to the literal value 'data1', instead of the value in the variable data1.

Here is a fix, along with some simplification of the code and some better practices for avoiding SQL injection attacks:

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

and then

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);
    }
}