Tableview更新数据库在编辑 [英] Tableview update database on edit
问题描述
所以我想要发生的事情是让tableview在编辑后更新数据库中的数据。我想在这里使用SetOnEditCommit方法。单元格编辑确实有效，但它永远不会更新，也没有错误。首先，如果这种方法实际上有效（可能不是），我有点无能为力，因为很难找到这个特定事物的一些来源。我找到的消息来源并不是很有帮助。所以，如果有人知道为什么它不更新，或者可能在这里提供替代选项，那就太好了。
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.
上述部分：
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
完全控制器类：
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;
}
}
推荐答案
来自 TableView 文档：
默认情况下，TableColumn编辑提交处理程序为非null，
默认处理程序，它尝试覆盖当前正在编辑的行中
项的属性值。它可以这样做，因为
Cell.commitEdit（Object）方法在新值中传递，这是
通过
触发的CellEditEvent传递给编辑提交处理程序。只需调用
TableColumn.CellEditEvent.getNewValue（）来检索此值。
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.
请注意，如果您调用$ b，这一点非常重要$ b TableColumn.setOnEditCommit（javafx.event.EventHandler）和你自己的
EventHandler，然后你将删除默认的处理程序。除非
然后您处理对该属性的回写（或相关数据
源），否则不会发生任何事情。
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 ，你删除了实际更新的默认处理程序<$ UserDetails 实例中的c $ c> typeProperty 。因此
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.
此外，您在创建SQL语句的方式中存在错误。您在 WHERE 子句中使用文字值'c1'（而不是变量中包含的值） c1 ，类似地将的值设置为文字值'data1'，而不是变量 data1 中的值。
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.
这是一个修复，以及一些简化的代码和一些更好的做法，以避免 SQL注入攻击：
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());
});
然后
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);
}
}
这篇关于Tableview更新数据库在编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
